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

drp92

New Member
Joined
May 23, 2013
Messages
17
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

drp92

New Member
Joined
May 23, 2013
Messages
17
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 !
 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,798
Members
416,983
Latest member
LessThanAverageUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top