?
Solved

Trouble running Excel Macro from Access with VBA

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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.
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…
Suggested Courses

765 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