Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import extrenal data exceeds 65536
"AA2e72E" wrote ...
I don't have any sample code to hand: I can suggest some ideas. ADO has a pagesize (no of rows) property which you can set to 65536 & then loop through the PageCount property to retrieve all records. Could you post some code that demonstrates how to this, please? Many thanks, Jamie. -- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import extrenal data exceeds 65536
Here's some code, annotated and tested: I have used a WORKBOOK as the source
data, modify the code to use another source. Sub Partition2() 'Get the Source data Cnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\ajay2.xls;Extended Properties=Excel 8.0;" Sql = "SELECT * FROM [Sheet1]" Set ADORS = CreateObject("ADODB.RecordSet") 'Number of records in a single sheet ADORS.PageSize = 20000 ADORS.Open Sql, Cnn, 1, 3 ' adOpenKeySet, adLockOptimistic 'Create Excel Workbook ... Set xl = CreateObject("Excel.Application") 'Hold user's default number of sheets UserWBS = xl.SheetsInNewWorkbook With ADORS '... with the right number of worksheets to hold all records xl.SheetsInNewWorkbook = .PageCount xl.Workbooks.Add xl.SheetsInNewWorkbook = UserWBS xl.Sheets.Select xl.Sheets(1).Activate 'Add field names to Row 1 in all sheets For i = 0 To .Fields.Count - 1 xl.Cells(1, i + 1).Select xl.Selection.Font.Bold = True xl.Selection.FormulaR1C1 = .Fields(i).Name Next 'Populate each sheet in turn For i = 1 To .PageCount xl.Sheets(i).Select xl.Range("A2").CopyFromRecordset ADORS, .PageSize, 256 Next xl.Sheets(1).Select xl.Sheets(1).Range("A1").Select End With ' Tidy up ADORS.Close Set ADORS = Nothing ' Make Excel visible, user must decide to save it or not xl.Visible = 1 Set xl = Nothing End Sub "Jamie Collins" wrote: "AA2e72E" wrote ... I don't have any sample code to hand: I can suggest some ideas. ADO has a pagesize (no of rows) property which you can set to 65536 & then loop through the PageCount property to retrieve all records. Could you post some code that demonstrates how to this, please? Many thanks, Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data exceeds chart limits | Charts and Charting in Excel | |||
How Can I import external data what is more than 65536 lines? | Excel Discussion (Misc queries) | |||
How do I import data into Excel that exceeds 70000 rows? | Excel Discussion (Misc queries) | |||
Import extrenal data exceeds 65536 | Excel Programming | |||
Import extrenal data exceeds 65536 | Excel Programming |