Error on Import

G

Guest

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=Anal 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?
 
G

Guest

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...
 
G

Guest

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 said:
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 said:
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=Anal 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?
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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=Anal 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?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ADO Returns Only Last Record 3
Sript error when previewing page. 4
Error on Webpage Object Expected 1
Excel Excel Macro works on one computer but errors out on other computers 2
Runtime Error 6
CSV Import Error on text field with +- (Excel 2003) 2
Seeking Import/Link/Query workaround #Num! and #Error responses 6

Top