Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Trouble running Excel Macro from Access with VBA

Posted on 2002-03-22
3
Medium Priority
?
248 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
[X]
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
  • 2
3 Comments
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 150 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

636 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