open all files from a folder

**Italia2006** · 08-04-2009, 09:48 AM

Hello,

I have created a Spreadsheet that does all of the calculations, analysis, graphs, etc. that i need; however every time I wish to use this spreadsheet I need to first open up a spreadsheet with data, copy it and then paste it into the original spreadsheet. While this is not horrible, it is quite tedious to do several thousand times.
Thus, I was wondering if it were possible to create a Macro that would open up all files in a specific folder, then select each one and paste the relevant data into my original spreadsheet. I know this possible for specific files; for example, I have created a Macro that will select files data.xls, dats1.xls, data2.xls, and paste these into the spreadsheet, but I was wondering if there was a way to generalize this so that it will simply open every file regardless of the name or how many files there are in the specific folder.

Does this make sense?

**Phil_V** · 08-04-2009, 09:51 AM

Take a look at this post to see how to cycle through all the files in a directory.
http://www.excelforum.com/2139404-post3.html

You can then wrap this around a copy / paste operation.
If you need any more assistance after looking at that post please do ask

**Italia2006** · 08-04-2009, 10:56 AM

Phil, the code looks great thanks! however, when I try and run it, nothing happens. I put the files in the same folder as the workbook; are there any more conditions about where the files need to be?

**Phil_V** · 08-04-2009, 11:04 AM

The macro as it stands does not do much anyway other than cycle through all the files in the directory.
Do the Excel workbooks have the extension .xls?

Where my code has;

Please Login or Register  to view this content.

You could put in:

Please Login or Register  to view this content.

Which should present a message box with the workbook name.

If that doesn't work then paste your code here, (remember to put code tags around it when posting

), and I can take a look

**arthurbr** · 08-04-2009, 11:11 AM

Thanks for taking the time to read and comply with the rule

.

**Italia2006** · 08-04-2009, 01:11 PM

The message box does not pop up.

I was expecting this part of your code to actually open the file, but it appears not to.

Please Login or Register  to view this content.

The 'do stuff' that I would like to do is copy a segment of each file that is opened and then paste it into the first available blank sheet of my orginal spreadsheet (baseline). This is the code I have for that.

Please Login or Register  to view this content.

convert1 is the spreadsheet I am currently copying from right now, but ideally I would like to make it general.

Thanks for all the help thus far.

**Phil_V** · 08-05-2009, 06:24 AM

Convert1.xls is the name of the workbook, but which worksheet are you copying from?
Is it always from a sheet with a particular name, or is it always the first / second sheet in the workbook?

**Phil_V** · 08-05-2009, 06:33 AM

Try the following code and let me know how you get on.
The macro is currently written to always copy from the first worksheet in the source file.

Also I have COPIED the structure of your macro, including it's limitations. If you have more than 3 files then it will only copy 3 before failing as there will be no 'blank' sheets left in your destination workbook. The macro can easily be changed to create new sheets if they are needed, but I didn't want to do that without confirming with you exactly what you wanted first

Please Login or Register  to view this content.

**Italia2006** · 08-05-2009, 10:11 AM

This seems to be almost it.
Yes, I plan on copying the data from the first sheet of the workbooks that are opened and the paste that selection into the first available blank sheet in a workbook named Baseline1.2.

When I try and run it an Error comes up saying that a variable is not defined (filepath). Do I need to change this to the name of the folder I wish to open my files from, or something along those lines?

**Phil_V** · 08-05-2009, 10:33 AM

There are no paths to change as it is currently set to operate on the files in the same directory as itself.

It seems as if my brain wasn't quite connected when writting that code, please use this instead as it includes some slight, (but crucial!), changes:

Please Login or Register  to view this content.

**Italia2006** · 08-05-2009, 11:10 AM

well it does seem we're getting closer ... haha
When trying to run your code exactly as it appears a message box appeared saying it was unable open each file, though there were blank shits available.
After fixing the code a bit a message box appears stating: "Finished" however no files were opened. It does appear something is working as the screen flickers for a moment however again the files do not open. Could you think of a possible explanation for this?
If it is possible to just open all the files in the folder I think I could figure out the rest.

**Phil_V** · 08-05-2009, 11:35 AM

Can you tell me exactly what the message box says when it reports it was unable to open a file?
When it reports that it is not related as to whether there are any blank sheets left or not, it just literally means for some reason it couldn't open the file.

In what way did you 'fix' the code? It sounds to me as if you just hid the error.

In the code as I pasted above, please change the line:

Please Login or Register  to view this content.

to be:

Please Login or Register  to view this content.

Then rerun the code, and post here exactly what appears in the Msgbox.
We need to find out why it was unable to open the files.

**Italia2006** · 08-05-2009, 12:11 PM

On second thought, your right, my "fix" wasn't anything more than that.

When I rerun the code it simply states "Unable to open: filename.xls" in a Msgbox with a red X.

My first thoughts were that it may be because the files are on a flashdrive, but I saved them to the hardrive and it still didn't work; or maybe there were certain security settings preventing this but I tried it on 2 different computers and the same message pops up/

**Phil_V** · 08-05-2009, 12:14 PM

Did you implement the code change at the end of my last post?
Also are you saving the workbook containing this macro into the same folder as all the other files are in?

**Italia2006** · 08-05-2009, 12:32 PM

I did implement your change to the code and yes the workbook is saved in a folder along with the files i wish to open.
Sadly, it's just not working = (

**Phil_V** · 08-05-2009, 02:49 PM

and the only thing you get in the message box is the Filename that can't be opened, no further error message?
Can you upload some of the files for me to try as it all works fine here

**Italia2006** · 08-05-2009, 03:25 PM

Yep the only thing the Message Box says is that the file can not be opened.

Here are the files. The Macro is in Convert1 and the files that I'm hoping it can open are the ACTA files (These will all be in one folder with many more ACTA files). Once the files are open I will then run the Macro2 I have in the Convert1 workbook which will edit them and then paste the result into the first available blank worksheet in Baseline1.2. which I have included but does not directly involve the Macro other than needing to be open (This process has changed slightly since we last spoke).

Hopefully it all works on your computer and it's just a security setting that I need to change or something like that I need to find on mine.

**Italia2006** · 08-05-2009, 03:31 PM

The files seem to big to attach.

Here is the code I have

Please Login or Register  to view this content.

You can change Application.Run "Convert1.xls!Macro2" to anything. If it opens up the files in the folder in which you have the worksheet with this macro than I can assume that there specific about my files/computer I need to investigate.
Thanks

If you wish I could also email you the files.

**Phil_V** · 08-05-2009, 03:54 PM

Sorry, the files open fine here, and I'm at home now so using a different computer.

Must be something with your files, computer, or file location

If you ZIP the files you might be able to upload the zip file.

**Italia2006** · 08-05-2009, 04:29 PM

I tried them on two different computers and both times got the Unable to open file Message.

The files are still too big once zipped (barley!), can you think of anything off the top of your head that could be potentially wrong with the files.

**Phil_V** · 08-05-2009, 05:15 PM

I'd be concerned that even zipped they are too large, perhaps that is what is causing the problem. I don't know how many you are trying to zip and upload, but can you just do 2, or even 1?

**Italia2006** · 08-06-2009, 09:51 AM

I made the files a bit smaller and they fit now. Again the macro is in convert, it should open up the ACTA files then there is another macro in Convert that will edit these files and paste them into the first available blank sheet in baseline1.2.

Thanks, hopefully it doesn't work for you! haha

**Phil_V** · 08-06-2009, 10:13 AM

Please use this version of the 'Open_Files' macro.
I can only appologise, it seems I missed specifying the path to the 'Open', which for some reason didn't effect my machine originally!
I have now corrected this, and also improved the error reporting in the code below.

All being well THIS WILL WORK!

As ever, let me know how you get on.

Please Login or Register  to view this content.

**Italia2006** · 08-06-2009, 11:11 AM

works exactly like I wanted! Thank you so much for staying with me and helping these past few days.

Tommaso

**Phil_V** · 08-06-2009, 11:30 AM

Glad to help, and I can only appologise again for the couple of little mistakes I allowed through

open all files from a folder

LinkBack

Thread Tools

Rate This Thread

Display

open all files from a folder

Re: Is this possible?

re: open all files from a folder

re: open all files from a folder

re: open all files from a folder

re: open all files from a folder

Re: open all files from a folder

Re: open all files from a folder

Re: open all files from a folder

Re: open all files from a folder

Re: open all files from a folder

Re: open all files from a folder

Re: open all files from a folder

Re: open all files from a folder

Re: open all files from a folder

Re: open all files from a folder

Re: open all files from a folder

Re: open all files from a folder

Re: open all files from a folder

Re: open all files from a folder

Re: open all files from a folder

Re: open all files from a folder

Re: open all files from a folder

Re: open all files from a folder

Re: open all files from a folder

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions