Excel file doesn't open-error

[RaunLGoode]

Upon returning from a vacation I was notified of a problem with a form in an Access file. I have a command button which is supposed to open an Excel workbook used as a data entry form. The Excel file has an "Auto Open" sub that clears the form upon opening the Excel file.
Here is the Subroutine:

Upon returning from Vacation I was notified of a problem with a form in an Access file. There is a command button which is supposed to open an Excel workbook with a data entry form. The Excel file has an "Auto Open" sub that clears the form. Here is the Subroutine:

Private Sub cmdAddDoc_Click()
'On Error GoTo Err_cmdAddDoc_Click

MsgBox "Opening the 'Project Entry' Spreadsheet"
Dim xlApp As Object
Set xlApp = GetObject("{path to File on Server}\2006_EngDB.xls")

'. RunAutoMacros Which:=xlAutoOpen
With xlApp
.Application.Visible = True
.Parent.Windows(1).Visible = True
End With
End Sub


There is a problem with the section of the code shown in green. If I try to run with the Green script I get a "compile error" "Invalid or unqualified reference. If I remark this part out, the Excel file opens, but seems to close down after the subroutine called in the Auto Open sub run, leaving only my "PERSONAL.xls" file open. I have been futzing around with off and on this for 2+ days
I think I have a syntax error, but I can't figure out what I am doing wrong. Could somebody with fresh eyes, or more experience than I have, help me find the error in my ways?
I am running Office 2003 /WinXP

 

[namliam]

GetObject presumes the file is allready open... If the file is not allready open then the xlApp is not set and any xlApp. will return an error....

also the green line should be within the With ... End With bit...

 

[RaunLGoode]

So do I need to replace the term "get" with another term...like "Open" or "Start" to open a closed file? (Those two don't work)

 

[namliam]

Try create

 

[RaunLGoode]

Thank You. Between your info and more searching in this group, I was able to get the Worksheet to open. But It still Isn't running subs called in the AutoOpen subroutine when I open the spreadsheet from the Access form.

Private Sub cmdAddDoc_Click()
'On Error GoTo Err_cmdAddDoc_Click

MsgBox "Opening MS Excel 'Project Entry' Spreadsheet"
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")

With xlApp
.Application.Visible = True
.UserControl = True
.Workbooks.Open "\\{path}\2006_EngDB.xls"
.Workbooks.RunAutoMacros Which:=xlAutoOpen
End With
End Sub

As you can see I placed the code within the "With" statement, but something still isn't right because the spreadsheet opens but the AutoOpen routine doesn't run. (If I open the speradsheet from Excel, AutoOpen works fine)

 

[namliam]

I think the autorun is not/cannot be triggered from Access... Try figuring out how to start a peace of code in Excel and "manualy" run the code...