When Limit has reached in Excel, send to Sheet2

[Rookie11]

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

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

[SamT]

FYI:

  ' 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

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.

[p45cal]

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.

[p45cal]

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

with these:

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

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:

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).