Macro to open multiple files

[paperwings25]

I run 13 reports from my Accounting software every morning. I then save them into a folder dated the current date. I would love to generate a macro that would open up all these files at once and copy and paste the data that I need from them into the master file. Is this possible to do?

Here is my sample master file. SAMPLE TEST.xls.

Each one of the tabs has a report that I open. If I could take the data from each one of the reports and copy/paste it into this master file, that would be wonderful! It would cut out a lot of my time and would allow me to be a lot more efficient.

[GeneralDisarray]

Here's a look at something similar I did awhile back (had to collect data from 2.5 years worth of weekly reports...so...lots of files had to be opened/copied from/closed again).

THis snippet of code is by no means state of the art (first time I've looped over a directory since microsoft depricated application.filesearch methods (which happened 4 years ago))

But, you can hack away at it and get something useful out.

Please Login or Register  to view this content.

[paperwings25]

Thanks General. It's something to work with. I'll see if I can't tweak it and get it to work somehow unless someone else chimes in and gives me a nice, perfect macro!!

[paperwings25]

Here's a look at something similar I did awhile back (had to collect data from 2.5 years worth of weekly reports...so...lots of files had to be opened/copied from/closed again).

THis snippet of code is by no means state of the art (first time I've looped over a directory since microsoft depricated application.filesearch methods (which happened 4 years ago))

But, you can hack away at it and get something useful out.

Please Login or Register  to view this content.

I'm a newbie when it comes to Visual Basic. Can you edit your Code to cater towards more of my needs? Where I need to input my own data, you could just put that in parenthesis.

[paperwings25]

Any help on this?

[jaslake]

Hi paperwings25

It's a bit difficult sharing an approach since you haven't given basic required information.

What are the File Names of your 13 Accounting files?

What's the Sheet Name that contains the required data in each of the 13 files?

What Accounting File Name/Sheet Name goes into which Master File worksheet?

Are the file structures of the Accounting File worksheets the same in all respects as the file structures of the Master File worksheets?

If you want this...

someone else chimes in and gives me a nice, perfect macro

you'll need to provide samples of the Accounting File workbooks so whatever code we come up with can be tested.

[paperwings25]

Does this file help? I tried to break it down and answer all your questions above.

Reports.xlsx

[GeneralDisarray]

hello again. Quick note:

This macro assumes all the files you want to access are in one place (you mentioned in your first post that you save 13 reports to one folder).

Once you give it the PATH to this folder (like C:\MyReports\Today). It simply opens all .xlsx files in that folder (directory), accesses sheet1, and closes them.

i suggest you Step-Through (run it line-by-line) to see what is happening. Then if you need help with a particular function (like when each sheet is open i need to paste all rows from XYZ tab to the master books 123 tab....) it will be easy to help you.

Please Login or Register  to view this content.

[paperwings25]

Thank you. Does the summationbook need to be the title of my Master Balancing File? There are 4 Balancing files that I have. One for each fo the 4 companies. 13 reports per company.

[paperwings25]

hello again. Quick note:

This macro assumes all the files you want to access are in one place (you mentioned in your first post that you save 13 reports to one folder).

Once you give it the PATH to this folder (like C:\MyReports\Today). It simply opens all .xlsx files in that folder (directory), accesses sheet1, and closes them.

i suggest you Step-Through (run it line-by-line) to see what is happening. Then if you need help with a particular function (like when each sheet is open i need to paste all rows from XYZ tab to the master books 123 tab....) it will be easy to help you.

Please Login or Register  to view this content.

This is the part I am having trouble with right now. I am having a hard time copying and pasting specific data to a specific tab in the Master file. When I ran your code snippet, it accessed the folders correctly, so I know that's good. Now I need it to do the magic after these files are opened.

[GeneralDisarray]

This could be handled another way, the line that says
[code]set summationbook = thisworkbook[\code] means the variable "summationbook" will point to the actual book containing the macro for the duration of the routine.

So, you could copy this macro to each of the four "master" books. Each would be the summation book when that book ran the macro -- & each macro would then need to point to the folder for that "master" books 13 reports.

But, that's something you can enter when prompted.

--
what ranges are you looking to copy paste? if you can provide a basic example i could help you get started.

[paperwings25]

This could be handled another way, the line that says
[code]set summationbook = thisworkbook[\code] means the variable "summationbook" will point to the actual book containing the macro for the duration of the routine.

So, you could copy this macro to each of the four "master" books. Each would be the summation book when that book ran the macro -- & each macro would then need to point to the folder for that "master" books 13 reports.

But, that's something you can enter when prompted.

--
what ranges are you looking to copy paste? if you can provide a basic example i could help you get started.

I just Ctrl + A(Or I click the top left corner that selects the entire file) and select all when I do all the copying and pasting. So for company 1, in tabs Page 2, Page 3, Page 4, Page 6, nVision TB, Exh 1, Exh 7, Exh 8_1, Exh 8_2, Exh 9, Exh 12, Exh Cap GL, and Exh NII, I just copy all data from my 13 reports and paste them into those tabs that correspond with each report.

Will I have to use IF functions to get the data copied to the correct tabs in the Master File? Like IF("BBPG2",Selection.Copy to Page 2)? I obviously know that is in incorrect code format, but I was just trying to give an example. I am just curious how I will get the correct report to the correct tab in the Master File.

[paperwings25]

[QUOTE=GeneralDisarray;2617437]This could be handled another way, the line that says
[code]set summationbook = thisworkbook[\code] means the variable "summationbook" will point to the actual book containing the macro for the duration of the routine.

So, you could copy this macro to each of the four "master" books. Each would be the summation book when that book ran the macro -- & each macro would then need to point to the folder for that "master" books 13 reports.QUOTE]

This is what I am going to do. I'll just create the macro for each master workbook.

[GeneralDisarray]

check out this similar post:

http://www.excelforum.com/excel-prog...ml#post2617507

[GeneralDisarray]

Ok, so if you want Excel to pop open each sheet in sequence...and copy tab-for-tab everything...try this.

Like the other post:

Download the zip file and unzip it.
Then, run the macro (which is in the mastersheet_withMacro file)


The key is the Parts are kept in a folder which sits in the same directory as the master sheet...boo-ya-grand-ma don't forget to rate

Please Login or Register  to view this content.

[paperwings25]

Ok, so if you want Excel to pop open each sheet in sequence...and copy tab-for-tab everything...try this.

Like the other post:

Download the zip file and unzip it.
Then, run the macro (which is in the mastersheet_withMacro file)


The key is the Parts are kept in a folder which sits in the same directory as the master sheet...boo-ya-grand-ma don't forget to rate

I appreciate the help. I tried to run it and it failed. I debugged it and it is failing at "Workbooks.Open (myPath & "\" & CurrentFileName)" underneath the Do function. Any idea what I am doing wrong?

[GeneralDisarray]

Wereyou careful to leave the "ComponentSheets" folder in the directory with the worksheet that is running the macro?

The lines:

Please Login or Register  to view this content.

Require this to be the relative position of the master / component sheets...


EDIT: I've just uploaded another zip file which will help with this point -- after you unzip it, open the folder and run the macro in the master sheet...the ComponentSheets folder will be right were it needs to be.

_____________________________________________________________________________________________________________________________________________________________________


Also, i noticed that i left the file extension as ".xls" instead of ".xlsx" which is what the files actually are...but even with that issue it seems to run just fine for me -- but try changing it to ".xlsx" (unless you are using the '03 version of excel that does indeed use the .xls extension).


Other than that, I suggest that while you are stepping through the code you "mouse-over" the CurrentFileName variable (or add a watch on this variable) and verify that it does indeed pick up the files (at this line)

Please Login or Register  to view this content.

Is "CurrentFileName" is still showing a blank value "" after this line?

[paperwings25]

I saved your Zipped folder to the desktop. I opened it up, then I opened the master file and ran the macro. This is the error I got...

Screenshot.xlsx

When I scroll over "CurrentFileName" I receive CurrentFileName = "". The myPath is a different directory then what I saved it as. This has to be the issue. Why is it showing that myPath is the temporary Directory when I have saved it on my desktop and opened it from there? I also cannot find this directory that it says myPath is.

[GeneralDisarray]

are you running it from the zip file. That's what it looks like in that screenshot

'C:\temp\Temporary Directory 2 for TEST2_Folder.zip\Test2.......

you need to extract (unzip the file)...that will place a folder on your desktop named TEST2_Folder....open THAT folder and run the file from there.

does that make sense?

[paperwings25]

Yeah, I attempted that before I posted. I just tried it again and it worked!

Now what does this mean to a newbie to apply it to my actual reports and such that I can use every day?

Let's say the directory that my 13 reports and Master Balancing File is in "C:\Balancing\10-12-2011". Since I create a new folder for every day, is there anything I need to change in the code to reflect this? Will I need to change it daily? If so, what do I need to change?

[paperwings25]

Also, your masterfile macro pulls the data from the 4 test files and applies it to the same sheet. I need each report to copy on to a specific tab. I'll attach the all 13 reports here in a second.

[GeneralDisarray]

glad to hear it worked.

do you plan to make a new "Master sheet" every day also? If so you would need to make sure each master sheet carried the macro (wouldn't matter what you called the sheet, would only need to contain the macro).

Then, since it's set up to reference a specific "ComponentSheets" folder (as you are now well aware of) you could place the master and the component sheets inside this daily folder....

see my own attached shot...picture is worth a thousand words here

Also...just a geeky style point....when you name by date try the 2011.10.12 format instead of the month-day-year thing.

why? because if you view the balancing folder sorted by name (and they are named year-month-day) it will automatically be sorted by date too!!!

[paperwings25]

Here are the 13 reports that I run for a single company. Obviously I have cleared any contents for confidential purposes. I will attach a file that lays out which report goes to which tab in the master file(which I will also include).

BBPG2-2011-09-30.xls
BBPG3-2011-09-30.xls
BBPG4.xls
BBPG6-2011-09-30.xls
CONDSTBS-2011-09-30.xls

Edit: Can only do 5 at a time, so I will do them in 3 posts.

[paperwings25]

BBEXH1-1-2011-09-30.xls

BBEXH7-2011-09-30.xls

BBEXH8-1-2011-09-30.xls

BBEXH8-2-2011-09-30.xls

BBEXH9-2011-09-30.xls

[paperwings25]

BBEXH12.xls

BBEXHCGL-2011-09-30.xls

BBEXHNII-2011-09-30.xls

Reports.xlsx

SAMPLE TEST - Master Balancing File.xls

Here they all are. BBPG2, BBPG3, BBPG4 are the only ones that have different name other than "Sheet1" as where the data is pulled from. I will just manually change these 3 workbooks' sheets to Sheet1 to make it easier to pull the data.

[paperwings25]

glad to hear it worked.

do you plan to make a new "Master sheet" every day also? If so you would need to make sure each master sheet carried the macro (wouldn't matter what you called the sheet, would only need to contain the macro).

Then, since it's set up to reference a specific "ComponentSheets" folder (as you are now well aware of) you could place the master and the component sheets inside this daily folder....

see my own attached shot...picture is worth a thousand words here

Also...just a geeky style point....when you name by date try the 2011.10.12 format instead of the month-day-year thing.

why? because if you view the balancing folder sorted by name (and they are named year-month-day) it will automatically be sorted by date too!!!

No, I do not plan to make a new master balance every day. I have already made a macro that adds another tab to represent each day, so I just open up the Balancing File from yesterday, hit that Macro and it adds a new tab with the formulas. What I hope to accomplish is the copying/pasting of the 13 reports to go into the individual tabs on the Balancing File and then the tab that I created for that day will populate all the check variances and the formulas will be applied. I won't have to manually open each individual report for all 4 companies and copy/paste, copy/paste, copy/ paste, copy/paste....Tediousness.

[paperwings25]

Is this macro too hard to create? I understand if it is. I had little hopes to get one made, so if one does, then that's just fantastic.

[GeneralDisarray]

hmm... i think you're going to have to do most of the leg work on this one...you're going to have to fine-tune it to work exactly with what you are doing.

You really can finish it up with practice (stepping through, changing things one line at a time...it's more fun than it sounds). Also, if this is something new to you...what better way to learn.

Actually, i never used VBA until about 5 years ago i hade to clean and combine about 80 multi-sheet workbooks for a total of about 40K lines of data...

Necessity is the best motivator eh?

good luck.

[MINIYUKI1007]

Hello,
i'm trying to write a macro which open many files and copy paste all data into one worksheet. Your program above really useful to me. However, as each of my file are download from another software, all worksheet in the files has auto given a name. example “SK” instead of “Sheet1”.
meaning that i can't use - Set SourceData = sourceBook.Worksheets("Sheet1")

Please help.

Thank you!

[GeneralDisarray]

well, if you know the name you would use that name in place of Sheet1. However you can skip that altogether and use an index, or access all the worksheets as a 'collection' using a For Each type loop.

Option1: Identify by worksheet name
example:
Set SourceData = sourceBook.worksheets("SHEET_NAME_HERE")

Option2: Identify by worksheet index
example (this would access the first or left-most worksheet)
Set SourceData = sourceBook.worksheets(1)

Option3: Access all sheets in the book. This would allow you to loop through all the sheets to do whatever you need with them.

For Each sourceData in sourceBook.worksheets
'----------------------
' YOUR CODE HERE
'----------------------
next sourceData

[GeneralDisarray]

Here is a full example of this (uses a different approach - you need to enable the "Microsoft Scripting Runtime" for this module to work).

This macro looks through a directory with (or without) sub-folders included. It will open each file in the folder, copy the contents from the worksheets of that files to a single sheet.

I was using this module to do something else, but edited it to do what i think you meant to do. Attached book contains this module (run dumpDirectoryDriver to try it out).

enjoy
--

Please Login or Register  to view this content.

[MINIYUKI1007]

thanks for the fast respond.
if i'm using Option3, meant all files saved in the sourcebook will be open, regardless of what worksheet name in all files. Right?

[MINIYUKI1007]

thanks for the fast respond.
meant if i'm using option3, all files in the sourcebook will be open, regardless of what worksheet name in all the files. Right?

[MINIYUKI1007]

Hello,
i try to use the above macro program to run for my reports. No error during run, but, after finished copy, only have 1 culumn with data. Why?

thanks in advance for the help.

[MINIYUKI1007]

Hello,
This program is perfect. thank you so much.

One more help from you. Do you have a sample macro to filter data which value not equal to 0?
Example, I need to filter the data not equal to 0 from column “M”

once again, Thanks for the helps.

[arlu1201]

MINIYUKI1007,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

[MINIYUKI1007]

Noted.
Thanks!