Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import extrenal data exceeds 65536
A few day ago, the question of how a recordset that has more rows than Excel
can be spanned into multiple worksheets. I attach the code for the suggestion I had made then. Note: I have use an Excel data source: modify code to use any source. I have selected 20000 rows as the maximum- in practice, this will be 65536 (Row 1 contains field names). 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
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 | |||
Import extrenal data exceeds 65536 | Excel Programming |