Running code in Excel via Access using GetObject

[Angielah]

Have managed to open an Excel file via Access by using GetObject. Code as follows.....

Set ExcelWorksheet1 = Nothing
Set ExcelWorksheet1 = GetObject("c:\bpm-teamlead\performance\Performancemacdata\pmac220502.xls")
ExcelWorksheet1.Activate
ExcelWorksheet1.Application.Visible = True


I want the code in pmac220502.xls to run as soon as it is opened. I have started with Auto_open.
However, GetObject doesn't seem to open it as it is minimised and greyed out which is why I put in the line "ExcelWorksheet1.Activate". This has given it the focus but of course Auto_open doesn't run as it as already opened by the GetObject command.
Really confused and would appreciate advice
Angie

 

[Mario]

Can't you run your macro Autpen through your object? ie.: ExcelWorksheet1.Autpen.Run

 

[Angielah]

Will give that a go.
Thanks
Angie

 

[Angielah]

Hi Mario
Tried your suggestion but got the following error message
"Object doesn't support this property or method"
Have you any other ideas
Angie

 

[doulostheou]

The following seems to work:

Dim ExcelWorksheet1 As Excel.Application
Set ExcelWorksheet1 = CreateObject("Excel.Application")
ExcelWorksheet1.Workbooks.Open "[color=blue]YourPath.xls[/color]"
ExcelWorksheet1.Visible = True
ExcelWorksheet1.Run "[Color=blue]YourMacroName[/Color]"

 

[Angielah]

It works......!!!!!
Am delighted! Had to use As Object because it didn't like As Excel.Application for some reason.

Thank you so much Doulostheou

Angie

 

[Fornatian]

Another way of doing this is to use this method:

Application.FollowHyperlink "Path.xls"


Sub Autpen()
MsgBox "AutoOpen kicked in"
End Sub

or use the Workbook_Open event...

Private Sub Workbook_Open()
Autpen
End Sub

 

[Angielah]

Well
My delight was short lived.......

After opening Excel up , Access still has more code to run. However once Access has finished running, my macro in Excel (which has lots more to do before it too can quit) decides to hang.

Oh Lawd