Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - CopyFromRecordset (ADO) Problems with 107000 records
Hi,
I have an ADO recordset with 107000+ records (loaded from text file using schema.ini file and ADBC Text Driver). I need to get this data into Excel sheets, first, copy 65535 (1st row=column heads), then keep copying 65535 onto new worksheets (1st row=column heads) until there are no records left. There could be many hundreds of thousands of records, perhaps up to 700000 .... so I can't just hard code it to do 2 or 3 Copies Unfortunately on the second time I use the CopyFromRecordset method it fails, and for some reason the recordset 'AbsolutePosition' property is set to -3, even though I can get the recordcount... Here is my code: CODE Dim oConn As New ADODB.Connection Dim oRec As New ADODB.Recordset Dim iNoRec As Long Dim iCount As Long Dim iCurrRec As Long Dim iNoSheets As Integer, iSheet As Integer, iFlds As Integer Dim sQry As String, sColumnHeads As String oConn.Open "DBQ=G:\EVERYONE\Ad\IPS Group\ddi\;DefaultDir=G:\EVERYONE\Ad\IPS Group\ddi\;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;Extensions=asc,csv,tab,txt;FIL =text;MaxBufferSize=2048;MaxScanRows=25;PageTimeou t=5;SafeTransactions=0;Threads=3;UID=admin;UserCom mitSync=Yes;" sQry = "select FUNDCODE, CLIENT_SPARE, FIN_STMT_CURRCY, ACCOUNT, SUBACCOUNT, LOCAL_CURRENCY, BASIS," _ & "CD_ACTIVITY_SIGN + CD_ACTIVITY as CD_ACTIVITY1, CY_ACTIVITY_SIGN + CY_ACTIVITY as CY_ACTIVITY1, PROC_RATIO, DATE_ADDED," _ & "TIME_ADDED, ADD_PROGRAM_ID, DATE_UPDATED, TIME_UPDATED, UPD_PROGRAM_ID from l902glm#txt" With oRec .CursorLocation = adUseClient .Open sQry, oConn Sheets("Sheet1").Name = "l902glm_1" For iFlds = 0 To .Fields.Count - 1 Sheets("l902glm_1").Cells(1, iFlds + 1).Value = .Fields(iFlds).Name Next End With iCurrRec = 1 iSheet = 1 iNoRec = oRec.RecordCount oRec.MoveFirst Sheets("l902glm_" & iSheet).Select Range("A2").Select Do While Not iCurrRec iNoRec oRec.Move iCurrRec - 1 ActiveCell.CopyFromRecordset oRec, 65536 If oRec.RecordCount = iCurrRec Then Sheets.Add After:=Sheets("l902glm_" & iSheet) iSheet = iSheet + 1 Sheets(Sheets.Count).Name = "l902glm_" & iSheet iCurrRec = iCurrRec + 65535 Sheets("l902glm_" & IIf(iSheet 1, iSheet - 1, 1)).Activate ' copy column/field names ... to next sheet Sheets("l902glm_" & IIf(iSheet 1, iSheet - 1, 1)).Range(Cells(1, 1), Cells(1, oRec.Fields.Count)).Copy Destination:=Sheets("l902glm_" & iSheet).Range("A1") Sheets("l902glm_" & iSheet).Select Range("A2").Select End If Loop 'MsgBox oRec.RecordCount 'oRec.Move 65537 oRec.Close oConn.Close END I'd be grtateful for any help, ideas, improvements, solutions, or even some sympathy... thanks Philip |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - CopyFromRecordset (ADO) Problems with 107000 records
"Philip" wrote in message ... Hi, I have an ADO recordset with 107000+ records (loaded from text file using schema.ini file and ADBC Text Driver). I need to get this data into Excel sheets, first, copy 65535 (1st row=column heads), then keep copying 65535 onto new worksheets (1st row=column heads) until there are no records left. There could be many hundreds of thousands of records, perhaps up to 700000 ... so I can't just hard code it to do 2 or 3 Copies Unfortunately on the second time I use the CopyFromRecordset method it fails, and for some reason the recordset 'AbsolutePosition' property is set to -3, even though I can get the recordcount... Here is my code: CODE Dim oConn As New ADODB.Connection Dim oRec As New ADODB.Recordset Dim iNoRec As Long Dim iCount As Long Dim iCurrRec As Long Dim iNoSheets As Integer, iSheet As Integer, iFlds As Integer Dim sQry As String, sColumnHeads As String oConn.Open "DBQ=G:\EVERYONE\Ad\IPS Group\ddi\;DefaultDir=G:\EVERYONE\Ad\IPS Group\ddi\;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;Extensions=asc,csv,tab,txt;FIL =text;MaxBufferSize=2048;M axScanRows=25;PageTimeout=5;SafeTransactions=0;Thr eads=3;UID=admin;UserCommi tSync=Yes;" sQry = "select FUNDCODE, CLIENT_SPARE, FIN_STMT_CURRCY, ACCOUNT, SUBACCOUNT, LOCAL_CURRENCY, BASIS," _ & "CD_ACTIVITY_SIGN + CD_ACTIVITY as CD_ACTIVITY1, CY_ACTIVITY_SIGN + CY_ACTIVITY as CY_ACTIVITY1, PROC_RATIO, DATE_ADDED," _ & "TIME_ADDED, ADD_PROGRAM_ID, DATE_UPDATED, TIME_UPDATED, UPD_PROGRAM_ID from l902glm#txt" With oRec .CursorLocation = adUseClient .Open sQry, oConn Sheets("Sheet1").Name = "l902glm_1" For iFlds = 0 To .Fields.Count - 1 Sheets("l902glm_1").Cells(1, iFlds + 1).Value = ..Fields(iFlds).Name Next End With iCurrRec = 1 iSheet = 1 iNoRec = oRec.RecordCount oRec.MoveFirst Sheets("l902glm_" & iSheet).Select Range("A2").Select Do While Not iCurrRec iNoRec oRec.Move iCurrRec - 1 ActiveCell.CopyFromRecordset oRec, 65536 If oRec.RecordCount = iCurrRec Then Sheets.Add After:=Sheets("l902glm_" & iSheet) iSheet = iSheet + 1 Sheets(Sheets.Count).Name = "l902glm_" & iSheet iCurrRec = iCurrRec + 65535 Sheets("l902glm_" & IIf(iSheet 1, iSheet - 1, 1)).Activate ' copy column/field names ... to next sheet Sheets("l902glm_" & IIf(iSheet 1, iSheet - 1, 1)).Range(Cells(1, 1), Cells(1, oRec.Fields.Count)).Copy Destination:=Sheets("l902glm_" & iSheet).Range("A1") Sheets("l902glm_" & iSheet).Select Range("A2").Select End If Loop 'MsgBox oRec.RecordCount 'oRec.Move 65537 oRec.Close oConn.Close END I'd be grtateful for any help, ideas, improvements, solutions, or even some sympathy... thanks Philip Are you sure you need every darn record? Wouldn't it make sense to use the WHERE clause to limit the records you get? /Fredrik |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - CopyFromRecordset (ADO) Problems with 107000 records
Might be worth it to describe first what exactly has to be done as there may
be other methods to achieve the same. Maybe you even don't need a recordset for example if you could do the job with low level file IO functions. Another option is to transfer the recordset to an array with GetRows and then handle the array. A third option is to use Access as this would bypass the 65536 row limitation. RBS "Philip" wrote in message ... Hi, I have an ADO recordset with 107000+ records (loaded from text file using schema.ini file and ADBC Text Driver). I need to get this data into Excel sheets, first, copy 65535 (1st row=column heads), then keep copying 65535 onto new worksheets (1st row=column heads) until there are no records left. There could be many hundreds of thousands of records, perhaps up to 700000 ... so I can't just hard code it to do 2 or 3 Copies Unfortunately on the second time I use the CopyFromRecordset method it fails, and for some reason the recordset 'AbsolutePosition' property is set to -3, even though I can get the recordcount... Here is my code: CODE Dim oConn As New ADODB.Connection Dim oRec As New ADODB.Recordset Dim iNoRec As Long Dim iCount As Long Dim iCurrRec As Long Dim iNoSheets As Integer, iSheet As Integer, iFlds As Integer Dim sQry As String, sColumnHeads As String oConn.Open "DBQ=G:\EVERYONE\Ad\IPS Group\ddi\;DefaultDir=G:\EVERYONE\Ad\IPS Group\ddi\;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;Extensions=asc,csv,tab,txt;FIL =text;MaxBufferSize=2048;MaxScanRows=25;PageTimeou t=5;SafeTransactions=0;Threads=3;UID=admin;UserCom mitSync=Yes;" sQry = "select FUNDCODE, CLIENT_SPARE, FIN_STMT_CURRCY, ACCOUNT, SUBACCOUNT, LOCAL_CURRENCY, BASIS," _ & "CD_ACTIVITY_SIGN + CD_ACTIVITY as CD_ACTIVITY1, CY_ACTIVITY_SIGN + CY_ACTIVITY as CY_ACTIVITY1, PROC_RATIO, DATE_ADDED," _ & "TIME_ADDED, ADD_PROGRAM_ID, DATE_UPDATED, TIME_UPDATED, UPD_PROGRAM_ID from l902glm#txt" With oRec .CursorLocation = adUseClient .Open sQry, oConn Sheets("Sheet1").Name = "l902glm_1" For iFlds = 0 To .Fields.Count - 1 Sheets("l902glm_1").Cells(1, iFlds + 1).Value = .Fields(iFlds).Name Next End With iCurrRec = 1 iSheet = 1 iNoRec = oRec.RecordCount oRec.MoveFirst Sheets("l902glm_" & iSheet).Select Range("A2").Select Do While Not iCurrRec iNoRec oRec.Move iCurrRec - 1 ActiveCell.CopyFromRecordset oRec, 65536 If oRec.RecordCount = iCurrRec Then Sheets.Add After:=Sheets("l902glm_" & iSheet) iSheet = iSheet + 1 Sheets(Sheets.Count).Name = "l902glm_" & iSheet iCurrRec = iCurrRec + 65535 Sheets("l902glm_" & IIf(iSheet 1, iSheet - 1, 1)).Activate ' copy column/field names ... to next sheet Sheets("l902glm_" & IIf(iSheet 1, iSheet - 1, 1)).Range(Cells(1, 1), Cells(1, oRec.Fields.Count)).Copy Destination:=Sheets("l902glm_" & iSheet).Range("A1") Sheets("l902glm_" & iSheet).Select Range("A2").Select End If Loop 'MsgBox oRec.RecordCount 'oRec.Move 65537 oRec.Close oConn.Close END I'd be grtateful for any help, ideas, improvements, solutions, or even some sympathy... thanks Philip |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - CopyFromRecordset (ADO) Problems with 107000 recor
Hi,
Well, files containing 65536+ records need to be imported into MS Excel for checking. I agree that Access would be a nice option, but it isn't an option as the person checking the data won't have MS Access. So I decided to use ADO as File I/O is unbelievably slow - for example, why read a file line by line when you can read the entire file using ADO / ODBC in 3 seconds then use Excels 'CopyFromrecordset' method to dump it into MS Excel...where the data has to be. Anyway, I solved it... it turns out that the behaviour (seems to be undocumented!) of the CopyFromRecordset method is to take the first 65536 records, and move the recordset pointer to the first unread record... so in fact all I have to do is loop unti EOF, and in the loop add a new sheet, rename it, and call CopyFromrecordst again... nice! Thanks anyway :) Philip "RB Smissaert" wrote: Might be worth it to describe first what exactly has to be done as there may be other methods to achieve the same. Maybe you even don't need a recordset for example if you could do the job with low level file IO functions. Another option is to transfer the recordset to an array with GetRows and then handle the array. A third option is to use Access as this would bypass the 65536 row limitation. RBS "Philip" wrote in message ... Hi, I have an ADO recordset with 107000+ records (loaded from text file using schema.ini file and ADBC Text Driver). I need to get this data into Excel sheets, first, copy 65535 (1st row=column heads), then keep copying 65535 onto new worksheets (1st row=column heads) until there are no records left. There could be many hundreds of thousands of records, perhaps up to 700000 ... so I can't just hard code it to do 2 or 3 Copies Unfortunately on the second time I use the CopyFromRecordset method it fails, and for some reason the recordset 'AbsolutePosition' property is set to -3, even though I can get the recordcount... Here is my code: CODE Dim oConn As New ADODB.Connection Dim oRec As New ADODB.Recordset Dim iNoRec As Long Dim iCount As Long Dim iCurrRec As Long Dim iNoSheets As Integer, iSheet As Integer, iFlds As Integer Dim sQry As String, sColumnHeads As String oConn.Open "DBQ=G:\EVERYONE\Ad\IPS Group\ddi\;DefaultDir=G:\EVERYONE\Ad\IPS Group\ddi\;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;Extensions=asc,csv,tab,txt;FIL =text;MaxBufferSize=2048;MaxScanRows=25;PageTimeou t=5;SafeTransactions=0;Threads=3;UID=admin;UserCom mitSync=Yes;" sQry = "select FUNDCODE, CLIENT_SPARE, FIN_STMT_CURRCY, ACCOUNT, SUBACCOUNT, LOCAL_CURRENCY, BASIS," _ & "CD_ACTIVITY_SIGN + CD_ACTIVITY as CD_ACTIVITY1, CY_ACTIVITY_SIGN + CY_ACTIVITY as CY_ACTIVITY1, PROC_RATIO, DATE_ADDED," _ & "TIME_ADDED, ADD_PROGRAM_ID, DATE_UPDATED, TIME_UPDATED, UPD_PROGRAM_ID from l902glm#txt" With oRec .CursorLocation = adUseClient .Open sQry, oConn Sheets("Sheet1").Name = "l902glm_1" For iFlds = 0 To .Fields.Count - 1 Sheets("l902glm_1").Cells(1, iFlds + 1).Value = .Fields(iFlds).Name Next End With iCurrRec = 1 iSheet = 1 iNoRec = oRec.RecordCount oRec.MoveFirst Sheets("l902glm_" & iSheet).Select Range("A2").Select Do While Not iCurrRec iNoRec oRec.Move iCurrRec - 1 ActiveCell.CopyFromRecordset oRec, 65536 If oRec.RecordCount = iCurrRec Then Sheets.Add After:=Sheets("l902glm_" & iSheet) iSheet = iSheet + 1 Sheets(Sheets.Count).Name = "l902glm_" & iSheet iCurrRec = iCurrRec + 65535 Sheets("l902glm_" & IIf(iSheet 1, iSheet - 1, 1)).Activate ' copy column/field names ... to next sheet Sheets("l902glm_" & IIf(iSheet 1, iSheet - 1, 1)).Range(Cells(1, 1), Cells(1, oRec.Fields.Count)).Copy Destination:=Sheets("l902glm_" & iSheet).Range("A1") Sheets("l902glm_" & iSheet).Select Range("A2").Select End If Loop 'MsgBox oRec.RecordCount 'oRec.Move 65537 oRec.Close oConn.Close END I'd be grtateful for any help, ideas, improvements, solutions, or even some sympathy... thanks Philip |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - CopyFromRecordset (ADO) Problems with 107000 recor
"Philip" wrote in message ... Hi, Well, files containing 65536+ records need to be imported into MS Excel for checking. I agree that Access would be a nice option, but it isn't an option as the person checking the data won't have MS Access. So I decided to use ADO as File I/O is unbelievably slow - for example, why read a file line by line when you can read the entire file using ADO / ODBC in 3 seconds then use Excels 'CopyFromrecordset' method to dump it into MS Excel...where the data has to be. Anyway, I solved it... it turns out that the behaviour (seems to be undocumented!) of the CopyFromRecordset method is to take the first 65536 records, and move the recordset pointer to the first unread record... so in fact all I have to do is loop unti EOF, and in the loop add a new sheet, rename it, and call CopyFromrecordst again... nice! Thanks anyway :) Maybe this solves th problem in a way. Maybe there's an easier way. I have never used SQL to get data from a txt file,anyway depending on what kind of checking you are doing, maybe it's possible to make a smarter SQL statement that selects the records that have some kind of error in them. So.. What kind of checking do you do? /Fredrik |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - CopyFromRecordset (ADO) Problems with 107000 recor
OK, thanks for the feedback.
I am not sure file IO will always be slow. You can read the whole file to a string variable without any looping, but then of course you haven't got your data in Excel yet. Will keep that behaviour of CopyFromRecordset in mind. RBS "Philip" wrote in message ... Hi, Well, files containing 65536+ records need to be imported into MS Excel for checking. I agree that Access would be a nice option, but it isn't an option as the person checking the data won't have MS Access. So I decided to use ADO as File I/O is unbelievably slow - for example, why read a file line by line when you can read the entire file using ADO / ODBC in 3 seconds then use Excels 'CopyFromrecordset' method to dump it into MS Excel...where the data has to be. Anyway, I solved it... it turns out that the behaviour (seems to be undocumented!) of the CopyFromRecordset method is to take the first 65536 records, and move the recordset pointer to the first unread record... so in fact all I have to do is loop unti EOF, and in the loop add a new sheet, rename it, and call CopyFromrecordst again... nice! Thanks anyway :) Philip "RB Smissaert" wrote: Might be worth it to describe first what exactly has to be done as there may be other methods to achieve the same. Maybe you even don't need a recordset for example if you could do the job with low level file IO functions. Another option is to transfer the recordset to an array with GetRows and then handle the array. A third option is to use Access as this would bypass the 65536 row limitation. RBS "Philip" wrote in message ... Hi, I have an ADO recordset with 107000+ records (loaded from text file using schema.ini file and ADBC Text Driver). I need to get this data into Excel sheets, first, copy 65535 (1st row=column heads), then keep copying 65535 onto new worksheets (1st row=column heads) until there are no records left. There could be many hundreds of thousands of records, perhaps up to 700000 ... so I can't just hard code it to do 2 or 3 Copies Unfortunately on the second time I use the CopyFromRecordset method it fails, and for some reason the recordset 'AbsolutePosition' property is set to -3, even though I can get the recordcount... Here is my code: CODE Dim oConn As New ADODB.Connection Dim oRec As New ADODB.Recordset Dim iNoRec As Long Dim iCount As Long Dim iCurrRec As Long Dim iNoSheets As Integer, iSheet As Integer, iFlds As Integer Dim sQry As String, sColumnHeads As String oConn.Open "DBQ=G:\EVERYONE\Ad\IPS Group\ddi\;DefaultDir=G:\EVERYONE\Ad\IPS Group\ddi\;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;Extensions=asc,csv,tab,txt;FIL =text;MaxBufferSize=2048;MaxScanRows=25;PageTimeou t=5;SafeTransactions=0;Threads=3;UID=admin;UserCom mitSync=Yes;" sQry = "select FUNDCODE, CLIENT_SPARE, FIN_STMT_CURRCY, ACCOUNT, SUBACCOUNT, LOCAL_CURRENCY, BASIS," _ & "CD_ACTIVITY_SIGN + CD_ACTIVITY as CD_ACTIVITY1, CY_ACTIVITY_SIGN + CY_ACTIVITY as CY_ACTIVITY1, PROC_RATIO, DATE_ADDED," _ & "TIME_ADDED, ADD_PROGRAM_ID, DATE_UPDATED, TIME_UPDATED, UPD_PROGRAM_ID from l902glm#txt" With oRec .CursorLocation = adUseClient .Open sQry, oConn Sheets("Sheet1").Name = "l902glm_1" For iFlds = 0 To .Fields.Count - 1 Sheets("l902glm_1").Cells(1, iFlds + 1).Value = .Fields(iFlds).Name Next End With iCurrRec = 1 iSheet = 1 iNoRec = oRec.RecordCount oRec.MoveFirst Sheets("l902glm_" & iSheet).Select Range("A2").Select Do While Not iCurrRec iNoRec oRec.Move iCurrRec - 1 ActiveCell.CopyFromRecordset oRec, 65536 If oRec.RecordCount = iCurrRec Then Sheets.Add After:=Sheets("l902glm_" & iSheet) iSheet = iSheet + 1 Sheets(Sheets.Count).Name = "l902glm_" & iSheet iCurrRec = iCurrRec + 65535 Sheets("l902glm_" & IIf(iSheet 1, iSheet - 1, 1)).Activate ' copy column/field names ... to next sheet Sheets("l902glm_" & IIf(iSheet 1, iSheet - 1, 1)).Range(Cells(1, 1), Cells(1, oRec.Fields.Count)).Copy Destination:=Sheets("l902glm_" & iSheet).Range("A1") Sheets("l902glm_" & iSheet).Select Range("A2").Select End If Loop 'MsgBox oRec.RecordCount 'oRec.Move 65537 oRec.Close oConn.Close END I'd be grtateful for any help, ideas, improvements, solutions, or even some sympathy... thanks Philip |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - CopyFromRecordset (ADO) Problems with 107000 recor
Even when you use File I/O with loops it seems pretty quick.
Just tried it with this on a 30000 row 6 column text file and it did it in 0.3 secs Function OpenTextFileToArray(ByVal txtFile As String, _ ByRef arr As Variant, _ ByVal LBRow As Long, _ ByVal UBRow As Long, _ ByVal LBCol As Long, _ ByVal UBCol As Long, _ Optional ByVal bSkipFields As Boolean = False) As Variant Dim hFile As Long Dim R As Long Dim c As Long Dim varWaste hFile = FreeFile Open txtFile For Input As #hFile On Error Resume Next If bSkipFields = False Then For R = LBRow To UBRow For c = LBCol To UBCol Input #hFile, arr(R, c) Next Next Else For c = LBCol To UBCol Input #hFile, varWaste Next For R = LBRow To UBRow For c = LBCol To UBCol Input #hFile, arr(R, c) Next Next End If Close #hFile OpenTextFileToArray = arr End Function RBS "Philip" wrote in message ... Hi, Well, files containing 65536+ records need to be imported into MS Excel for checking. I agree that Access would be a nice option, but it isn't an option as the person checking the data won't have MS Access. So I decided to use ADO as File I/O is unbelievably slow - for example, why read a file line by line when you can read the entire file using ADO / ODBC in 3 seconds then use Excels 'CopyFromrecordset' method to dump it into MS Excel...where the data has to be. Anyway, I solved it... it turns out that the behaviour (seems to be undocumented!) of the CopyFromRecordset method is to take the first 65536 records, and move the recordset pointer to the first unread record... so in fact all I have to do is loop unti EOF, and in the loop add a new sheet, rename it, and call CopyFromrecordst again... nice! Thanks anyway :) Philip "RB Smissaert" wrote: Might be worth it to describe first what exactly has to be done as there may be other methods to achieve the same. Maybe you even don't need a recordset for example if you could do the job with low level file IO functions. Another option is to transfer the recordset to an array with GetRows and then handle the array. A third option is to use Access as this would bypass the 65536 row limitation. RBS "Philip" wrote in message ... Hi, I have an ADO recordset with 107000+ records (loaded from text file using schema.ini file and ADBC Text Driver). I need to get this data into Excel sheets, first, copy 65535 (1st row=column heads), then keep copying 65535 onto new worksheets (1st row=column heads) until there are no records left. There could be many hundreds of thousands of records, perhaps up to 700000 ... so I can't just hard code it to do 2 or 3 Copies Unfortunately on the second time I use the CopyFromRecordset method it fails, and for some reason the recordset 'AbsolutePosition' property is set to -3, even though I can get the recordcount... Here is my code: CODE Dim oConn As New ADODB.Connection Dim oRec As New ADODB.Recordset Dim iNoRec As Long Dim iCount As Long Dim iCurrRec As Long Dim iNoSheets As Integer, iSheet As Integer, iFlds As Integer Dim sQry As String, sColumnHeads As String oConn.Open "DBQ=G:\EVERYONE\Ad\IPS Group\ddi\;DefaultDir=G:\EVERYONE\Ad\IPS Group\ddi\;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;Extensions=asc,csv,tab,txt;FIL =text;MaxBufferSize=2048;MaxScanRows=25;PageTimeou t=5;SafeTransactions=0;Threads=3;UID=admin;UserCom mitSync=Yes;" sQry = "select FUNDCODE, CLIENT_SPARE, FIN_STMT_CURRCY, ACCOUNT, SUBACCOUNT, LOCAL_CURRENCY, BASIS," _ & "CD_ACTIVITY_SIGN + CD_ACTIVITY as CD_ACTIVITY1, CY_ACTIVITY_SIGN + CY_ACTIVITY as CY_ACTIVITY1, PROC_RATIO, DATE_ADDED," _ & "TIME_ADDED, ADD_PROGRAM_ID, DATE_UPDATED, TIME_UPDATED, UPD_PROGRAM_ID from l902glm#txt" With oRec .CursorLocation = adUseClient .Open sQry, oConn Sheets("Sheet1").Name = "l902glm_1" For iFlds = 0 To .Fields.Count - 1 Sheets("l902glm_1").Cells(1, iFlds + 1).Value = .Fields(iFlds).Name Next End With iCurrRec = 1 iSheet = 1 iNoRec = oRec.RecordCount oRec.MoveFirst Sheets("l902glm_" & iSheet).Select Range("A2").Select Do While Not iCurrRec iNoRec oRec.Move iCurrRec - 1 ActiveCell.CopyFromRecordset oRec, 65536 If oRec.RecordCount = iCurrRec Then Sheets.Add After:=Sheets("l902glm_" & iSheet) iSheet = iSheet + 1 Sheets(Sheets.Count).Name = "l902glm_" & iSheet iCurrRec = iCurrRec + 65535 Sheets("l902glm_" & IIf(iSheet 1, iSheet - 1, 1)).Activate ' copy column/field names ... to next sheet Sheets("l902glm_" & IIf(iSheet 1, iSheet - 1, 1)).Range(Cells(1, 1), Cells(1, oRec.Fields.Count)).Copy Destination:=Sheets("l902glm_" & iSheet).Range("A1") Sheets("l902glm_" & iSheet).Select Range("A2").Select End If Loop 'MsgBox oRec.RecordCount 'oRec.Move 65537 oRec.Close oConn.Close END I'd be grtateful for any help, ideas, improvements, solutions, or even some sympathy... thanks Philip |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - CopyFromRecordset (ADO) Problems with 107000 records
Philip wrote: I have an ADO recordset with 107000+ records (loaded from text file using schema.ini file and ADBC Text Driver). I need to get this data into Excel sheets, first, copy 65535 (1st row=column heads), then keep copying 65535 onto new worksheets (1st row=column heads) until there are no records left. Here's a suggestion (thanks TK). Using Excel's CopyFromRecordset on a recordset with more records than the 65536 maximum worksheet rows does not cause the method to fail. Rather, the cursor is merely moved e.g. to record 65537. Therefore, you could do something like this: Sub test() Dim rs As Object Set rs = CreateObject("ADOR.Recordset") rs.Open _ "SELECT * FROM 100K_row_table;", _ "Provider=Microsoft.Jet.OLEDB.*4.0;" & _ "Data Source=C:\Tempo\New_Jet_DB.mdb*" Dim Counter As Long With Workbooks("MyWorkbook.xls") Do While Not rs.EOF Counter = Counter + 1 .Worksheets(Counter).Range("A1*") _ .CopyFromRecordset rs Loop End With End Sub Jamie. -- |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - CopyFromRecordset (ADO) Problems with 107000 recor
Hi,
Yes I need every darn record.... :) The users need to be able research discrepancies, so they need to see all of the source records... Nice to know that straight file i/o is pretty fast too ... that's good for when ADO / ODBC is not available for whatever reason... But the CopyFromRecordset method just seems to me to be more elegant...and this undocumented behaviour is very helpful too - definitely something to remember I think! So thanks all, it's looking good now, just tested with 710000 records and it's very fast... regards Philip "Fredrik Wahlgren" wrote: <snip Are you sure you need every darn record? Wouldn't it make sense to use the WHERE clause to limit the records you get? /Fredrik |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search Sample for Oracle-to-Excel extraction with CopyFromRecordset | Excel Discussion (Misc queries) | |||
More than 65,000 records for Excel 2007 problems! | Excel Discussion (Misc queries) | |||
XY Scatter-problems with large data records | Excel Discussion (Misc queries) | |||
Format data in Excel after using copyfromrecordset | Excel Discussion (Misc queries) | |||
Excel 2003 - 'CopyFromRecordset' Error | Excel Programming |