Solved

Trouble running Excel Macro from Access with VBA

Posted on 2002-03-22
3
242 Views
Last Modified: 2012-05-04
Hello all
I have exported a report to Excel, no prob.
Now I need to tell Excel to run a macro.  The macro works, and if I pull up the macros list (from tools, macro) I can see it fine.

However, trying to call it with VBA I get the error that the macro cannot be found.

Here is the code:

Set XL = Excel.Application
Set WrkBk = Windows.Application.Workbooks.Open("Graphdata.xls")
WrkBk.Application.Visible = True
Set WrkBk2 = Windows.Application.Workbooks.Open("GraphdataNew.xls")
WrkBk2.Application.Visible = True
XL.Run ("findSheetNames")

I also tried
WrkBk.Application.Run ("FindSheetNames")


I tried
WrkBk.Application.Run ("GraphData!sheet1.FindSheetNames")
and
WrkBk.Application.Run ("sheet1.FindSheetNames")

I always get the same error.
I tried it without quotes, and it said variable not defined.

I tried Excel.Application.Run("GraphData!sheet1.FindSheetNames")

In the immediate window this last line would execute the first line of the macro (which is just a msg box to let me know it started) then would say 'subscript out of range.'

I can run the macro fine, no errors, from Excel!

I have to have this fixed today. pls help!

Sasha


0
Comment
Question by:SBFurr
  • 2
3 Comments
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 50 total points
ID: 6889541
this is just a guess, but try this:

Set XL = Excel.Application
Set WrkBk = XL.Workbooks.Open("Graphdata.xls")
WrkBk.Application.Visible = True
Set WrkBk2 = XL.Workbooks.Open("GraphdataNew.xls")
WrkBk2.Application.Visible = True
WrkBK2.Run ("findSheetNames")


which of the two XLS files holds the Macro? (WrkBk or WrkBk2?)

Since you never associated your XL object with a specific XLS file, clearly the expected Macro was not found. This code uses the XL Applicationb Object to Open the specified WorkBooks, and then once the necessary woirklbook (which holds teh desired Macro) is opend, you should be able to run that macro, in That workbook.

Arthur Wood

0
 

Author Comment

by:SBFurr
ID: 6889603
The macro lives in WrkBk

I see your point about opening the workbooks using the XL object.

Unfortunatley the code now says "object does not support this property or method."

It seems only an Application object can use the Run method.

ack!

Sasha
0
 

Author Comment

by:SBFurr
ID: 6889662
i got it!
I"m going to give you the points, because you put the idea in my head to associate the XL object with the specific workbook.

The line that finally did it was:

XL.Run ("GraphData.xls!sheet1.findSheetNames")


apparently I needed the .xls in there

thanks!

Sasha
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

770 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