Import Multiple Text Files from a Folder using VBA

[AGALLEGOS]

I have been searching for an answer online, and I am getting sporadic results.

I would like to bring in the data from about 8 text files (actually .rpt files) located in a folder on my computer. I would like to have a VBA code that will loop through the folder, import the data from the files and insert them all in the same worksheet -named 'DATA' in succeeding columns. With this data in place, I can run a macro that I have already built. At this point in time, I have to manually open the file in word, run a data-selection macro, go back to excel and run the macro there.

Any help or direction would be greatly appreciated. I will use this code continually, with varying number of files, so it should have the flexibility to import 8 or import 3.

Adam

[patel45]

attach please a sample txt file and a xls file with desired result

[HaHoBe]

Hi, Adam ,

the basic code for doing so might look like this (you may need to alter the separator to suit your needs as I picked semicolon here):

Sub EF982969()
  Dim strFile As String
  Dim strPath As String
  Dim ws As Worksheet
  Dim lngCol As Long
  
  Application.ScreenUpdating = False
  strPath = "C:\Temp\My Files\"  'change to your drive and folder
  If Right(strPath, 1) <> "/" Then
    strPath = strPath & "\"
  End If
  
  Set ws = ThisWorkbook.Sheets("DATA")
  strFile = Dir(strPath & "*.rpt")
  Do While strFile <> ""
    lngCol = ws.Cells(1, Columns.Count).End(xlToLeft).col + 1
    Workbooks.OpenText _
      Filename:=strPath & strFile, _
      DataType:=xlDelimited, _
      semicolon:=True
    ActiveSheet.UsedRange.Copy ws.Cells(1, lngCol)
    Application.CutCopyMode = False
    ActiveWorkbook.Close False
    strFile = Dir()
  Loop
  Application.ScreenUpdating = True
  
  Set ws = Nothing
End Sub

Ciao,
Holger