Accessing Excel functions from Word VBA (Office 97)

Topic

New Reply

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!

Reply | Quote

Replies

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.

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

I think you can put the module & the reference into Normal.dot so you don’t have to copy it all the time.

Reply | Quote

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.

Reply | Quote