How to open Excel file from Word VBA

[vb6novice]

In a Word VB module I use calls to the GetOpenFile API to select an Excel file that I want to open. Once I have the file name how do I open that file and make Excel the active application from Word.

 

[SkipVought]

Hi,

1. you must set a reference to the Excel Object Library in Tools/References

2. in your code you must set an Excle application object using the CreateObject or set an Excel Workbook object using the GetObject method.

hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[vb6novice]

Skip,

Thanks for the tip. Adding the Excel Object Library reference was simple, but I have litle (read: none) experience with the CreateObject or GetObject methods. Can you give me an example.

By the way, I like your 'The Office Experts' web site. I do that type of thing for my employer but would like to do it for my self. How'd you get started seling that type of service?.

 

[SkipVought]

It ain't easy to get business!

Have you checked out VBA Help on those methods? There are examples. If you run into problems, post back.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[vb6novice]

Thanks, Skip. I tried the Help and have incorporated part the GetObject example. (FYI: I'm using Office 97 Apps). It opened an instance of XL the first time I ran it but the Personal.xls workbook was the ActiveWindow, not the file I called for. After that it wouldn't work. Here's the code I'm using.

Sub open_it()
Dim myfile
Dim strFilter As String

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.XLS")
myfile = ahtCommonFileOpenSave( _
                    OpenFile:=True, _
                    InitialDir:=varDirectory, _
                    Filter:=strFilter, _
                    Flags:=lngFlags, _
                    DialogTitle:=varTitleForDialog)

    GetExcel (myfile)
End Sub

Function ahtAddFilterItem(strFilter As String, _
    strDescription As String, Optional varItem As Variant) As String

    If IsMissing(varItem) Then varItem = "*.*"
    ahtAddFilterItem = strFilter & _
                strDescription & vbNullChar & _
                varItem & vbNullChar
End Function

Sub GetExcel(file_to_open As String)
    Dim MyXL As Object

    Set MyXL = GetObject(file_to_open, "Excel.Application")
    MyXL.Application.Visible = True
    MyXL.Parent.Windows(1).Visible = True

End Sub

Nothing happens when I get to the Set MyXL= GetObject(... line. Any help is greatly appreciated.

 

[SkipVought]

Since your personal.xls is also opening and you really don't know what WINDOW your workbook is in, why not...

MyXL.Activate

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[vb6novice]

Excel is opening and becoming visible now. I added the

MyXL.Activate

but it did not solve the problem. When the Excel application opens there is no file open with it, just as if I had opened Excel from the button on the Office Shortcut Bar and then closed the Book# that opens with it.

The Personal.xls only opens when I include the line

MyXL.Parent.Windows(1).Visible = True

in the code.

 

[SkipVought]

When you look at menu item Window, is your workbook listed?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[vb6novice]

No.