import hundreds of individual text files into one sheet and put each file as the first cell in each colum

[Dpalkon]

I have hundreds of individual text files that i need to review and edit. they contain machine recipes. The data in the text file is arranged vertically. The first two lines are string data and then 8 lines after are integers. all the files have the same number of lines. Each line has no more than 20 character. I can drag the data very easily into excel but it creates new files for each txt file i import. I just want it to create new a new colume for each text file so i can review all the recipes side by side. I can cut and paste and do all that but my goal would also be to go the other way and take each column and create text files that have a name that's in the first column and the data in all the columns below. Am I crazy for thinking this is possible? If anyone has any suggestions I would love to hear them.
thanks

 

[DanteAmor]

Try this, change "Sheet1" by the name of your sheet

Sub import_text_files()
  Dim sh As Worksheet, wPath As String, wFile As String
  Dim w2 As Workbook, sh2 As Worksheet, k As Long, lr2 As Long
 
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
 
  Set sh = Sheets("Sheet1")
  sh.Cells.Clear
  k = 1
 
  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Selecciona una carpeta"
    .AllowMultiSelect = False
    .InitialFileName = ThisWorkbook.Path
    If .Show <> -1 Then Exit Sub
    wPath = .SelectedItems(1)
    wFile = Dir(wPath & "\" & "*.csv")
    Do While wFile <> ""
      Set w2 = Workbooks.Open(wPath & "\" & wFile)
      Set sh2 = w2.Sheets(1)
      lr2 = sh2.Range("A" & Rows.Count).End(xlUp).Row
      sh.Cells(1, k).Value = wFile
      sh.Cells(2, k).Resize(lr2).Value = sh2.Range("A1:A" & lr2).Value
      k = k + 1
      w2.Close False
      wFile = Dir()
    Loop
  End With
  MsgBox "End"
End Sub

 

[Dpalkon]

Try this, change "Sheet1" by the name of your sheet

Sub import_text_files()
  Dim sh As Worksheet, wPath As String, wFile As String
  Dim w2 As Workbook, sh2 As Worksheet, k As Long, lr2 As Long

  Application.ScreenUpdating = False
  Application.DisplayAlerts = False

  Set sh = Sheets("Sheet1")
  sh.Cells.Clear
  k = 1

  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Selecciona una carpeta"
    .AllowMultiSelect = False
    .InitialFileName = ThisWorkbook.Path
    If .Show <> -1 Then Exit Sub
    wPath = .SelectedItems(1)
    wFile = Dir(wPath & "\" & "*.csv")
    Do While wFile <> ""
      Set w2 = Workbooks.Open(wPath & "\" & wFile)
      Set sh2 = w2.Sheets(1)
      lr2 = sh2.Range("A" & Rows.Count).End(xlUp).Row
      sh.Cells(1, k).Value = wFile
      sh.Cells(2, k).Resize(lr2).Value = sh2.Range("A1:A" & lr2).Value
      k = k + 1
      w2.Close False
      wFile = Dir()
    Loop
  End With
  MsgBox "End"
End Sub

Try this, change "Sheet1" by the name of your sheet

Sub import_text_files()
  Dim sh As Worksheet, wPath As String, wFile As String
  Dim w2 As Workbook, sh2 As Worksheet, k As Long, lr2 As Long

  Application.ScreenUpdating = False
  Application.DisplayAlerts = False

  Set sh = Sheets("Sheet1")
  sh.Cells.Clear
  k = 1

  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Selecciona una carpeta"
    .AllowMultiSelect = False
    .InitialFileName = ThisWorkbook.Path
    If .Show <> -1 Then Exit Sub
    wPath = .SelectedItems(1)
    wFile = Dir(wPath & "\" & "*.csv")
    Do While wFile <> ""
      Set w2 = Workbooks.Open(wPath & "\" & wFile)
      Set sh2 = w2.Sheets(1)
      lr2 = sh2.Range("A" & Rows.Count).End(xlUp).Row
      sh.Cells(1, k).Value = wFile
      sh.Cells(2, k).Resize(lr2).Value = sh2.Range("A1:A" & lr2).Value
      k = k + 1
      w2.Close False
      wFile = Dir()
    Loop
  End With
  MsgBox "End"
End Sub

This worked amazing. My text files actually ended in .DAT so I changed only that small portion of your code from .csv to .dat and BAM! This was really great. Is a lot of this same code the same for going back the other way? I mean Converting each Column back into many text files that would be named "row 1 string".DAT and the data on the rest of the rows would go into the text file right down the page one line at a time.

 

[DanteAmor]

This worked amazing. My text files actually ended in .DAT so I changed only that small portion of your code from .csv to .dat and BAM! This was really great. Is a lot of this same code the same for going back the other way? I mean Converting each Column back into many text files that would be named "row 1 string".DAT and the data on the rest of the rows would go into the text file right down the page one line at a time.

I'm glad to help you. Thanks for the feedback.

You need a new code, create a new thread and explain what you need.

 

[Dpalkon]

Dante,
Muchas gracias!!! I created a new thread with a picture for better understanding.