Importing Multiple Text Files, each text file in different sheet within a Excel Workbook

[drp92]

Greetings Friends!!
As am new to the Excel "Macros", still in the process of learning, hence if on further discussions if I ask tiny-simple things, I sincerely ask you, to bare with me!!

Without further ado, let's get started!

Basically what I have to do is Import text files, all which has same format.
It has following:
UniqueNo,Date(in yyyymmdd format),(next 5 different numbers separated by comma) for your ref it quite looks like this:

123456,20140901,123.00,1000.90,112.25,123.15,001259

Now what I want is have each of this "text" files (all which has date as its name, so Unique name) imported in excel, but "each" text file should be in separate sheet.

i.e. for e.g. if I wrote a macro and tested it, and the path from where the text files are picked up by macro are 15 in number (for the sake of example I have taken 15, in reality there could be 100 or 1000), then after the successful running of code I could see all those 15 text files in 15 sheet, within a workbook.

So, it’s basically like creating a "loop" or "array" of strings or something ( I don’t really know, "NOT ONE BIT"---Quoting Heath Ledger's dialog there)

And finally here is my Code where I have recorded a Macro to import A text file, which looks like as below:

---

​

Sub data_importTest1()
'
' data_importTest1 Macro
' to import data from text file to excel using data import query
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Export\2014-09-01-RPD-PP.txt", Destination:=Range("$A$1"))
.Name = "2014-09-01-RPD-PP"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 5, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

End Sub

---

​

Actually I don’t really know is this possible or not, but it would really help me a lot if the text file that is imported into excel sheet, that sheet picks up the name of the file I have imported !!

So in Nut shell
If the given text file name is "YYYY-MM-DD-XYZ-PQ", while importing this (in my format-->as my code suggested), excel sheet picks this as its name before moving to next file for importing onto different excel sheet and doing all this over and over again till the path where I have the text files all are over of importing.

Please I kindly request all of you to help me with this! (Take your time I am in no hurry!!)

Thanks.

Regards,
DrpRD

 

[drp92]

Anyways.......

Mean while i was surfing on net reg this found this amazing Macro Code, and whosshh...my problem solved.

It does exactly what i wanted and in perfect sense.

---

Sub CombineTextFiles()
Dim FilesToOpen
Dim x As Integer
Dim wkbAll As Workbook
Dim wkbTemp As Workbook
Dim sDelimiter As String

On Error GoTo ErrHandler
Application.ScreenUpdating = False

sDelimiter = ","

FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Text Files (*.txt), *.txt", _
MultiSelect:=True, Title:="Text Files to Open")

If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "No Files were selected"
GoTo ExitHandler
End If

x = 1
Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
wkbTemp.Sheets(1).Copy
Set wkbAll = ActiveWorkbook
wkbTemp.Close (False)
wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=True, OtherChar:="|"
x = x + 1

While x <= UBound(FilesToOpen)
Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
With wkbAll
wkbTemp.Sheets(1).Move After:=.Sheets(.Sheets.Count)
.Worksheets(x).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=True, OtherChar:=sDelimiter
End With
x = x + 1
Wend

ExitHandler:
Application.ScreenUpdating = True
Set wkbAll = Nothing
Set wkbTemp = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub

---

PS: If instead you want to select ".csv", files just find replace ".txt" with csv and voila..its done !!
Also if you have a file with some another delimiter, change ' sDelimiter = "," ' with thing you want to separate !