Macro to save file with Active Document Name

  • Thread starter Thread starter Reha
  • Start date Start date
R

Reha

Hello All,
I am running a macro from MS Word which extracts info from Word Tables
and put it in
Excel 2003.

The macro runs fine but I have a problem with saving the
ActiveWorkbook with the same path and name as the Word Document (sans
the extension .doc)

I have managed to make up following with my limited knowledge. I am
missing the <<<<<>>>>> part. Would appreciate any help please

Sub ToSaveFile()
Dim StrFile As String
Dim StrPath As String
Dim StrName As String
StrPath = ActiveDocument.Path 'Get document path
StrFile = ActiveDocument.Name 'Get document name
StrName = Left(StrFile, Len(StrFile) - 4) <<>>
sFilename = StrPath & StrName
<<<<<>>>>>>>>>

ActiveWorkbook.SaveAs sFileName

End Sub

The macro should save the Excel file (without any confirmation from
the user) with the same name as the Word Doc to the same path from
where the Doc file was opened.

Thanks in advance
Reha
 
I don't believe it is possible to have both a Word document and an Excel workbook "active" at the same time.
What code did you use to get the info into the excel workbook?
Usually you would have a object variable referencing the Excel application and use that to save/close the workbook.
Note: a standard Excel workbook (2003) has a file extension of ".xls"
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware

..
..
..

"Reha" <[email protected]>
wrote in message Hello All,
I am running a macro from MS Word which extracts info from Word Tables
and put it in
Excel 2003.

The macro runs fine but I have a problem with saving the
ActiveWorkbook with the same path and name as the Word Document (sans
the extension .doc)

I have managed to make up following with my limited knowledge. I am
missing the <<<<<>>>>> part. Would appreciate any help please

Sub ToSaveFile()
Dim StrFile As String
Dim StrPath As String
Dim StrName As String
StrPath = ActiveDocument.Path 'Get document path
StrFile = ActiveDocument.Name 'Get document name
StrName = Left(StrFile, Len(StrFile) - 4) <<>>
sFilename = StrPath & StrName
<<<<<>>>>>>>>>

ActiveWorkbook.SaveAs sFileName

End Sub

The macro should save the Excel file (without any confirmation from
the user) with the same name as the Word Doc to the same path from
where the Doc file was opened.

Thanks in advance
Reha
 
I don't believe it is possible to have both a Word document and an Excel workbook "active" at the same time.
What code did you use to get the info into the excel workbook?
Usually you would have a object variable referencing the Excel application and use that to save/close the workbook.
Note:  a standard Excel workbook (2003) has a file extension of ".xls"
--
Jim Cone
Portland, Oregon  USAhttp://www.mediafire.com/PrimitiveSoftware

.
.
.

"Reha" <[email protected]>
wrote in message Hello All,
I am running a macro from MS Word which extracts info from Word Tables
and put it in
Excel 2003.

The macro runs fine but I have a problem with saving the
ActiveWorkbook with the same path and name as the Word Document (sans
the extension .doc)

I have managed to make up following with my limited knowledge.  I am
missing the <<<<<>>>>> part.  Would appreciate any help please

Sub ToSaveFile()
    Dim StrFile As String
    Dim StrPath As String
    Dim StrName As String
StrPath = ActiveDocument.Path 'Get document path
StrFile = ActiveDocument.Name    'Get document name
    StrName = Left(StrFile, Len(StrFile) - 4)  <<>>
sFilename = StrPath & StrName
<<<<<>>>>>>>>>

    ActiveWorkbook.SaveAs sFileName

End Sub

The macro should save the Excel file (without any confirmation from
the user) with the same name as the Word Doc to the same path from
where the Doc file was opened.

Thanks in advance
Reha
Click to expand...

This is the code
Dim i As Long, j As Long, xi As Long, k As Long
Dim wdrange As Range
Dim xlapp As Object
Dim xlbook As Object
Dim xlsheet As Object
Dim xlrange As Object

'added for file save
Dim StrFile As String
Dim StrPath As String
Dim StrName As String

'Defines ActiveDocument Name for File Saving
StrFile= ActiveDocument.Name
StrPath = ActiveDocument.Path
StrName= Left(StrFile, Len(StrFile) - 4)
On Error Resume Next
Set xlapp = GetObject(, "Excel.Application")
If Err Then
bstartApp = True
Set xlapp = CreateObject("Excel.Application")
End If
On Error GoTo 0
Set xlbook = xlapp.Workbooks.Add
Set xlsheet = xlbook.Worksheets(1)

.......the rest of the code to import Word Table follows..

After the macro is completed I have both Word and Excel document open
on the screen.

I wish to save the Excel file with the same name as the Open Word
Document..
Eg Test.doc is open then Excel file should be Test.xls and saved in
the same path as Test.doc

Hope I am clear
Reha
 
Try this untested code...
'---
strName = ActiveDocument.FullName
strName = Left$(StrName, Len(strName) - 4)
sFileName = strName & ".xls"
'other code
xlbook.SaveAs sFileName
--
Jim Cone
Portland, Oregon USA
Compare | Match | Uniques: http://tinyurl.com/XLCompanion

..
..
..

"Reha" <[email protected]>
wrote in message

This is the code
Dim i As Long, j As Long, xi As Long, k As Long
Dim wdrange As Range
Dim xlapp As Object
Dim xlbook As Object
Dim xlsheet As Object
Dim xlrange As Object

'added for file save
Dim StrFile As String
Dim StrPath As String
Dim StrName As String

'Defines ActiveDocument Name for File Saving
StrFile= ActiveDocument.Name
StrPath = ActiveDocument.Path
StrName= Left(StrFile, Len(StrFile) - 4)
On Error Resume Next
Set xlapp = GetObject(, "Excel.Application")
If Err Then
bstartApp = True
Set xlapp = CreateObject("Excel.Application")
End If
On Error GoTo 0
Set xlbook = xlapp.Workbooks.Add
Set xlsheet = xlbook.Worksheets(1)

.......the rest of the code to import Word Table follows..

After the macro is completed I have both Word and Excel document open
on the screen.

I wish to save the Excel file with the same name as the Open Word
Document..
Eg Test.doc is open then Excel file should be Test.xls and saved in
the same path as Test.doc

Hope I am clear
Reha
 
Try this untested code...
'---
strName = ActiveDocument.FullName
strName = Left$(StrName, Len(strName) - 4)
sFileName = strName & ".xls"
   'other code
xlbook.SaveAs sFileName
--
Jim Cone
Portland, Oregon  USA
Compare | Match | Uniques:  http://tinyurl.com/XLCompanion

.
.
.

"Reha" <[email protected]>
wrote in message

This is the code
Dim i As Long, j As Long, xi As Long, k As Long
Dim wdrange As Range
Dim xlapp As Object
Dim xlbook As Object
Dim xlsheet As Object
Dim xlrange As Object

'added for file save
Dim StrFile As String
Dim StrPath As String
Dim StrName As String

'Defines ActiveDocument Name for File Saving
StrFile= ActiveDocument.Name
StrPath = ActiveDocument.Path
StrName= Left(StrFile, Len(StrFile) - 4)
On Error Resume Next
Set xlapp = GetObject(, "Excel.Application")
If Err Then
    bstartApp = True
    Set xlapp = CreateObject("Excel.Application")
End If
On Error GoTo 0
Set xlbook = xlapp.Workbooks.Add
Set xlsheet = xlbook.Worksheets(1)

......the rest of the code to import Word Table follows..

After the macro is completed I have both Word and Excel document open
on the screen.

I wish to save the Excel file with the same name as the Open Word
Document..
Eg Test.doc is open then Excel file should be Test.xls and saved in
the same path as Test.doc

Hope I am clear
Reha
Click to expand...

Thanks for your help.
Reha
 
Back
Top