Err 1004 when move Workbooks.OpenText to called Sub

[Sue D]

I am trying to tidy up some VBA code by putting it in a Called Sub
When I use Workbooks.Opentext in the original code as for example

sFilename = sPath & "F59080"
sWbookname = sPath & "x80G01.xls"
Workbooks.OpenText FileName:=sFilename, _
Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 1), Array(2, 2), Array(27, 2), Array(137, 2), Array(150, 2))

It works.

But if I put it in a called Sub as

Sub texToWB(Wb As String, Textfile As String, Arr As String, GrpId As String)

If Dir(Textfile) <> "" Then
Workbooks.OpenText FileName:=Textfile, _
Origin:=xlWindows, _
DataType:=xlFixedWidth, FieldInfo:=Array(Arr)
Else
MsgBox Textfile & " doesn't exist"
End If

It fails with Error 1004.

I am running Office 2003 and Windows XP

[Leith Ross]

Hello Sue,

The problem is with passing the array. In your Sub you're passing it as a String. The Array must be a Variant. I have highlighted the problem in red.

Sub texToWB(Wb As String, Textfile As String, Arr As String, GrpId As String)

Change it to read...
Sub texToWB(Wb As String, Textfile As String, Arr As Variant, GrpId As String)

[Sue D]

Thanks for taking the trouble to answer. Once I understood that I needed to
pass the Field Info, defining it as an Array held in variant datatype it all
fell into place and now works fine


"Leith Ross" wrote:

>
> Hello Sue,
>
> The problem is with passing the array. In your Sub you're passing it as
> a String. The Array must be a Variant. I have highlighted the problem in
> red.
>
> Sub texToWB(Wb As String, Textfile As String, Arr As String, GrpId As
> String)
>
> Change it to read...
> Sub texToWB(Wb As String, Textfile As String, Arr As Variant, GrpId As
> String)
>
>
> --
> Leith Ross
>
>
> ------------------------------------------------------------------------
> Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
> View this thread: http://www.excelforum.com/showthread...hreadid=478205
>
>