Personal.xls - Missing Macro's

[purceld2]

I have some VBA code that exports a query to Excel and then is trying to call and Excel macro in the Personal.xls file to format the spreadsheet.

I open the spreadsheet fine but get a message saying it cannot find the macro. when check the opened personal.xls file in Excel not all of the macros in the file are listed.

If I open the spreadsheet directly the personal.xls show all the macros

For some reason if the Personal.xls is opened remotely not all the macro are available/displayed

Any ideas

Code Below

Private Sub Command12_Click()
On Error GoTo LastLine
DoCmd.OutputTo acOutputQuery, "supplier gate call", acFormatXLS, _
"c:\documents and settings\802284714\My Documents\PC Data\Supplier Gate Call.xls", False

Dim xlObj
Set xlObj = CreateObject("excel.application")

xlObj.Visible = True
xlObj.Workbooks.Open "C:\documents and settings\802284714\My Documents\PC Data\Documents and Settings\Excel\XLSTART\personal.xls"
xlObj.Workbooks.Open "c:\documents and settings\802284714\My Documents\PC Data\Supplier Gate Call.xls"


xlObj.Run "personal.xls!sgc"


'Your recorded macro code code ends here

xlObj.activeworkbook.Save
xlObj.activeworkbook.Close
xlObj.Quit
Exit Sub
LastLine:
MsgBox "Error " & Err.Number & ": " & Err.Description
End Sub

 

[Kiwiman]

Howzit

I think the problem is your code is opening a new Excel Object. Opening a new Excel Object when one is already open will lock the Personal.xls for editing (by the first Excel Object) and will not be available for the subsequent Excel Object(s). None of the macros stored in the Personal.xls will be available for use in the subsequent ones.

You can test this in Excel, by first opening the first instance of Excel, then opening a second instance of Excel.