Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to open Excel file from Word VBA

Status
Not open for further replies.

vb6novice

Programmer
Sep 23, 2002
288
US
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.
 
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
 
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?.
 
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.

Code:
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.
 
Excel is opening and becoming visible now. I added the
Code:
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
Code:
MyXL.Parent.Windows(1).Visible = True
in the code.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top