Loop through multiple files to build one file

Locoguy

Board Regular
Joined
Jan 25, 2012
Messages
107
Hello everyone... been some time since I last posted in this forum... Hope everyone is doing well...

I have a question:

I have a bit of code that will allow me to "point" to a folder where I have placed some .dat files to be processed and process each of the files until all have been done. Now I have a situation where I have a folder of files where some of the data I'm processing is fragmented into separate files. Each file name is similar except for the extension at the end of each file name "ie: filename_1, filename_2, etc). What I'm trying to do is IF there are separate files for a given file name, I want to combine the data into one file or onto one tab in the workbook my code is running from. Any suggestions as to how I might achieve this ?

Thanks !

Loco

With Application.FileDialog(msoFileDialogFolderPicker) ' open file dialog box and select the folder that contains the .dat files.
.InitialFileName = Application.DefaultFilePath & "C:\Users"
.Title = "Please select the folder where the *.DAT data Files are located:"
.InitialFileName = InitialFoldr$
.Show

If .SelectedItems.Count <> 0 Then
xDirect$ = .SelectedItems(1) & ""
End If

End With


p = xDirect$ & "/*.dat" ' This will ONLY open the .dat files, can be modified for other file formats
x = GetFileList(p)

For k = LBound(x) To UBound(x) ' all of the files in the folder

sFileName = xDirect$ & x(k)
If sFileName = "False" Then Exit Sub ' Exits if there are no files in the folder
Workbooks.OpenText FileName:=sFileName _
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1) _
, TrailingMinusNumbers:=True

Set WBDat = ActiveWorkbook 'Sets the active workbook equal to WBDat or WorkBook - data file

' MsgBox "active workbook" & WBDat



If WBDat Is Nothing Then
MsgBox ".DAT data file not open"
Exit Sub
Else
End If


file_name = sFileName


strFileFullName = ActiveWorkbook.FullName
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Forum statistics

Threads
1,223,248
Messages
6,171,027
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top