?
Solved

Run Excel macro in Personal.xls against multiple xls files

Posted on 2005-03-23
9
Medium Priority
?
442 Views
Last Modified: 2012-08-13
I've looked through a lot of the q&a's but none quite address my problem.  I have a macro named "PrepareRange" defined in my Personal.xls file.  I have 53 xls files that I can open, go to tools, macros,...and see the macro listed.  From Access I am opening both the Personal.xls and *.xls with:

Set xlApp = CreateObject("excel.application") 'open Excel
'open the personal file holding macro
Set psFile = xlApp.Workbooks.Open("C:\Program Files\Microsoft Office\Office\XLStart\Personal.xls")
xlApp.Visible = True
Set wbFile = CreateObject("excel.application")
    Set ccFile = wbFile.Workbooks.Open(path) 'path is previously defined
    wbFile.Visible = True
    xlApp.Run "PrepareRange"

When I set a breakpoint after this snippet, I can see the macro from Personal.xls, but not from *.xls.  Also, xlApp.Run "PrepareRange" runs the macro in Personal.xls.  If I substitute -- wbFile.Run "PrepareRange" nothing happens.

Can somebody help?

Thanks
0
Comment
Question by:Yoery
[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
9 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13616842
Has the file pointed to in the path variable opened correctly?
0
 
LVL 3

Expert Comment

by:harsha_dp
ID: 13619729
If file can be opened properly, i dont think there is any problem in running a macro in it. Are u able to see the workbook u r opening ..
0
 

Author Comment

by:Yoery
ID: 13622561
Yes, I can see both workbooks.  In the Personal.xls I can see the macro, but in the *.xls the macro box is empty.
0
Industry Leaders: 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!

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13622576
You've verified that the other xls file does contain the macro?
0
 

Author Comment

by:Yoery
ID: 13644339
No, the *.xls does not contain the macro.  I have 53 of these that I need to run the macro on.  The macros is in Personal.xls.

0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 13644577
If the *.xls doesn't contain the macro then you can't run it because it doesn't exist! You can only run a macro that the Excel file itself contains.
0
 

Author Comment

by:Yoery
ID: 13710693
I figured it out.  Here is the code that worked:

Public Sub prepSpreadSheet()
'Sub-routine to open each cost center spreadsheet, and run the PrepareRange macro in Personal.xls
'to bring in the correct data
'run this routine first


Dim gaFile, path, ccVal, range As String
Dim xlApp As Object
Dim psFile As Object
Dim ccFile As Object

Set xlApp = CreateObject("excel.application") 'open Excel
'open the personal file holding macro
Set psFile = xlApp.Workbooks.Open("C:\Program Files\Microsoft Office\Office\XLStart\Personal.xls")
xlApp.Visible = True

gaFile = Dir("C:\GA\*.xls") 'initialize file name
path = "C:\GA\" & gaFile 'initialize path
ccVal = Left(gaFile, 4) 'initialize cost center value

Do Until gaFile = ""
    'open the individual cost center files
    Set ccFile = xlApp.Workbooks.Open(path)
    xlApp.Visible = True
    'run the macro
    xlApp.Run "Personal.xls!PrepareRange"
   
    gaFile = Dir 'call without parameters to get next suitable file
    path = "C:\GA\" & gaFile 'reset path
    ccVal = Left(gaFile, 4) 'reset cost center value
Loop
    xlApp.Quit
    Set xlApp = Nothing
End Sub

0
 

Accepted Solution

by:
PAQ_Man earned 0 total points
ID: 14095021
PAQed with points refunded (500)

PAQ_Man
Community Support Moderator
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

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