Copy Data from First Sheet of Multiple XLS Files in 1 Folder and Copy to 1 New XLSX Sheet

tiredtea · May 11th 2020

I have spent 10 hours trying and trying my hand at this code. It seems like a repetitive question after scouring the web yet any code I came across gave me some kind of issue, even with hours of tinkering. That being said - SOS

>I have hundreds of workbooks in a folder on my X drive (X:\Purchasing.330\ERP\GRC\AACG\MSIT2 Incident Detail Reports)

>They all only have one worksheet, titled "Incident Report"

>They have a various number of lines, all typically maxing out at line 10,027.

>Each workbooks' data starts at line A28.

>Each column has data to column AA (last column with data. Column AB and onwards is empty)

I just want to copy all of this data into my original open sheet (Sheet1) without any line gaps in the data. Just an excel 2010 worksheet. Should the workbook I'm copying into be open or close? I'm good with anything.

The header of the first document should be copied but the header from the remaining documents should be left alone. The header starts on line 1 and ends on line 27. The remaining data starts at line 28.

As an aside, these files all downloaded as as an Excel HTTP Page? So every time you try to open one of the files, you get the error that I have attached below. But if you click "Yes", it opens as expected. Is that an underlying issue?

Thank you SO much in advance. This community is incredible.

tiredtea · May 11th 2020

Here's a file that may work better. This one only has 3k lines but all the others typically tap out at 10k, like I said above. Posting a screenshot so you can see how the file should open and look!

All of the hundreds of files will start with the same name "Access Incident Details Extract Report_"

Mumps · May 11th 2020

I'm having trouble opening the Report file. I get an error message that the file is in a different format than that specified by the file extension. The file extension is "xls" which is an older Excel extension. Please check your file and perhaps save it with an "xlsx" extension the same as your Book1 file and try attaching it again.

tiredtea · May 12th 2020

Hi Mumps,

Exactly. When I download all of the files, they download as XLS. So I was trying to figure out how to make the macro run on XLS files. Anyways, I learned how to batch convert them all to XLSX today so it's fine! I can convert them first and then run the macro. Attached is a XLSX copy of the data.

Mumps · May 12th 2020

First make sure that all your source files have an "xlsx" extension. Place this macro in a regular module in the Book1 file and run it from there.

Code

Sub CopyData()
Application.ScreenUpdating = False
Dim desWS As Worksheet, srcWB As Workbook, LastRow As Long, cnt As Long
Set desWS = ThisWorkbook.Sheets("Sheet1")
Const strPath As String = "X:\Purchasing.330\ERP\GRC\AACG\MSIT2 Incident Detail Reports\"
ChDir strPath
strExtension = Dir(strPath & "*.xlsx")
Do While strExtension <> ""
Set srcWB = Workbooks.Open(strPath & strExtension)
cnt = cnt + 1
If cnt = 1 Then
With Sheets("Access Incident Details Extract")
.UsedRange.Copy desWS.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
desWS.Columns.AutoFit
End With
Else
With Sheets("Access Incident Details Extract")
LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
.Range("A28:AA" & LastRow).Copy desWS.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
End If
srcWB.Close savechanges:=False
strExtension = Dir
Loop
Application.ScreenUpdating = True
End Sub

Display More

Copy Data from First Sheet of Multiple XLS Files in 1 Folder and Copy to 1 New XLSX Sheet

Share

Tags