Importing info from all documents in a folder

[wmcneely]

I want to write a macro that looks in every file in a particular folder (preferably specified by text written in one of the cells in the active spreadsheet), copies a certain range of cells in each document, and pastes these values in a single column.

All documents in these folders are Excel documents formated in the same way.

Any ideas?

 

[Domenic]

Try...

<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> CombineWorksheets()<br><br>    <SPAN style="color:#007F00">'Declare the variables</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> strPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> strFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> wkbSource <SPAN style="color:#00007F">As</SPAN> Workbook<br>    <SPAN style="color:#00007F">Dim</SPAN> wksSource <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> wksDest <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> SourceRange <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> SourceRowCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> SourceColCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> NextRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> CalcMode <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#007F00">'Change the settings for Calculation, EnableEvents, and ScreenUpdating</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Application<br>        CalcMode = .Calculation<br>        .Calculation = xlCalculationManual<br>        .EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>        .ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    <SPAN style="color:#007F00">'Define the active worksheet</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> wksDest = ActiveWorkbook.ActiveSheet<br>    <br>    <SPAN style="color:#007F00">'Define the path to your folder (change the cell reference accordingly)</SPAN><br>    strPath = wksDest.Range("A1").Value<br>    <br>    <SPAN style="color:#007F00">'Add a backslash at the end of the path, if one does not exist</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Right(strPath, 1) <> "\" <SPAN style="color:#00007F">Then</SPAN> strPath = strPath & "\"<br>    <br>    <SPAN style="color:#007F00">'Call the first file from the folder</SPAN><br>    strFile = Dir(strPath & "*.xls")<br>    <br>    <SPAN style="color:#007F00">'Start to copy the data in Row 3</SPAN><br>    NextRow = 3<br>    <br>    <SPAN style="color:#007F00">'Loop through each file in the folder</SPAN><br>    <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> Len(strFile) > 0<br>    <br>        <SPAN style="color:#007F00">'Open the current workbook</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> wkbSource = Workbooks.Open(strPath & strFile)<br>        <br>        <SPAN style="color:#007F00">'Define the first sheet of the current workbook (change accordingly)</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> wksSource = wkbSource.Worksheets(1)<br>        <br>        <SPAN style="color:#007F00">'Find the last row in the current workbook</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> wksSource.UsedRange<br>            LastRow = .Rows.Count + .Rows(1).Row - 1<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <br>        <SPAN style="color:#007F00">'Define the copy range in the current workbook (change accordingly)</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> SourceRange = wksSource.Range("A1:A" & LastRow)<br>        <br>        <SPAN style="color:#007F00">'Count the number of rows in the source range</SPAN><br>        SourceRowCount = SourceRange.Rows.Count<br>        <br>        <SPAN style="color:#007F00">'Count the number of columns in the source range</SPAN><br>        SourceColCount = SourceRange.Columns.Count<br>        <br>        <SPAN style="color:#007F00">'If there's not enough rows in the worksheet, exit the sub</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> NextRow + SourceRowCount - 1 > wksDest.Rows.Count <SPAN style="color:#00007F">Then</SPAN><br>            MsgBox "There are not enough rows in the worksheet...", vbExclamation<br>            <SPAN style="color:#00007F">GoTo</SPAN> ExitSub<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <br>        <SPAN style="color:#007F00">'Copy the file name to Column A</SPAN><br>        wksDest.Cells(NextRow, "A").Value = strFile<br>        <br>        <SPAN style="color:#007F00">'Copy the values from the source range</SPAN><br>        wksDest.Cells(NextRow, "B").Resize(SourceRowCount, SourceColCount) = SourceRange.Value<br>        <br>        <SPAN style="color:#007F00">'Close the current workbook, without saving it</SPAN><br>        wkbSource.Close savechanges:=<SPAN style="color:#00007F">False</SPAN><br>        <br>        <SPAN style="color:#007F00">'Find the next available row</SPAN><br>        NextRow = NextRow + SourceRowCount<br>        <br>        <SPAN style="color:#007F00">'Call the next file from the folder</SPAN><br>        strFile = Dir<br>        <br>    <SPAN style="color:#00007F">Loop</SPAN><br>    <br>ExitSub:<br><br>    <SPAN style="color:#007F00">'Restore the settings for Calculation, EnableEvents, and ScreenUpdating</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Application<br>        .Calculation = CalcMode<br>        .EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>        .ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>