Link to home
Start Free TrialLog in
Avatar of EdLB
EdLB

asked on

Assigning OpenText work book to workbook variable

I am trying to open a text file and assign it to a workbook variable.

The following code works to open the file (I assign the file name in a previous line to ret)
    Dim wb As Workbook
    Dim ret As Variant
    Dim Control As Worksheet  

 Workbooks.OpenText Filename:=ret, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 2), 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)), TrailingMinusNumbers:=True
 
However, the following line does NOT work
    Set wb = Workbooks.OpenText Filename:=ret, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 2), 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)), TrailingMinusNumbers:=True

I get a compile error "expected end of statement"

Help please.
ASKER CERTIFIED SOLUTION
Avatar of crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of EdLB
EdLB

ASKER

That was simple. Thanks!
you're welcome ~ happy to help
alternately, you may be able to do it in one statement if you enclose the arguments with parentheses ...

rule of thumb: use parentheses around arguments if you are assigning the value.  Do not use them if you are not.