PC Review


Reply
Thread Tools Rate Thread

Macro to save file with Active Document Name

 
 
Reha
Guest
Posts: n/a
 
      10th Aug 2010

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
 
Reply With Quote
 
 
 
 
Jim Cone
Guest
Posts: n/a
 
      10th Aug 2010

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" <(E-Mail Removed)>
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
 
Reply With Quote
 
 
 
 
Reha
Guest
Posts: n/a
 
      10th Aug 2010
On Aug 10, 9:31*pm, "Jim Cone" <(E-Mail Removed)> wrote:
> 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" <(E-Mail Removed)>
> 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


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

 
Reply With Quote
 
Jim Cone
Guest
Posts: n/a
 
      10th Aug 2010
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" <(E-Mail Removed)>
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

 
Reply With Quote
 
Reha
Guest
Posts: n/a
 
      11th Aug 2010
On Aug 11, 1:48*am, "Jim Cone" <(E-Mail Removed)> wrote:
> 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" <(E-Mail Removed)>
> 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


Thanks for your help.
Reha
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro Button on Document to Email active document as an attachment sot Microsoft Word Document Management 6 6th Dec 2012 03:39 PM
save ppt file based on active excel file name intoit Microsoft Powerpoint 1 14th Jul 2009 06:59 AM
active form name & active control name =?Utf-8?B?UGlldHJv?= Microsoft Access 2 16th Oct 2007 10:08 AM
Word 2007 "Save As" closes document, doesn't allow me to save it to a different directory or change name of document. =?iso-8859-1?q?El_Capit=E1n_de_las_Albondigas?= Microsoft Word Document Management 3 27th Jul 2007 03:59 AM
how do I reveerse name order last name, first name to first name, last name Don Smith Microsoft Excel Discussion 9 29th Nov 2006 10:55 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:13 PM.