Update data from text file in worksheet starting at particualr cell.

[Arun]

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

[Kenneth Hobs]

The text file was not in the zip file. You can add the txt file to a reply. You could post your code in the xls to make it easier to help. Please click the VBA icon and paste your code between the tags when posting to the forum.

So, I guess what you want is to format the imported data as shown in your xls?

[rinser]

You have to have 5 postings to upload files. :)