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:=Fals e, 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:=Fals e, 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.
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:=Fals
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:=Fals
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
rule of thumb: use parentheses around arguments if you are assigning the value. Do not use them if you are not.
ASKER