-1

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.

This code was the best I was able to find in my search:

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 & "\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 = "'" & 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" & 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" & 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

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!

WjdIrene
  • 3
  • 4
  • You say that everything works, except the info gets put into column A. What if, after everything is in column A, you use "Text to Columns" under the Data tab, and use a comma as delimiter. Does that help? Or is everything being put in A, AFTER being split up? Also, I think your final "Text to Columns" range may be slightly off. Instead, replace that range with `Range(Cells(1,1),Cells(Cells.Rows.Count,1)).TexttoColumns ...` That removes the "select" which isn't recommended to use in VBA if you can avoid it. – BruceWayne Jul 16 '15 at 19:33
  • I just tested it ... it's putting each value into a new row (rather than each line)! – 3-14159265358979323846264 Jul 16 '15 at 19:37
  • Yes; I believe that was the intent of the person who wrote it, but I couldn't figure out how to alter the code in order to move to a new row once it reached the end of a line in the text file. – WjdIrene Jul 16 '15 at 19:44
  • Take your [Pick](http://stackoverflow.com/questions/11267459/vba-importing-text-file-into-excel-sheet/11267603#11267603) – Siddharth Rout Jul 16 '15 at 21:07

1 Answers1

0

Use Line Input #1, sLine. This takes the whole text line in, instead of one word at a time.

Try to avoid selecting things you want to operate on. Instead, operate on them directly. Re-write your selection based code to the following:

Range("A1", Range("A" & UsedRange.Rows.Count).End(xlUp)).TextToColumns DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=False

Also, you don't need quotes in between lines.

usncahill
  • 469
  • 6
  • 16
  • That worked fantastically! Thank you! Not that I can't work around it, but is there a way to remove the symbols (') that are appearing at the beginning of the first line of each file? That and/or a way to not include the headers of each txt file? – WjdIrene Jul 16 '15 at 20:43
  • What is producing these text files that these characters are appearing? If you know what the first line of valid text might look like or start with, you can do `do: line input #1,sLine: loop while instr(sLine,char(bad char code here)) ` or something like that – usncahill Jul 17 '15 at 00:59
  • Please pick my answer if it answered your question adequately. I will continue to provide feedback if you would like to fix these other issues as well, or you can ask another question :) – usncahill Jul 17 '15 at 01:01