| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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=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? -- Ken Hudson |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error on Import
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=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? -- Ken Hudson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error on Import
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=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? -- Ken Hudson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Error on Import
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=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? -- Ken Hudson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| XML Import Parsing error | Excel Discussion (Misc queries) | |||
| Connection/Import Error msgs | Excel Programming | |||
| Excel 2000 Import Error | Excel Programming | |||
| Import sheet error | Excel Programming | |||
| HTML/XML Import Error -ExcelName | Excel Programming | |||
Linear Mode
