Process to run multiple processes.

Here's a general VBA skeleton for what you mentioned.

Requirements are -
* You must copy and paste the code to a new module in your managers sheet.
* All the individual source files need to be collected in one folder. You will need to indicate where that is (green part below).
* You then need some kind of partial name match to identify the files you want to pull the data from (red part).

Also - I'm just looking at every file ending in .xls, .xlsx, .xlsm -- i.e., any excel file. That is all you have to do if ONLY those sales files with the macro are placed in that folder. By default, my routine is recursive and checks subfolders -keep the subfolders clean as well.

The orange parts are specific to your project. You can use Application.run to run the macro you need to use to update the file, and then after the save - you need to insert whatever code you are using to transfer the data (you indicated you had that working).


Basically - this skeleton provides the looping over multiple files to do whatever it is you are trying to do (that's what you need to add in the orange areas).

Please Login or Register  to view this content.

Application.Run wbk.Name & "!" & "GETTGECME"

This is my first macro on the sheets. GETTGECM is the name of the macro.

This is my code now.

Please Login or Register  to view this content.

It does start now.. it DOES open the first excel program in the folder but then fails when it tries to execute the first macro

Please Login or Register  to view this content.

with a run time 1004 error.

ADDED: I dont know if this helps or not but the file name is a multiple words long. Example is "Lot #100 top camp.xlsm"

Originally Posted by grimes0332

Excel does not have Objects named FOLDER or FILE...

As I indicated in my post - you must set a reference to the Microsoft scripting runtime in your project. I admit, I put the comment in the code itself, so you may not have seen it.

To do that:

With the project open and active, open the VBA editor (press Alt+F11).
Go to Tools>References
Scroll to find "Microsoft Scripting Runtime" in the list of libraries.
Check the box next to it, press OK.

NOW excel DOES have knowledge of the objects needed for that code.

Originally Posted by sungen99

I dont know if this helps or not but the file name is a multiple words long. Example is "Lot #100 top camp.xlsm"

OK - Spaces in the name are a problem. You solve that by wrapping the workbook name in single quotes.

Try this:

Please Login or Register  to view this content.

If there are spaces in the filename you need to enclose it in single quotes when using Application.Run.

Please Login or Register  to view this content.

You don't specify where to save the workbook, all you have is this which I suspect will save to the 'active' directory.

Please Login or Register  to view this content.

Try using SaveAs and specify the path/folder/filename etc.

By the way, what do the macros in each of the workbooks do?

Is it something specific to the workbook they are in?

As to the "Save" issue. Yeah, I missed that. Normally when I'm looping sheets like this, I don't save them.

I think there is more than one way. But, I would just save the individual sheet with the CLOSE command as follows -NOTE you can just take out the wbk.save line if you save upon closing.

Please Login or Register  to view this content.

I noticed you are declaring your variable 'As Object' - I addressed this issue in post #6 > I'm not sure if you can use my little trick of accessing the file's parent folder and name if you just declare that as 'object'.

The objects declared in the 'Microsoft Scripting Runtime' are quite useful, I'm not aware of any reason to avoid adding a checkmark for that library.


Links Question:
I just tested that scenario - and the links DO indeed update for me.

If you need to, you can also manually update the links by reopening the workbook or going through the data ribbon.

That line gives me a runtime 1004 error.. ahhhhhh

General- i DID add the Microsoft Scripting add in like you said. I made sure that it was on on the Master sheet as well as ALL of the data sheets but it still was erroring for some reason. so perhaps there is code to save the file another way?

Please Login or Register  to view this content.

That line should throw no error.


Error 1004 is general - What is the description of the problem included with that error?


I took your code from post#5 and made minimal modifications below - this runs without any problems, so I can't reproduce your issue

Please Login or Register  to view this content.

I didn't have that error previously - but when I put copied that code to a workbook, I got the same error. Curious.


TRY CODE BELOW - Seems like breaking up the operation into a save, then a close works out. Spaces in the name don't matter in that line.


ALSO! I've attached a workbook to run the loop, and a zipped collection of dummy files to run on - if you unzip it onto your Desktop (and the desktop is sitting at C:\users\yourUserName\Dektop... then you should be able to run it.

Please Login or Register  to view this content.

Awesome news.




To mark your thread solved do the following:
New Method
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved

Old Method
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save