Macro works in native excel but not via automation

[kenstr]

Preamble: Workbook with 3 sheets and the following code in it

Private Sub Workbook_Open()

ThisWorkbook.Worksheets("Sheet2").Select
ActiveSheet.Range("A1").Select
ActiveCell = "this text should be in the second sheet cell a1"
ActiveWindow.Zoom = (Application.UsableWidth / Range("a1:o1").Width) * 100
Application.DisplayFullScreen = True

End Sub


The code works fine when opened directly. If the workbook is opend via automation (say with VB), it will "run" but nothing really works the same.

Opened in VB as simple as possible:

Set mobjExcel = CreateObject("Excel.Application")
File = "c:OnOpen_sample.xls"
mobjExcel.Workbooks.Open File, 0, False, , , , True


This is driving me nuts...

[Al Chara]

Try the following code to open your workbook:

ChDir ("c:\")
Workbooks.Open "OnOpen_sample.XLS"

[HunterN]

Your problem sounds just like mine!
I have two workbooks. Seperately they both
work fine. But when I want the first workbook to call the second workbook, the open works fine but the rest of the macro does not work like it should.

If you find a help (that works) please let me know. Thanks

[Russell Hauf]

What do you mean by "nothing really works the same"? What exactly does it do when it is opened?

[kenstr]

Thanks for the replies, but I think I need to explain further.

If opened directly in excel results are:
1.Text is placed in sheet 2 cell a1 and is selected
2. window is zoomed in and excel is full screen

If opened via automation results are:
1. Text is placed in cell a1 of whatever the last viewed sheet was.
2. no zoom and no full screen and no sheet1 cell a1 selected.

Basically nothing at the application. level works at all and anything refering to activesheet or activeworkbook refers to the wrong sheet. I can even activate sheet 3 and the activesheet still returns whatever the last sheet viewed before it was opened.

[Sbirro Cornuto]

You always need to run the Macro from the main code, like Application.Run "yourworkbook.xls!macroname", just open up the WorkBook by code and then run the macro with above mentioned command. After that it gives total control of the WorkBook to it's own macro.

[kenstr]

Hey - now were on to something. That sort of works.

The file can be run directly or from inside a VB app. So I moved the code out of the workbook_open and into a sub called initilize. Then added this to the open event:

If Application.UserControl = True Then
initialize
End If

That way the code dosen't double fire if run from the VB app.

I can live with this... now I have to see if we can put a business rule in place "All files with startup code need to work like this to run in our VB app..."

Tks.

[Sbirro Cornuto]

That's the case buddy, sorry! No automated starting if you need to run those things from VB also...