Macro works in native excel but not via automation

kenstr

New Member
Joined
Mar 19, 2002
Messages
11
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:eek:1").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...
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Try the following code to open your workbook:

ChDir ("c:\")
Workbooks.Open "OnOpen_sample.XLS"
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top