How to pull text from text file into Excel spreadsheet using the filename.txt using VBA

[kdjonesmtb]

How can use VBA/macro in Excel to pull text file into Excel spreadsheet specified column using the filename.txt

Example

I have a spreadsheet with over 900 columns of textfile names that contain SQL code in text files

I want to automate pulling text out each file -- and place it in the column associated with the text filenames. The Text files are located on a OneDrive on my laptop --- want the specific syntax to pull the folder path correctly as well

thanks

 

[DanteAmor]

I guess the file names are in row 1 and start in cell A1, then try this:

Sub read_txt_files()
  Dim ff As Integer, col As Long, lngFileLen As Long, arrLines As Variant
  Dim strWholeFile As String, strFile As String, strFolder As String
  
  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Selecct folder"
    .AllowMultiSelect = False
    .InitialFileName = "C:\trabajo\" 'ThisWorkbook.Path
    If .Show <> -1 Then Exit Sub
    strFolder = .SelectedItems(1)
  End With

  For col = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
    strFile = strFolder & "\" & Cells(1, col).Value
    If Dir(strFile) <> "" Then
      ff = FreeFile   'returns a handle to use in the Open statement that follows
      lngFileLen = FileLen(strFile)
      strWholeFile = Space(lngFileLen)  'create enough room to hold entire file
      Open strFile For Binary Access Read As #ff
        Get #ff, , strWholeFile  'variable now holds all file data
      Close #ff
      'Now split the string at each occurrence of vbCrLf (standard line delimiter in Windows OS)
      arrLines = Split(strWholeFile, vbCrLf)
      Cells(2, col).Resize(UBound(arrLines) + 1, 1).Value = Application.Transpose(arrLines)
    End If
  Next
End Sub

 

[kdjonesmtb]

Sorry to bother you --- I am getting the following error "Run Time Error '53' File not found

The workbook is located in the path --- Do I need to include the Excel filename in the InitialFileName; there are text files in this folder as well

Thanks

Sub read_txt_files()
Dim ff As Integer, col As Long, lngFileLen As Long, arrLines As Variant
Dim strWholeFile As String, strFile As String, strFolder As String

With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select folder"
.AllowMultiSelect = False
.InitialFileName = "C:\Users\kgittenjones\Documents\" 'ThisWorkbook.Path
If .Show <> -1 Then Exit Sub
strFolder = .SelectedItems(1)
End With
For col = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
strFile = strFolder & "\" & Cells(1, col).Value
If Dir(strFile) <> "" Then
ff = FreeFile 'returns a handle to use in the Open statement that follows
lngFileLen = FileLen(strFile)
strWholeFile = Space(lngFileLen) 'create enough room to hold entire file
Open strFile For Binary Access Read As #ff
Get #ff, , strWholeFile 'variable now holds all file data
Close #ff
'Now split the string at each occurrence of vbCrLf (standard line delimiter in Windows OS)
arrLines = Split(strWholeFile, vbCrLf)
Cells(2, col).Resize(UBound(arrLines) + 1, 1).Value = Application.Transpose(arrLines)
End If
Next
End Sub

 

[kdjonesmtb]

Disregard - I figured it out … If I want the result column b instead of a; what is the correct syntax?