snyperj
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!
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!
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?
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:
for example.
xlApp.Enableevents = false
' your code here
xlApp.Enableevents = True
for example.
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:
Then in your Access caller:
So in ThisWorkbook of your template:
Public Sub DoTasks()
' or call your own DoTasks routine....
MsgBox "hi"
End Sub
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
https://www.experts-exchange.com/questions/27293981/Excel-Export-Question.html?anchorAnswerId=36492355#a36492355
http://www.ozgrid.com/VBA/auto-run-macros.htm