Rookie11
12-12-2016, 07:16 AM
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
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