Need Help Combining Workbooks!!

[dmontez]

I have a data source the only allows me to download 500 listings at one time. I have created a marco to get all of the data but them am left with about 80 workbooks of info that i need to be in one sheet.

Thanks for any help on this one!

 

[Jon von der Heyden]

Hello

This thread should get you started: http://www.mrexcel.com/forum/showthread.php?p=1768124

More specifically refer to post 4 and amend the code to suit. I would amend for you but you would need to specify the ranges being occupied in each of the 80 files (i.e. sheet name if relevant and then which columns and cells are occupied with needed content).

Change the part that picks up only row 10 to your specific range. Also, remove the bit that checks that the file name contains "invoice". Make sure that your 80 files are housed in a single folder (with no other files housed within it), and change fldPath to the path of that particular folder.

Hope this helps.

 

[dmontez]

THANKS! When i download the data it is automatically saved as a CSV and then given a name by the computer. "bensonka1.csv_20090113_135428.csv" so basically the date and a random number it looks like. This also changes the sheet name to that same name everytime so all 80 workbooks have different sheet names.

The columns i need are A thru CO. The rows occupied change from download to download but are never more than 1000.

 

[Jon von der Heyden]

Hello again

Copy this code to a new module in whatever workbook you choose to consolidate the data into (a new workbook if you prefer). You need to have a blank sheet open when you run the code. It will rename the blank sheet "Raw Data" so it assumes that you do not already have a sheet with this name.

This code will only pick up all .csv files in fldPath. Make sure you change this path to reflect the full path to the folder that houses the .csv files that you want to consolidate. Make sure that the folder does not house other .csv files that you do not want to consolidate.

I suggest that if you are consolidating this into an existing workbook then please make a copy of the workbook as a back-up.

Here is the code:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ConsolidateWB()<br><br><SPAN style="color:#00007F">With</SPAN> Application<br>    .ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    .EnableEvents = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br><SPAN style="color:#00007F">Dim</SPAN> fso <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, fld <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, fil <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, fldPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, wbSrc <SPAN style="color:#00007F">As</SPAN> Workbook, wbCur <SPAN style="color:#00007F">As</SPAN> Workbook<br><br>fldPath = "C:\Excel\Files\" <SPAN style="color:#007F00">'<------change to location where your files are stored</SPAN><br><br><SPAN style="color:#00007F">Set</SPAN> wbCur = ActiveWorkbook<br>ActiveSheet.Name = "Raw Data"<br><br><SPAN style="color:#00007F">Set</SPAN> fso = CreateObject("Scripting.FileSystemObject")<br><SPAN style="color:#00007F">Set</SPAN> fld = fso.getfolder(fldPath)<br><br><SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> ErrHandler<br><br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> fil <SPAN style="color:#00007F">In</SPAN> fld.Files<br>    <SPAN style="color:#00007F">If</SPAN> InStr(LCase(fil.Name), ".csv") > 0 <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> wbSrc = Application.Workbooks.Open(fil.Path)<br>            <SPAN style="color:#00007F">With</SPAN> wbSrc.Sheets(1)<br>                .Range("A2:CO" & .Range("CO" & Rows.Count).End(xlUp).Row).Copy wbCur.Sheets("Raw Data").Range("A" & Rows.Count).End(xlUp).Offset(1)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            wbSrc.Close <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">Next</SPAN> fil<br><br><br>ErrHandler:<br>Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br><br>End <SPAN style="color:#00007F">Sub</SPAN></FONT>