Merging 200+ files

[Chris]

I have 203 files that have identical headers (fields). Is there an
easy way to select them all and merge them? I go to data, get external
data, and import text file. This is a tough way to do it...
The files are all comma delimited. Any help would be great.
Chris

 

[J.E. McGimpsey]

I have 203 files that have identical headers (fields). Is there an
easy way to select them all and merge them? I go to data, get external
data, and import text file. This is a tough way to do it...
The files are all comma delimited. Any help would be great.
Chris

A macro could certainly be written to automate this. Are all the
files in one folder? How should they be merged - each file a
separate sheet? Add records from each file to one sheet? Consolodate
the data?

 

[Chris]

All of the files are in the same folder. Currently I am merging them
all into the same worksheet to consolidate the data.

 

[J.E. McGimpsey]

All of the files are in the same folder. Currently I am merging them
all into the same worksheet to consolidate the data.

Is there a question there?

If you're looking for a way to automate the merging, here's one way:

Public Sub MergeFolderToWorkbook()
Const MYFOLDER As String = _
"OS X Drive:Users:johnocuments:XL Scratch:TestFolder:"
Dim fNames() As String
Dim wkbk As Workbook
Dim indexRange As Range
Dim i As Integer
Dim j As Integer
Dim fName As String

With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With

fName = Dir(MYFOLDER, MacID("XLS8"))
If fName <> "" Then
With Workbooks.Add(xlWBATWorksheet)
.SaveAs FileName:="Consolidation" & _
Format(Date, "yyyymmdd.xl\s")
With .Worksheets(1)
.Range("A1:B1").Value = _
Array("Workbooks", "Sheets")
Set indexRange = .Range("A2")
End With
Do While fName <> ""
i = i + 1
ReDim Preserve fNames(1 To i)
fNames(i) = fName
fName = Dir()
Loop
For i = LBound(fNames) To UBound(fNames)
Application.StatusBar = "Merging File " & i & _
": " & fNames(i)
Set wkbk = Workbooks.Open( _
FileName:=MYFOLDER & fNames(i))
indexRange.Value = "'" & fNames(i)
For j = 1 To wkbk.Worksheets.Count
If Application.CountA( _
wkbk.Worksheets(j).UsedRange) > 0 Then
wkbk.Worksheets(j).Copy _
after:=.Sheets(.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = Left(wkbk.Name, 28) _
& Format(j, "000")
On Error GoTo 0
indexRange.Offset(0, 1) = "'" & _
ActiveSheet.Name
Set indexRange = indexRange.Offset(1, 0)
End If
Next j
wkbk.Close savechanges:=False
Set indexRange = indexRange.Offset(1, 0)
Next i
End With
indexRange.Resize(1, 2).Columns.AutoFit
Else
MsgBox "no XL files found"
End If
With Application
.EnableEvents = True
.DisplayAlerts = True
.StatusBar = False
.ScreenUpdating = True
End With
End Sub

 

[Chris]

All of the files are in the same folder. Currently I am merging them
all into the same worksheet to consolidate the data.

Thanks so much!!! You are good!! I think I only have one more
question. What is the MacID for a .csv file? I am getting the error of
No XL files found. Even if I use MacID(" ") I still get that error.
Which as I understand the No XL files is telling me that all of the
files in the folder are done.

 

[J.E. McGimpsey]

Thanks so much!!! You are good!! I think I only have one more
question. What is the MacID for a .csv file? I am getting the error of
No XL files found. Even if I use MacID(" ") I still get that error.
Which as I understand the No XL files is telling me that all of the
files in the folder are done.

MacID depends on the application that writes the file (it's just the
standard Mac File Type). You can omit the MacID and the routine will
grab all of the files in the folder.