Just wrote this for someone else that had a delimited text file but Excel was parsing the values as numbers and dropping the leading "0"s
This will open the text file and read it in using the supplied delimiter, and return a 2D array. You can then use that array to target the range that the data is going to be placed and set the formatting accordingly.
Might save someone some time instead of having to go through and wrap everything in quotes.
- Function MM_OpenTextFile(vPath As String, delim As String) As Variant
- Dim FF As Integer
- Dim lineArray As Variant
- Dim temp As String
- Dim arrayList As Object
- Set arrayList = CreateObject("System.Collections.ArrayList")
- FF = FreeFile
- Open vPath For Input As #FF
- While Not EOF(FF)
- Line Input #FF, temp
- lineArray = Split(temp, delim)
- arrayList.Add lineArray
- Erase lineArray
- Close #FF
- MM_OpenTextFile = WorksheetFunction.Transpose(WorksheetFunction.Transpose(arrayList.ToArray()))
- Set arrayList = Nothing
- End Function
- Sub Example()
- Dim ar As Variant '// Must be a Variant to work
- '// Change to a file and delimiter of your choosing...
- ar = MM_OpenTextFile("C:\Users\S O\SomeFile.txt", ";")
- With Range("A1").Resize(UBound(ar, 1), UBound(ar, 2))
- .NumberFormat = "@" '// Change format to "text"
- .value = ar '// insert array values
- End With
- End Sub