vba excel to open files and to run a built in macro

hello,

I'm trying to create a way (maybe a vba code) or something a HTML application (HTA) that would do the following:

in a folder, i have multiple excel files probably about 40-50 excel files.
i want to create a way (vba code or HTML applicator HTA) that would open all of the excel files in this folder and run a macro automatically (Ctrl+G) for each of every file.
after that, save and close all excel files.

this is only a brief description of what I want to do, if needed more detail let me know.

hoping someone would be able to help.

A vba code perhaps something like this?

Please Login or Register  to view this content.

Alf

but how exactly to run this vba code?
open a new excel?

You say that there are a number of excel files but the folder name is "\csv\" and I do wonder are you opening csv files?

I think the best you could do at the moment is to record a macro where you select a file from that folder and then post this macro.

Alf

no, it's not a CSV files, it's just a folder name.

attach is the file i save with the vba code
do i need to create this file actually ?

hi jeff,

i edited and use code tags. sorry and thank you for the reminder.

hello alf,

are you able to check my file?
it's not working.

Sorry for the delay. Xmas have taken 1st priority according to my wife but now I'm back with a bit of time for excel.

The first part of the macro just opens the excel file, but I assume that the opened file does not contain any macro if so then you just run the commands
after the line

Please Login or Register  to view this content.

so just add the lines from the first macro

Please Login or Register  to view this content.

Since you are adding new variables they must also be dim'ed either at the start of the macro or as in this case before the new macro runs.
So you just adds the other macros after each other and after the last lines you need to include the loop so that all files are save and closed after modefication.

You modified macro in post #4 is a bit wrong, it should look like this:

Please Login or Register  to view this content.

So you open the file do the modification, saves the file and closes it, then you open the next file .....

As I said this assumes that the files you opens don't contains the macros you wish to run in order to modify them.

thanks for taking your time during the holiday to answer me.
i put your new code to create a new excel file to run the new code macro. as attached file.
nothing happen though, did i do anything wrong?

First of all make a backup of you excel files before you run the macro in case something goes wrong so you don't mess up your data.

Since I did not study you macro setup properly I missed how things works together i.e. from the main macro you call 4 sub macros in order to organize the data in the files. So you did not do anything wrong, it was my faulty understanding of the problem that messed things up. Sorry about that.

After the first macro has done it's job by transferring / changing data in the opened file macro jumps back to main macro and execute next line that calls the
second macro who modifies data and macro jumps back to main macro and third sub macro is called and so forth

The problem (my bad as I did not realize how these macros were called from the main macro) is found in the end of the last macro
"Sub AddCartonLabels(ws As Worksheet)"
Change the lines:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

As the extra lines are found in the "Sub ModFiles" macro and when all lines in macro 4 is processed macro jumps back to "Sub ModFiles" and then do the

Please Login or Register  to view this content.

before it opens the next file in the folder.

Well I must say I did learn quite a bit from your problem and interesting to see how one can split a long macro into several small stand alone macros.

Alf

i remodeled the macro but still has:

run time erro '9':
subsciption out of range

i'm attaching the new macro file and also my excel file that i'm working to convert

Changed macro a bit, did a run letting the macro modify the uploaded usa-20-xxxxx file. As I'm uploading both files you can see if
the modified usa-20-xxxx file has the layout as you expect.

Alf

it works.

thank you so much for taking your time to help me again and again. you are really Amazing !