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

Hello Sue,

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

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

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

--
Leith Ros

-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=47820

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