• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

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!
0
snyperj
Asked:
snyperj
  • 3
  • 3
  • 3
  • +1
2 Solutions
 
Darrell PorterEnterprise Business Process ArchitectCommented:
Let me direct your attention to this:

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

0
 
snyperjAuthor Commented:
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?
0
 
Rory ArchibaldCommented:
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.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
snyperjAuthor Commented:
Rorya- I am putting that in the access code?
0
 
andrewssd3Commented:
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

0
 
andrewssd3Commented:
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
0
 
Rory ArchibaldCommented:
Wouldn't you have to declare p as Object since DoTasks is not a method of the Workbook class?
0
 
Rory ArchibaldCommented:
snyperj
Without seeing how you are doing the export, I can't really answer specifically, but yes, that needs to go in the Access code.
0
 
andrewssd3Commented:
rorya - yes interesting point - you would expect that, but it compiles as it is in Excel 2010 and works.  It would also work if you declared it as Object, which you might want to do in Access anyway of you were late binding.
0
 
snyperjAuthor Commented:
I split the points, thanks for the help.  I went with another solution gleaned from the Access zone.

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27293981.html#a36492355
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now