Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Automatically fire come code when workbook is opened

Posted on 2011-09-06
10
209 Views
Last Modified: 2012-05-12
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
Comment
Question by:snyperj
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 15

Expert Comment

by:WalkaboutTigger
ID: 36491409
Let me direct your attention to this:

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

0
 

Author Comment

by:snyperj
ID: 36491474
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36491580
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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

Author Comment

by:snyperj
ID: 36491716
Rorya- I am putting that in the access code?
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36491725
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
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36491746
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36491748
Wouldn't you have to declare p as Object since DoTasks is not a method of the Workbook class?
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 250 total points
ID: 36491759
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
 
LVL 17

Assisted Solution

by:andrewssd3
andrewssd3 earned 250 total points
ID: 36491783
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
 

Author Closing Comment

by:snyperj
ID: 36495236
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

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question