Using Container in Auto open macro

[pukya78]

Hi,
I have an excel file which is embedded in some application. I ahve a
macro written in the excel file. I want the macro to run automatically
as soon as I open the excel sheet. For this I used the Workbook_Open
macro. But inside this macro if I try to call the Container method, I
am getting an error.

<code>
Private Sub Workbook_Open()
Dim x As Object
Worksheets("Sheet1").Activate
Set x = ThisWorkbook.Container
End Sub

</code>

But, instead of using the Workbook_Open macro, I use some other macro,
say macro1, and then run that macro manucally after opening the excel
sheet, I get no error. Does anyone have any idea why it does not work
when I use Workbook_Open macro? I want the macro to run as soon as I
open the excel sheet.

<code>

Private Sub macro1()
Dim x As Object
Worksheets("Sheet1").Activate
Set x = ThisWorkbook.Container
End Sub

</code>

Regards,
AA

 

[Peter T]

Hi,
I have an excel file which is embedded in some application. I ahve a
macro written in the excel file. I want the macro to run automatically
as soon as I open the excel sheet. For this I used the Workbook_Open
macro. But inside this macro if I try to call the Container method, I
am getting an error.

<code>
Private Sub Workbook_Open()
Dim x As Object
Worksheets("Sheet1").Activate
Set x = ThisWorkbook.Container
End Sub

</code>

But, instead of using the Workbook_Open macro, I use some other macro,
say macro1, and then run that macro manucally after opening the excel
sheet, I get no error. Does anyone have any idea why it does not work
when I use Workbook_Open macro? I want the macro to run as soon as I
open the excel sheet.

<code>

Private Sub macro1()
Dim x As Object
Worksheets("Sheet1").Activate
Set x = ThisWorkbook.Container
End Sub

</code>

Regards,
AA

 

[Peter T]

In the Workbook open event try calling your macro1 with the OnTime method,
perhaps add a second or two to 'Now'.

Not sure if this'll work but depending on the Container the open event can
fire before the wb and Excel is fully loaded.

Regards,
Peter T