CODEWITHSUNDEEP
vbaexcelcsv
Lasse Ivanouw
Lasse Ivanouw

Reputation: 3

My VBA .OpenText code does not format the columns right

I would like to open a .csv file via VBA, with some columns formatted as text. My code currently looks like this.

Workbooks.OpenText Filename:=vPath, Origin:=xlMSDOS, StartRow:=1 _
, DataType:=xlDelimited, TextQualifier:=xlTextQualifierDoubleQuote, ConsecutiveDelimiter:=True, semicolon:=True, local:=True, FieldInfo:=Array(Array(1, 2), Array(3, 2), Array(3, 2), Array(4, 2))

I use the local:=True because my key languish is danish, I don't know if it also effects the FieldInfo, or is there another problem with my code? But the fields are not formatted as text but as General.

Upvotes: 0

Views: 747

Answers (1)

SierraOscar
SierraOscar

Reputation: 17637

Another approach you could use - read the file into an array line-by-line and split by delimiter. Then use that array to predict the area that your data will sit in and format that area as text before adding the values.

Here's a UDF that would create the 2D array from your text file:

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 Function

And here's an example of how you would use it - change the reference to "A1" to a start cell of your choice.

Sub MM_Example_Use()

Dim ar As Variant '// Must be a Variant to work

'// Change to a file and delimiter of your choosing...
ar = MM_OpenTextFile("C:\Users\MacroMan\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

Upvotes: 1

Related Questions