CODEWITHSUNDEEP
excelvbatext-filesdelimiter
REdim.Learning
REdim.Learning

Reputation: 659

Open Text File and Split() with exclamation mark as delimiter

Should be very simple I think, but can't seem to get it to work. Tried two methods for opening a text file (.txt) into excel and splitting by the Other option: "!" 

Set workbookbk = Workbooks.OpenText(fullfilepath, xlMSDOS, _
xlDelimited, xlDoubleQuote, False, False, False, False, False, True, "!")

this syntax is fine but then it says "Compile Error: Expected Function or Variable" what am I missing?!

I also used

Set workbk = Workbooks.Open filename:= fullfilepath, Delimiter:= "!"

This opens the file ok, but doesn't split the file at all

Help much appreciated

Upvotes: 1

Views: 819

Answers (1)

SierraOscar
SierraOscar

Reputation: 17637

If you're going to skip arguments from a method, then you need explicitly specify which arguments you are supplying:

Workbook.OpenText syntax (from MSDN)

expression.OpenText(Filename, Origin, StartRow, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, TextVisualLayout, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers, Local)

expression A variable that represents a Workbooks object.

In your code, you have missed out some arguments, but the compiler will assume you have supplied the arguments in the expected order for that method unless you specify which arguments you are passing. For example: 

Workbooks.OpenText Filename:=fuullfilepath, Origin:=xlMSDOS, DataType:=xlDelimited, Other:=True, OtherChar:="!"

Also, might not be the answer you were hoping for, but I wrote a UDF to accomplish a similar thing without using the .OpenText() method a while ago:

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

Here's an example of how you would use it:

Sub Foo()

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

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

Related Questions