Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    NJ, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Accessing Excel functions from Word VBA (Office 97)

    Hello,

    I have a Word template that to open a CSV file in Excel, copy the data, and then past in to the Word document. The only thing I am having trouble with is that I need the Open dialog to display in Excel to allow the user to select the file to open. I am able to get this to work in Excel, but cannot seem to get it to work when calling Excel from within Word. Here is what I have been trying:

    Dim xlApp As Object

    Set xlApp = CreateObject("excel.application")
    xlApp.Visible = True
    xlApp.Dialogs(xlDialogOpen).Show
    xlApp.Rows("1:1").Select
    xlApp.Selection.Delete Shift:=xlUp
    xlApp.Cells.Select
    xlApp.Selection.Copy
    xlApp.Quit
    Set xlApp = Nothing

    I am a beginner with VBA and don't know much about the VBA in Excel, so any assistance would be greatly appreciated! Please let me know if you need any additional information.

    Thanks in advance!

  2. #2
    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: Accessing Excel functions from Word VBA (Office 97)

    I tested this in Word 2000; I believe it should be the same in 97.

    As shown in the graphic, Word does not know how to interpret the constant xlDialogOpen. To give Word access to the Excel object model and constants, set a reference to the Microsoft Excel 8.0 Object Library under Tools, References while your Module is open in the editor.

    After that, you can replace your Dim and Set lines with one of these:

    Dim xlApp As New Excel.Application

    Now, when I run xlApp.Quit, I get questions from Excel about saving changes and preserving what's on the clipboard. Annoying. Maybe you can paste the data into your Word document first and then close Excel? Not sure if that would fix it, but it's worth a shot.
    Attached Images Attached Images

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    NJ, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing Excel functions from Word VBA (Office 97)

    Hello and thank you for your reply.

    I made the changes you suggested, but I get the following error:
    "Compile Error - User-defined type not defined"
    This error occurs on the line
    Dim xlApp As New Excel.Application
    that I replaced the original Dim and Set lines with. Any additional help appreciated!

    Thanks!

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing Excel functions from Word VBA (Office 97)

    You haven't set the reference to Excel.
    In the module design view, Open the menu Tools, Reference,
    Scroll down to Microsoft Excel 8.0 Object Library and check the checkbox
    Francois

  5. #5
    Lounger
    Join Date
    Jan 2001
    Location
    NJ, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing Excel functions from Word VBA (Office 97)

    Thanks for your response! I had set the reference, but I was then copying the module to another document, and the reference was lost! I needed to embed the module in the actual document and it works, which I guess is what we will need to do.

    Thanks for your help!

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Accessing Excel functions from Word VBA (Office 97)

    I think you can put the module & the reference into Normal.dot so you don't have to copy it all the time.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Accessing Excel functions from Word VBA (Office 97)

    Going back to the original post, if you put the module into the template on which the documents have been based, then that code (and reference) should become available to those documents.

Posting Permissions

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