Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Run Excel macro in Personal.xls against multiple xls files

Posted on 2005-03-23
9
Medium Priority
?
446 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
8 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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.
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

572 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