CODEWITHSUNDEEP
excelexcel-2007vba
L42
L42

Reputation: 19727

Passing workbook object to a variable

I have this code and it gives me a Compile error: Expected Function or Variable.
How do i code this properly.

Sub test()

Dim wb As Workbook
Dim FiletoOpen

FiletoOpen = Application.GetOpenFilename(filefilter:="Text Files (*.csv), *.csv", MultiSelect:=False)

Set csv_wb = Workbooks.OpenText(Filename:=FiletoOpen, startRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 2), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
        Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1)))

End Sub

If I use Set csv_wb = Workbooks.Open (FiletoOpen) it works fine.
But there are specific fields in the file that I need to be in Text format.
So I cannot use this. But when I use the above, it gives me the error.
Any help would be much appreciated.

Upvotes: 1

Views: 1167

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

You have declared wb but using csv_WB Try This.

UNTESTED

Sub test()
    Dim csv_WB As Workbook
    Dim FiletoOpen

    FiletoOpen = Application.GetOpenFilename(filefilter:="Text Files (*.csv), *.csv", MultiSelect:=False)

    If FiletoOpen = False Then Exit Sub

    Workbooks.OpenText Filename:=FiletoOpen, startRow:=1, DataType:=xlDelimited, TextQualifier:= _
                xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
                , Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
                Array(2, 1), Array(3, 2), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
                Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1))

    Set csv_WB = ActiveWorkbook

End Sub

Upvotes: 2

Related Questions