title: VBA code to import multiple text files into excel?
body: <p>It would seem this question has been asked plenty of times across the internet in some form or another, but I haven't been able to come up with code that meets my needs. I hate to say I'm not at all savvy with VBA; I have 10-15 text files in a folder that may or may not contain data. If they do, the first line will be a header with all lines beneath containing 4 pieces of information (number, description, quantity, and unit). Everything is comma delineated. I'm needing to have this data placed in 4 columns on a specific sheet in excel, preferably without any of the headers copied over. Ideally, this would be able to be refreshed (or have the current data deleted when they new data is imported) so changes in the txt files would be reflected on the sheet. I'm needing to pass group of files around for others to use so the path to the text files should be relative.</p>
  
  <p>This code was the best I was able to find in my search:</p>
  
  <pre><code>Sub Read_Text_Files()
  Dim sPath As String, sLine As String
  Dim oPath As Object, oFile As Object, oFSO As Object
  Dim r As Long
  '
  'Files location
  sPath = ThisWorkbook.path &amp; "\Quantities\"
  '
  r = 1
  Set oFSO = CreateObject( _
  "Scripting.FileSystemObject")
  Set oPath = oFSO.GetFolder(sPath)
  Application.ScreenUpdating = False
  For Each oFile In oPath.Files
  If LCase(Right(oFile.Name, 4)) = ".txt" Then
  '
  Open oFile For Input As #1 ' Open file for input.
  Do While Not EOF(1) ' Loop until end of file.
  Input #1, sLine ' Read data
  If Left(sLine, 1) = "=" Then sLine = "'" &amp; sLine 'If line starts with an equal sign, add a single quote at the start
  sLine = Replace(sLine, Chr(2), "") 'Strip funky characters
  sLine = Replace(sLine, Chr(3), "")
  sLine = Replace(sLine, Chr(10), "")
  Range("A" &amp; r).Formula = sLine ' Write data line
  r = r + 1
  Loop
  Close #1 ' Close file.
  '
  End If
  Next oFile
  '
  'Text to Columns
  Range("A1", Range("A" &amp; Cells.Rows.Count).End(xlUp)).Select
  Selection.TextToColumns DataType:=xlDelimited, _
  TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
  Semicolon:=False, Comma:=True, Space:=False, Other:=False
  Application.ScreenUpdating = True
  End Sub
  </code></pre>
  
  <p>I have attempted tinkering with it a dozen times at this point with little success. My best trial had it run correctly, though every individual value was in column A. At this point, I've messed with it to where it isn't running. Any help would be greatly appreciated!</p>