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.
Code
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
Wend
Close #FF
MM_OpenTextFile = WorksheetFunction.Transpose(WorksheetFunction.Transpose(arrayList.ToArray()))
arrayList.Clear
Set arrayList = Nothing
End FunctionExample Use:
Code
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