Get txt files from a folder to master file in different sheets

[aravindhan_3]

The cross post is here : http://www.excelforum.com/excel-programming/685663-get-txt-files-from-a-folder-to-master-file-in-different-sheets.html#post2101480
Hi,

I thank you all very much for helping so many days. I need your help again on this.
I have 25 text files say A,B,C,D,E etc in a folder and I have one master excel file with 5 sheets called Master file,Data,Groups,Total, and Price.
Wha I do now manually is I insert one sheet called A in my master file and open text file A when I open that file it open as text so I convert that to text to columns

I want to automate this process.. that is get all the 25 files from a folder to the masterfile in each sheet with converted text into to colums.

However I got a similar code in Ronderbruins site
but that will get file in a different workbook. i want the data to get in the same workbook
http://www.rondebruin.nl/txtcsv.htm (http://www.rondebruin.nl/txtcsv.htm)

Regards
Arvind

[mdmackillop]

that is get all the 25 files from a folder to the masterfile in each sheet
I don't follow this

[GTO]

Greetings Arvind,

I am either 'right-on' or way off on this:

You want to read from 25 text files, and write these to 5 sheets in the destination wb.

If this is (maybe?) the case, I would suggest attaching an example wb and a couple of example text files.

Not to be blunt, but we'd need to have a clearer "view" of how ea textfile if getting split up...

Hope to help,

Mark

[GTO]

Sorry, just in case you don;t know, you can zip the files and attach to one post. Also - a clear desript of where stuff is to go would most likely help.

mark

[aravindhan_3]

Hi,

I apologies for not being clear..

I have 25 text files in a folder and one masterfile with 28 sheets. 25 sheets are named same as my text files. and I have other 3 sheets with some formulas say "SheetMaster","SheetData","SheetPrice".

I need all those 25 text files to be copied in 25 sheets based on the sheet names. say if text file name is Apple, there will be sheets a in my master file as Apple, so the data to be imported to this sheet from the text file. same for all the 25 files.

The codes in the below link works almost fine but instead of importing data to my master file it creates a new workbook and inserts sheets there.
http://www.rondebruin.nl/txtcsv.htm (http://www.rondebruin.nl/txtcsv.htm)

I have attached the samples for your reference

[mdmackillop]

Try this.
BTW, it is hard to check correct execution where there is nothing to distinguish imported files.

Sub Macro1()
Dim i As Long
Dim MyConn As String
Dim Fil As String
Application.ScreenUpdating = False
For i = 4 To Sheets.Count
Fil = ActiveWorkbook.Path & "\" & Sheets(i).Name & ".txt"
If Len(Dir(Fil)) > 0 Then
MyConn = "TEXT;" & Fil
With Sheets(i).QueryTables.Add(Connection:=MyConn, _
Destination:=Sheets(i).Range("$A$1"))
.Name = "Apple_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End If
Next
Application.ScreenUpdating = False
End Sub