Error on Import

K

Ken Hudson

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?
 
J

Jim Thomlinson

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

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

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

Code to Test Condition and Save Record to Excel 0
ADO Returns Only Last Record 3
Syntax of VBA Code 1
run report on company intranet and import to excel 0
Error message "An error has occured in the script on this page" 1
Sript error when previewing page. 4
Record Click on Error Indicator 2
Wsh to import Csv and create a second sheet with sql 0

Top