-1

I would like to get the data of many txt files in one folder into one Excel sheet.

I recorded a macro and ended up opening the files in different workbooks.

Sub GetTxtData()

ChDir "C:\Users\Desktop\TXT"
Workbooks.OpenText Filename:="C:\Users\Desktop\TXT\Data0.txt", Origin:= _
    xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
    , ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:= _
    False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1) _
    , Array(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True

Workbooks.OpenText Filename:="C:\Users\Desktop\TXT\Data1.txt", _
    Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
    Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
    TrailingMinusNumbers:=True
End Sub
Community
  • 1
  • 1
Marc Chemali
  • 109
  • 5
  • 13
  • I would perform a full cycle, including copying the data into a single sheet, while recording the macro – K_B Apr 29 '13 at 15:05
  • 1
    why not try importing the data using Get External Data? There is a tutorial for this: [on the MSDN](http://office.microsoft.com/en-gb/training/import-data-import-text-files-into-excel-2003-RZ001180132.aspx?section=1) – Our Man in Bananas Apr 29 '13 at 15:12
  • 1
    also, here is a good answer from **Siddharth Rout** on this type pf thing: [http://stackoverflow.com/questions/11267459/vba-importing-text-file-into-excel-sheet](http://stackoverflow.com/questions/11267459/vba-importing-text-file-into-excel-sheet) – Our Man in Bananas Apr 29 '13 at 15:14
  • Thanks Philip but I have more than 30 txt files with data in them, using the get external Data won't be a feasible idea, I will try Siddharth's method. – Marc Chemali Apr 29 '13 at 15:24
  • Marc, it depends on the format of the files, if they're suitable to be in ADO recordsets, you could read them in inside a loop, and for each one use **CopyFromRecordset** to put the data into your worksheet – Our Man in Bananas Apr 29 '13 at 15:29

1 Answers1

1

Try importing the data instead of opening the csv files.

In Excel 2007 this is done by Data, From Text.

You'll get something like: ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Users\Desktop\TXT\Data0.txt", Destination:=Range("$A$1")) ...

You're going to have to change the destination in code to keep the files from overwriting each other in the same spot. I'd use a full address including sheet name if I wanted them all on different sheets.

Denise Skidmore
  • 2,286
  • 22
  • 51
  • works great with this macro:http://stackoverflow.com/questions/11267459/vba-importing-text-file-into-excel-‌​sheet – Marc Chemali Apr 29 '13 at 15:49