Macro to save file with Active Document Name

Discussion in 'Microsoft Excel Misc' started by Reha, Aug 10, 2010.

  1. Reha

    Reha Guest

    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
     
    Reha, Aug 10, 2010
    #1
    1. Advertisements

  2. Reha

    Jim Cone Guest

    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" <>
    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
     
    Jim Cone, Aug 10, 2010
    #2
  3. Reha

    Reha Guest

    On Aug 10, 9:31 pm, "Jim Cone" <> 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" <>
    > 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
     
    Reha, Aug 10, 2010
    #3
  4. Reha

    Jim Cone Guest

    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" <>
    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
     
    Jim Cone, Aug 10, 2010
    #4
  5. Reha

    Reha Guest

    On Aug 11, 1:48 am, "Jim Cone" <> 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" <>
    > 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
     
    Reha, Aug 11, 2010
    #5

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. robertguy
    Replies:
    2
    Views:
    428
    robertguy
    Mar 3, 2004
  2. ElsiePOA

    Macro to Save File with the name in a specified cell

    ElsiePOA, Oct 18, 2004, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    165
    Bob Phillips
    Oct 18, 2004
  3. Guest
    Replies:
    1
    Views:
    260
    Dave Peterson
    Jul 11, 2005
  4. zgall1
    Replies:
    3
    Views:
    512
    zgall1
    Feb 16, 2006
  5. Doris smith

    change first name last name to last name first name

    Doris smith, Feb 25, 2009, in forum: Microsoft Excel Misc
    Replies:
    3
    Views:
    437
    Stefi
    Feb 25, 2009
Loading...

Share This Page