Solved

Trouble running Excel Macro from Access with VBA

Posted on 2002-03-22
3
244 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 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

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.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

726 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