Error on Import

I have a macro that I use to import a large text file into multiple
worksheets in Excel.
To get these imported I have the following code:

Set oConn = New ADODB.Connection
Set rsInput = New ADODB.Recordset

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPath & ";" & _

"Extended Properties=""text;HDR=NO;FMT=FixedLength"""

rsInput.Open "SELECT * FROM " & FileOpenNameF827, oConn, adOpenStatic,
adLockOptimistic, adCmdText

If rsInput.RecordCount > 0 Then
Counter = 1
ActiveSheet.Name = "F827"

rsInput.MoveFirst
While Not rsInput.EOF
ActiveSheet.Cells(1, 1).CopyFromRecordset rsInput, 65000
If Not rsInput.EOF Then
Counter = Counter + 1
Sheets.Add
ActiveSheet.Name = "F827(" & Counter & ")"
End If
Wend
End If
rsInput.Close

I also have the following in a schema.ini file:

[F827.txt]
Format=FixedLength
ColNameHeader=False

Col1=GLAcctNo Text Width 5
Col2=GLName Text Width 9
Col3=Lim Text Width 3
Col4=**** Text Width 5
Col5=AO Text Width 5
Col6=TC Text Width 3
Col7=TT Text Width 3
Col8=DocID Text Width 12
Col9=DocLin Text Width 4
Col10=BatchNo Text Width 7
Col11=TransDate Text Width 9
Col12=AccDate Text Width 9
Col13=RefTC Text Width 3
Col14=RefNo Text Width 12
Col15=RefLn Text Width 4
Col16=DrAmt Double Width 20
Col17=CrAmt Double Width 20

The import works on my machine. I have sent the macro and ini file to about
20 other users. For a few of them, instead of importing and parsing the text
file into 17 cloumns, the data is being parsed into only two columns; column
A includes all the data that is supposed to parsed into columns 1-16 and
column B has the data that is supposed to be column 17.

The users have the same Excel version as me.

Any clues as to what might be happening?

--
Ken Hudson

Hard to say when it only happens on some machines and not others, but i would
suspect (WAG) that it is not finding your schema file on the affected
machines...
--
HTH...

Jim Thomlinson


"Ken Hudson" wrote:

> I have a macro that I use to import a large text file into multiple
> worksheets in Excel.
> To get these imported I have the following code:
>
> Set oConn = New ADODB.Connection
> Set rsInput = New ADODB.Recordset
>
> oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & strPath & ";" & _
>
> "Extended Properties=""text;HDR=NO;FMT=FixedLength"""
>
> rsInput.Open "SELECT * FROM " & FileOpenNameF827, oConn, adOpenStatic,
> adLockOptimistic, adCmdText
>
> If rsInput.RecordCount > 0 Then
> Counter = 1
> ActiveSheet.Name = "F827"
>
> rsInput.MoveFirst
> While Not rsInput.EOF
> ActiveSheet.Cells(1, 1).CopyFromRecordset rsInput, 65000
> If Not rsInput.EOF Then
> Counter = Counter + 1
> Sheets.Add
> ActiveSheet.Name = "F827(" & Counter & ")"
> End If
> Wend
> End If
> rsInput.Close
>
> I also have the following in a schema.ini file:
>
> [F827.txt]
> Format=FixedLength
> ColNameHeader=False
>
> Col1=GLAcctNo Text Width 5
> Col2=GLName Text Width 9
> Col3=Lim Text Width 3
> Col4=**** Text Width 5
> Col5=AO Text Width 5
> Col6=TC Text Width 3
> Col7=TT Text Width 3
> Col8=DocID Text Width 12
> Col9=DocLin Text Width 4
> Col10=BatchNo Text Width 7
> Col11=TransDate Text Width 9
> Col12=AccDate Text Width 9
> Col13=RefTC Text Width 3
> Col14=RefNo Text Width 12
> Col15=RefLn Text Width 4
> Col16=DrAmt Double Width 20
> Col17=CrAmt Double Width 20
>
> The import works on my machine. I have sent the macro and ini file to about
> 20 other users. For a few of them, instead of importing and parsing the text
> file into 17 cloumns, the data is being parsed into only two columns; column
> A includes all the data that is supposed to parsed into columns 1-16 and
> column B has the data that is supposed to be column 17.
>
> The users have the same Excel version as me.
>
> Any clues as to what might be happening?
>
> --
> Ken Hudson

Jim,
Thanks for the reply.
In the past my users have always received an error message to the effect
that the "specs were not found" when they don't have the ini file in the same
folder as the text file. In this case the macro runs, but the output is trash
because of the bad parsing.
I'll keep looking and let you now when/if I find the glitch.

--
Ken Hudson


"Jim Thomlinson" wrote:

> Hard to say when it only happens on some machines and not others, but i would
> suspect (WAG) that it is not finding your schema file on the affected
> machines...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Ken Hudson" wrote:
>
> > I have a macro that I use to import a large text file into multiple
> > worksheets in Excel.
> > To get these imported I have the following code:
> >
> > Set oConn = New ADODB.Connection
> > Set rsInput = New ADODB.Recordset
> >
> > oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > "Data Source=" & strPath & ";" & _
> >
> > "Extended Properties=""text;HDR=NO;FMT=FixedLength"""
> >
> > rsInput.Open "SELECT * FROM " & FileOpenNameF827, oConn, adOpenStatic,
> > adLockOptimistic, adCmdText
> >
> > If rsInput.RecordCount > 0 Then
> > Counter = 1
> > ActiveSheet.Name = "F827"
> >
> > rsInput.MoveFirst
> > While Not rsInput.EOF
> > ActiveSheet.Cells(1, 1).CopyFromRecordset rsInput, 65000
> > If Not rsInput.EOF Then
> > Counter = Counter + 1
> > Sheets.Add
> > ActiveSheet.Name = "F827(" & Counter & ")"
> > End If
> > Wend
> > End If
> > rsInput.Close
> >
> > I also have the following in a schema.ini file:
> >
> > [F827.txt]
> > Format=FixedLength
> > ColNameHeader=False
> >
> > Col1=GLAcctNo Text Width 5
> > Col2=GLName Text Width 9
> > Col3=Lim Text Width 3
> > Col4=**** Text Width 5
> > Col5=AO Text Width 5
> > Col6=TC Text Width 3
> > Col7=TT Text Width 3
> > Col8=DocID Text Width 12
> > Col9=DocLin Text Width 4
> > Col10=BatchNo Text Width 7
> > Col11=TransDate Text Width 9
> > Col12=AccDate Text Width 9
> > Col13=RefTC Text Width 3
> > Col14=RefNo Text Width 12
> > Col15=RefLn Text Width 4
> > Col16=DrAmt Double Width 20
> > Col17=CrAmt Double Width 20
> >
> > The import works on my machine. I have sent the macro and ini file to about
> > 20 other users. For a few of them, instead of importing and parsing the text
> > file into 17 cloumns, the data is being parsed into only two columns; column
> > A includes all the data that is supposed to parsed into columns 1-16 and
> > column B has the data that is supposed to be column 17.
> >
> > The users have the same Excel version as me.
> >
> > Any clues as to what might be happening?
> >
> > --
> > Ken Hudson

Well, I think we have the solution, but I don't know what the probelm was. We
download the text file from a corporate web site. After downloading it, the
user with the problem opened the text file in notepad and did a Save As
without changing anything. The macro worked. Go figure.
--
Ken Hudson


"Jim Thomlinson" wrote:

> Hard to say when it only happens on some machines and not others, but i would
> suspect (WAG) that it is not finding your schema file on the affected
> machines...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Ken Hudson" wrote:
>
> > I have a macro that I use to import a large text file into multiple
> > worksheets in Excel.
> > To get these imported I have the following code:
> >
> > Set oConn = New ADODB.Connection
> > Set rsInput = New ADODB.Recordset
> >
> > oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > "Data Source=" & strPath & ";" & _
> >
> > "Extended Properties=""text;HDR=NO;FMT=FixedLength"""
> >
> > rsInput.Open "SELECT * FROM " & FileOpenNameF827, oConn, adOpenStatic,
> > adLockOptimistic, adCmdText
> >
> > If rsInput.RecordCount > 0 Then
> > Counter = 1
> > ActiveSheet.Name = "F827"
> >
> > rsInput.MoveFirst
> > While Not rsInput.EOF
> > ActiveSheet.Cells(1, 1).CopyFromRecordset rsInput, 65000
> > If Not rsInput.EOF Then
> > Counter = Counter + 1
> > Sheets.Add
> > ActiveSheet.Name = "F827(" & Counter & ")"
> > End If
> > Wend
> > End If
> > rsInput.Close
> >
> > I also have the following in a schema.ini file:
> >
> > [F827.txt]
> > Format=FixedLength
> > ColNameHeader=False
> >
> > Col1=GLAcctNo Text Width 5
> > Col2=GLName Text Width 9
> > Col3=Lim Text Width 3
> > Col4=**** Text Width 5
> > Col5=AO Text Width 5
> > Col6=TC Text Width 3
> > Col7=TT Text Width 3
> > Col8=DocID Text Width 12
> > Col9=DocLin Text Width 4
> > Col10=BatchNo Text Width 7
> > Col11=TransDate Text Width 9
> > Col12=AccDate Text Width 9
> > Col13=RefTC Text Width 3
> > Col14=RefNo Text Width 12
> > Col15=RefLn Text Width 4
> > Col16=DrAmt Double Width 20
> > Col17=CrAmt Double Width 20
> >
> > The import works on my machine. I have sent the macro and ini file to about
> > 20 other users. For a few of them, instead of importing and parsing the text
> > file into 17 cloumns, the data is being parsed into only two columns; column
> > A includes all the data that is supposed to parsed into columns 1-16 and
> > column B has the data that is supposed to be column 17.
> >
> > The users have the same Excel version as me.
> >
> > Any clues as to what might be happening?
> >
> > --
> > Ken Hudson