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
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