Hello,
I am currently using VBA code to import multiple .txt and .tab files into excel. Each file has a header row and data. When the macro imports these files, I want it to only use one header row instead of what it is doing now. Is this even possible with variable file names? Here is a copy of the code I am using:
Sub Import_Text()
Dim myFolder, TabFile, fso As Object, fPath As String
' Turn off some Excel functions that are not needed
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
' Import files
Set fso = CreateObject("Scripting.FileSystemObject")
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
If .SelectedItems.Count > 0 Then fPath = .SelectedItems(1) & "\" Else Exit Sub
End With
Set myFolder = fso.GetFolder(fPath).Files
' Open each file sequentially
For Each TabFile In myFolder
If LCase(TabFile) Like "*.txt" Then
' Import data from Text file
With Workbooks.Open(Filename:=TabFile, Delimiter:=1)
With ThisWorkbook.ActiveSheet
ActiveSheet.UsedRange.Copy _
Destination:=.Cells(.Rows.Count, "A").End(xlUp)(2)
End With
.Close SaveChanges:=False
End With
End If
Next TabFile
' clean up
TabFile = vbNullString
' Turn Excel functions back on
With Application
.Calculation = xlCalculationAutomatic
.DisplayStatusBar = True
.EnableEvents = True
End With
End Sub
Please find attached example workbook and source files. Any assistance in this matter is greatly appreciated....Regards... Delta
Bookmarks