Opening data sets in excel that are too large for excel

[Courtney93]

Hey Guys!! I've got a dataset that is too large for excel, (a few million rows when the limit is 1048576)

To overcome this I've been copy and pasting within this limit which is time consuming, is there any way to automatically paste excess data into the next column?

Thanks in advance!!!

 

[Fazza]

Maybe the whole dataset doesn't need to be loaded to worksheets?

For example, if the (incoming) dataset is in a recordset object that recordset can be used to create a pivot table without loading the data to a worksheet. Refer this thread http://www.mrexcel.com/forum/showthread.php?t=315768

Or if you have the recordset and want to paste to multiple sheets, this codes shows an approach using CopyFromRecordset MaxRows (Excel help refers)

  With objRS
 
    .Open YourSQL, YourConnection
 
    'Save headers in array for quick pasting to worksheet
    ReDim arHeaders(1 To 1, 1 To .fields.Count)
    For i = 0 To .fields.Count - 1
      arHeaders(1, i + 1) = .fields(i).Name
    Next i
 
    'transfer recordset to workbook one page at a time
    j = 0
    Do While Not .EOF
      j = j + 1
      Set wks = wbk.Worksheets.Add(After:=wbk.Worksheets(j))
      wks.Name = "Data" & Format$(j, "00") & " at " & Format$(Now, "h.mm am/pm d-mmm-yy")
      wks.Range("A1").Resize(, i).Value = arHeaders
      wks.Range("A2").CopyFromRecordset Data:=objRS, MaxRows:=MaxWksRows
    Loop
    .Close
  End With
  Set objRS = Nothing

 

[Fazza]

PS. Maybe not relevant, but that code from Excel 2003