Dears,
I'm trying to build a macro to import in Excel a TXT file as text.
In the TXT file I normally have numbers with more that 16 digits so if I copy and paste them in Excel I need to make sure that the cell format is "text".
If not, those big numbers are loosing the last digits.
I tried a couple of macros, but when the TXT is opened automatically the numbers get converted. When they are pasted into the final sheet the result is already corrupted.
Below a TXT example:
9990600331568537
TEST1
54008520005491689
TEST2
In Excel I get:
9.9906E+15
TEST1
5.40085E+16
TEST2
Macros tested are:
Do you have any advice to solve this problem?
Thanks a lot!
I'm trying to build a macro to import in Excel a TXT file as text.
In the TXT file I normally have numbers with more that 16 digits so if I copy and paste them in Excel I need to make sure that the cell format is "text".
If not, those big numbers are loosing the last digits.
I tried a couple of macros, but when the TXT is opened automatically the numbers get converted. When they are pasted into the final sheet the result is already corrupted.
Below a TXT example:
9990600331568537
TEST1
54008520005491689
TEST2
In Excel I get:
9.9906E+15
TEST1
5.40085E+16
TEST2
Macros tested are:
VBA Code:
Sub Macro1()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\MyFile.txt", Destination:=Range("$H$5") _
)
.Name = "Sample"
.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 = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
VBA Code:
Sub Macro2()
Dim wbI As Workbook, wbO As Workbook
Dim wsI As Worksheet
Set wbI = ThisWorkbook
Set wsI = wbI.Sheets("Test")
Set wbO = Workbooks.Open("C:\MyFile.txt")
wbO.Sheets(1).Cells.Copy wsI.Cells
wbO.Close SaveChanges:=False
End Sub
Do you have any advice to solve this problem?
Thanks a lot!