Results 1 to 11 of 11

Thread: word/excel (97)

  1. #1
    New Lounger
    Join Date
    Jan 2003
    Location
    Michigan, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    word/excel (97)

    I am sure this has been covered here, but I can't find it. I need to write a macro to be executed in Word that opens a specific Excel file, copies a range from a specific worksheet, and pasts it into Word as a picture. I am skilled at writing macros in Excel, but Word seem different. Things that work in Excel don't seem to work in Word. thanks for any advice, references, or examples.

  2. #2
    New Lounger
    Join Date
    Jan 2003
    Location
    Michigan, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: word/excel (97)

    Thanks very much. This is perfect! I had come up with similar code, but with some subtle differences that made it not work right.

    Only one small issue. Word complains about the "XlApplication.quit" command saying "Object Required". Turns out, this command seems not to be necessary, as when the macro is finished running there is no incidence of Excel running.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: word/excel (97)

    Sorry about that. It was a typo, it should have been xlApp. I will edit my previous reply.

    Note: creating an Excel object this way starts an invisible instance (you can make it visible if desired by using xlApp.Visible = True); not terminating such an invisible instance can lead to memory leakage.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: word/excel (97)

    Welcome to Woody's Lounge!

    If you know Excel VBA, you can use it in a Word macro.

    First, you must set a reference to the Excel object library:
    <UL><LI>In Word, switch to the Visual Basic Editor (Alt+F11).
    <LI>Select Tools/References...
    <LI>In the list, locate Microsoft Excel 8.0 Object Library.
    <LI>Check the box corresponding to this item.
    <LI>Click OK.[/list]Now, you can create an Excel application object in your macro and use it the way you know. The only difference is that you *must* place the Excel application object before "general" Excel instructions. Here is some example code.

    <img src=/w3timages/blueline.gif width=33% height=2>

    Dim xlApp As New Excel.Application
    Dim xlWorkbook As Excel.Workbook
    Dim xlWorksheet As Excel.Worksheet
    Dim xlRange As Excel.Range

    ' Do things in Excel
    Set xlWorkbook = xlApp.Workbooks.Open("C:MyWorkbook.xls")
    Set xlWorksheet = xlWorkbook.Worksheets("Sheet1")
    Set xlRange = xlWorksheet.Range("D13:G37")
    xlRange.Copy

    ' Back to Word
    Selection.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, _
    Placement:=wdInLine, DisplayAsIcon:=False

    ' Clean up
    xlWorkbook.Close SaveChanges:=False
    xlApp.Quit ' originally had xlApplication here

    Set xlRange = Nothing
    Set xlWorksheet = Nothing
    Set xlWorkbook = Nothing
    Set xlApp = Nothing

    <img src=/w3timages/blueline.gif width=33% height=2>

  5. #5
    New Lounger
    Join Date
    Jan 2003
    Location
    Michigan, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: word/excel (97)

    Of course! I should have seen that.

    Another question, please: How do I get this to work. I want to use the file dialog box to find the filename for the previous function.

    Application.Dialogs(wdDialogFileOpen).Show "*.xls"

    Word keeps complaining that I am trying to open an Excel file.

    The sequence of arguments seems to be different than Excel, and I can't find a list of them in the word/VBA help files. Also can't find the selected filename.

    Thanks again.

  6. #6
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: word/excel (97)

    You want something like...

    With Application.Dialogs(wdDialogFileOpen)
    .Name = "*.xls"
    .Display
    MsgBox .Name
    End With

    StuartR

  7. #7
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: word/excel (97)

    I think when you say Application.something in VBA hosted in Word, it assumes you are referring to the Word Application object. Try using xlApp.Dialogs and see if that works better.

  8. #8
    New Lounger
    Join Date
    Jan 2003
    Location
    Michigan, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: word/excel (97)

    Well, another poorly documented operation:

    This works fine:
    ChangeFileOpenDirectory "c:windowsdesktop"
    With Application.Dialogs(wdDialogFileOpen)
    .Name = "*.xls"

    .Display
    MsgBox .Name

    But, suppose the user changes the directory while using the wddialogfileopen dialog box. How do I get this information? I have tried all variations of "Path" "filedirectory" etc that I can think of, and nothing seems to work. I was hoping the "changefileopendirectory" help file might refer me to what I wanted, but no.

    Thanks for suggestions.

  9. #9
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: word/excel (97)

    > suppose the user changes the directory while using the wddialogfileopen dialog box. How do I get this information?

    See <!post=Post #181008,181008>Post #181008<!/post>.

  10. #10
    New Lounger
    Join Date
    Jan 2003
    Location
    Michigan, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: word/excel (97)

    Thanks, just what I needed. I think Woody should give you guys a raise! :-)

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: word/excel (97)

    He just doubled our pay this month! <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •