Link to home
Start Free TrialLog in
Avatar of snyperj
snyperjFlag for United States of America

asked on

Automatically fire come code when workbook is opened

I have an access application that exports some data out to an excel template (.xlt) and subsequently opens the template in excel.

I have a command button at the top of the excel worksheet that fires some code DoTasks() when it is clicked.

The DoTasks() procedure resizes some columns and drops a subtotal line at the end of the data.

The user pretty much always clicks the command button, so I would like to just automatically run the DoTasks() when the workbook opens and remove the command button altogether.

How can I make the DoTasks() automatically fire?  Thanks!
Avatar of Darrell Porter
Darrell Porter
Flag of United States of America image

Let me direct your attention to this:

http://www.ozgrid.com/VBA/auto-run-macros.htm

Avatar of snyperj

ASKER

I cannot get it to work by putting it in the  Workbook_Open event.  I think it is because Access actually opens the template and then copies the data to it... so I think that event is firing before the data is pasted.

What other events can I use?
Use the open event. If you are automating Excel to do the export, then simply disable events before you open the file, and reenable them afterwards:

xlApp.Enableevents = false
' your code here
xlApp.Enableevents = True

Open in new window


for example.
Avatar of snyperj

ASKER

Rorya- I am putting that in the access code?
If you add a public sub to the ThisWorkbook module of your template, it is then available as a method of your workbook when you open the object from Access, and you can call it explicitly:

So in ThisWorkbook of your template:

Public Sub DoTasks()

    '  or call your own DoTasks routine....
    MsgBox "hi"

End Sub

Open in new window


Then in your Access caller:
Sub test()

    Dim p As Excel.Workbook
    
    Set p = Application.Workbooks.Add("your template path\your template.xltm")
    
    Call p.DoTasks

End Sub

Open in new window

Sorry, my second code snippet was created in Excel, so you would want whatever your Excel app object is called instead of Application in the Workbooks.Add call
Wouldn't you have to declare p as Object since DoTasks is not a method of the Workbook class?
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of snyperj

ASKER

I split the points, thanks for the help.  I went with another solution gleaned from the Access zone.

https://www.experts-exchange.com/questions/27293981/Excel-Export-Question.html?anchorAnswerId=36492355#a36492355