Arun
03-12-2009, 06:24 AM
Hello Gurus -
I need to import the data from a text file (which keeps on changing every day) and i need to put the values starting from C4. Now using the folllowing code i am able to import the values but next time i import the same file then it shift the exiting column to next locaiton.
Sub Macro1()
Dim MyFile As String
MyFile = "D:\Test\Sample.txt"
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & MyFile, _
Destination:=Range("$C$5"))
.Name = "MySample"
.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 = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
Attached are the text file and xls file , and i need to import the values in xls file from text file. TEXT FILE VALUES KEEP ON CHAGING every day and not the xls file.
Note:- It may be possible that rows in text file will also get changed as 1 time it may return 4 rows and next time it may return 7 rows but the column values will remain the same in text file.
Kindly help me in this.
My Data of text file is like as following -
Analysis 0 1 15 1 17
Closed 77 224 895 92 1303
Development 0 36 10 0 10
Duplicate 1 4 19 0 24
Failed 0 0 2 0 2
OnHold 5 0 4 0 4
Opened 9 30 108 3 150
QA 0 0 1 0 1
ReadyForQA 5 5 1 0 1
Withdrawn 14 104 201 14 335
Total 101 363 1256 110 1847
I need to import the data from a text file (which keeps on changing every day) and i need to put the values starting from C4. Now using the folllowing code i am able to import the values but next time i import the same file then it shift the exiting column to next locaiton.
Sub Macro1()
Dim MyFile As String
MyFile = "D:\Test\Sample.txt"
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & MyFile, _
Destination:=Range("$C$5"))
.Name = "MySample"
.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 = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
Attached are the text file and xls file , and i need to import the values in xls file from text file. TEXT FILE VALUES KEEP ON CHAGING every day and not the xls file.
Note:- It may be possible that rows in text file will also get changed as 1 time it may return 4 rows and next time it may return 7 rows but the column values will remain the same in text file.
Kindly help me in this.
My Data of text file is like as following -
Analysis 0 1 15 1 17
Closed 77 224 895 92 1303
Development 0 36 10 0 10
Duplicate 1 4 19 0 24
Failed 0 0 2 0 2
OnHold 5 0 4 0 4
Opened 9 30 108 3 150
QA 0 0 1 0 1
ReadyForQA 5 5 1 0 1
Withdrawn 14 104 201 14 335
Total 101 363 1256 110 1847