Dear all,
I have a macro that reads from a text file, but it skips trailing '1'!
Have tried a lot with the original macro, and boiled it down to the very basic here. Tried a lot with the format specifiers, but no luck.
When playing around with the context of test.txt, it seems like VBA is treating '1' as a seperator
Any clues?
Thanks Thomas B.
text file : test.txt
41
42
macro
Sub ReadPrjFileNew()
FileName = "TEST.txt"
CompleteFileName = "TEXT;" & FileName
' Start with clearing the whole lot
Cells.Select
Selection.ClearContents
Selection.FormatConditions.Delete
Selection.ColumnWidth = 10
Selection.Font.FontStyle = "normal"
Selection.Interior.ColorIndex = xlNone
With ActiveSheet.QueryTables.Add(Connection:= _
CompleteFileName, Destination:=Range("A1") _
)
.Name = FileName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
'.TextFileTextQualifier = xlTextQualifierNone
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileOtherDelimiter = xlTextQualifierDoubleQuote
.TextFileColumnDataTypes = Array(xlTextFormat)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
I have a macro that reads from a text file, but it skips trailing '1'!
Have tried a lot with the original macro, and boiled it down to the very basic here. Tried a lot with the format specifiers, but no luck.
When playing around with the context of test.txt, it seems like VBA is treating '1' as a seperator
Any clues?
Thanks Thomas B.
text file : test.txt
41
42
macro
Sub ReadPrjFileNew()
FileName = "TEST.txt"
CompleteFileName = "TEXT;" & FileName
' Start with clearing the whole lot
Cells.Select
Selection.ClearContents
Selection.FormatConditions.Delete
Selection.ColumnWidth = 10
Selection.Font.FontStyle = "normal"
Selection.Interior.ColorIndex = xlNone
With ActiveSheet.QueryTables.Add(Connection:= _
CompleteFileName, Destination:=Range("A1") _
)
.Name = FileName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
'.TextFileTextQualifier = xlTextQualifierNone
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileOtherDelimiter = xlTextQualifierDoubleQuote
.TextFileColumnDataTypes = Array(xlTextFormat)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub