Open Multiple Files & Paste Data

[jasper005]

Hello everybody,

I have just received a large quantity of excel files that I need to amalgamate into one excel document.

Does anybody know how I can create a macro that will open all excel files in one folder, one at a time, and paste the contents into another excel file (in a different folder)underneath the last row inserted?

Any help much appreciated.

Thanks,
Jasper

[Oorang]

How many worksheets to a file?

[jasper005]

Hi,

There is only one worksheet per file.

Cheers,
Jasper

[Oorang]

Something like this might get you started:Option Explicit
Sub Example()
Const strRootFolder_c As String = "C:\Test\"
Const lngLwrBnd_c As Long = 1
Const lngOffset_c As Long = 1
Dim fs As Office.FileSearch
Dim lngFileIndex As Long
Dim wbNew As Excel.Workbook
Dim wsTarget As Excel.Worksheet
Dim wbCrnt As Excel.Workbook
Dim wsOne As Excel.Worksheet
Set fs = Excel.Application.FileSearch
fs.NewSearch
fs.FileType = msoFileTypeExcelWorkbooks
fs.LookIn = strRootFolder_c
fs.Execute
If fs.FoundFiles.Count < lngLwrBnd_c Then
VBA.MsgBox _
"Cannot find any workbooks in the specified root folder. Please check to make sure you have excel workbooks in the location specified. Operation aborted.", _
vbExclamation Or vbSystemModal, "No Workbooks Found"
Exit Sub
End If
Set wbNew = Excel.Workbooks.Add
Set wsTarget = wbNew.Worksheets(lngLwrBnd_c)
For lngFileIndex = lngLwrBnd_c To fs.FoundFiles.Count
Set wbCrnt = Excel.Workbooks.Open(fs.FoundFiles(lngFileIndex), False, _
False, Password:="foo")
Set wsOne = wbCrnt.Worksheets(lngLwrBnd_c)
wsOne.UsedRange.Copy wsTarget.Cells(wsTarget.UsedRange.Rows.Count + _
lngOffset_c, lngLwrBnd_c)
wbCrnt.Close False
Next
VBA.MsgBox "All worksheets have been merged.", vbInformation Or _
vbSystemModal, "Operation Complete"
End Sub

[jasper005]

Thank you Oorang that is perfect, worked first time.

What would I need to change to make it find .csv files instead of excel files? I would still like it to copy them into an excel workbook.

Thanks again!