When Limit has reached in Excel, send to Sheet2

Hello,
I'm currently running a SQL Sp from Excel , but problem is the results return the max rows Excel allowed in one sheet, so I need to some way how transfer the results to the second page if the limit is reached?

Any Ideas? This is the code i have at the moment

Code:

---

Sub Button1_Click()
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim par As String
Dim WSP1 As Worksheet
Set con = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset


Application.DisplayStatusBar = True
Application.StatusBar = "Contacting SQL Server..."


' Remove any values in the cells where we want to put our Stored Procedure's results.
Dim rngRange As Range
Set rngRange = Range(Cells(8, 2), Cells(Rows.Count, 1)).EntireRow
rngRange.ClearContents


' Log into our SQL Server, and run the Stored Procedure
con.Open "Provider=SQLOLEDB;Data Source=;Initial Catalog=;Integrated Security=SSPI;Trusted_Connection=Yes;"
cmd.ActiveConnection = con


Dim prmCustomerID As ADODB.Parameter


' Set up the parameter for Stored Procedure
' (Parameter types can be adVarChar,adDate,adInteger)
' cmd.Parameters.Append cmd.CreateParameter("CustomerID", adVarChar, adParamInput, 10, Range("D2").Text)


Application.StatusBar = "Running stored procedure..."
cmd.CommandText = "SP_Billing"
Set rs = cmd.Execute(, , adCmdStoredProc)


' Copy the results to cell B7 on the first Worksheet
Set WSP1 = Worksheets(1)
WSP1.Activate
If rs.EOF = False Then WSP1.Cells(8, 2).CopyFromRecordset rs


rs.Close
Set rs = Nothing
Set cmd = Nothing


con.Close
Set con = Nothing


Application.StatusBar = "Data successfully updated."
End Sub

---

FYI:

Code:

---

  ' Remove any values in the cells where we want to put our Stored Procedure's results.
  Range("8:" & Rows.Count).ClearContents

---

I'm not clear on how to simply split an array into tow or more parts, but

Code:

---

If Ubound(rs) > Rows.Count - 8 Then rs 'Too big for sheet

---

I think this question will be answered faster in the Excel Forum, so I moved it there.

I'm not overly familiar with recordsets'n'stuff however I think I can point you in the right direction (if I get more time I'll expand on this with some code).
The copyfromrecordset seems to exist to make it very easy indeed to move recordset data to an excel spreadsheet - but as you have discovered it has its own limitations - it uses the entire recordset, and if that's too big for a sheet, tough.
So back to basics. You can use the GetRows method to grab data in chunks (in air code):

Do While Not rs.EOF
w = rs.GetRows(WSP1.rows.count-10)
v=transpose(w)
place v in next sheet
Loop

The transpose part seems to be needed as the array w is not returned in a form to drop into a spreadsheet. I would tend to avoid the worksheetfunction TRANSPOSE here as it has size limitations (still, I think - things may have changed) so a separate function to do that would be safer.

Following on…
try replacing this line:
If rs.EOF = False Then WSP1.Cells(8, 2).CopyFromRecordset rs

with these:

Code:

---

RowsPerSheet = WSP1.Rows.Count - 10  'change this to suit you; it is the max number of rows to copy over to each sheet.
Do While Not rs.EOF
  w = rs.GetRows(RowsPerSheet)
  v = TransposeDim(w)
  'place v in next sheet:
  If WSP1HasBeenUsed Then
    Set NewSht = Sheets.Add(after:=Sheets(Sheets.Count))
  Else
    Set NewSht = WSP1
    WSP1HasBeenUsed = True
  End If
  NewSht.Cells(8, 2).Resize(UBound(v) + 1, UBound(v, 2) + 1) = v
Loop

---

which will need to be supported by a function stolen from https://support.microsoft.com/en-gb/kb/246335

Code:

---

Function TransposeDim(v As Variant) As Variant
' Custom Function to Transpose a 0-based array (v)
Dim X As Long, Y As Long, Xupper As Long, Yupper As Long
Dim tempArray As Variant

Xupper = UBound(v, 2)
Yupper = UBound(v, 1)
ReDim tempArray(Xupper, Yupper)
For X = 0 To Xupper
  For Y = 0 To Yupper
    tempArray(X, Y) = v(Y, X)
  Next Y
Next X
TransposeDim = tempArray
End Function

---

which can be placed below your existing sub.

If you have Option Explicit at the top of your code-module, you may need to add the following at the top of your sub near the other Dim statements:

Code:

---

Dim RowsPerSheet As Long, w, v, WSP1HasBeenUsed As Boolean, NewSht As Worksheet

---

ps. If you want new sheets to be placed directly after the first sheet (WSP1) you might be able to replace:
Set NewSht = Sheets.Add(after:=Sheets(Sheets.Count))
with:
Set NewSht = Sheets.Add(after:=NewSht)

(untested).