[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Trouble running Excel Macro from Access with VBA

Posted on 2002-03-22
3
Medium Priority
?
250 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 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

829 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