Importing stored procedures into Excel with parameters
 

I wish to import the results of a sql stored procedure into Excel. The
stored procedure has two date parameters yyyy-mm-dd and an interger
subscription id parameter. The user needs to be able to change the dates and
subscription id.

I haven't been able to set the import up without putting in fixed dates and
a subscription id as Microsoft Query doesn't allow parameters to be set up if
the query can't be represented geographically
e.g.
exec dbo.sp_AutomatedStatsByMonth @DateFrom='2008-05-01',
@DateTo='2008-05-31', @SubscriberID='4603345'

If I change the parameters to '?' the error returned is - Error converting
data type varchar to datetime.

Importing stored procedures into Excel with parameters
 

I have some visual basic code that populates an excel sheet with data from a
stored procedure. The procedure takes one date as parameter. You should
extend to two. Data from stored procedure goes into a recordset RS. RS is
then printed from line 6 in an excel worksheet.

Private Sub ButtonShowData_Click()
On Error GoTo Errhandler
If IsNull(Me.txtDateReport) Then
MsgBox ("You have to select a date")
Exit Sub
End If

'Prepare date to a string that sql server can understand
'Make date format independent of settings in control panel regional settings
'Control txtDateReport is formatted as date in accordance with regional
settitings
'Add leading zero to day and month if they are only one digit:
Dim StrDate As String, StrMonth As String, StrDay As String
StrMonth = Month(Me.txtDateReport.Value)
If Len(StrMonth) = 1 Then
StrMonth = "0" + StrMonth
End If

StrDay = Day(Me.txtDateReport)
If Len(StrDay) = 1 Then
StrDay = "0" + StrDay
End If
StrDate = Year(Date) & "/" & StrMonth & "/" + StrDay


Dim Wbook As Workbook
Dim wsSheet1 As Worksheet


Set Wbook = ThisWorkbook

Set wsSheet1 = Wbook.Worksheets.Item("Sheet5") 'Name of sheet where data
is printed
wsSheet1.Unprotect
wsSheet1.Range("A6").CurrentRegion.Clear

Dim Cnxn As New ADODB.Connection, strCnxn As String, CMD As New
ADODB.Command, PDate As New ADODB.Parameter, RS As ADODB.Recordset

'set up connection string for your own server, here is mine:
strCnxn = "Provider='sqloledb.1';Data Source='tns-server;" & _
"Initial Catalog='Lenovo'; Persist Security info='TRUE';User
ID='TestUser'; Password='TestUser'"

Cnxn.Open strCnxn

Set CMD.ActiveConnection = Cnxn



CMD.CommandType = adCmdStoredProc
CMD.CommandText = "LSP_MonthlyReport"

Set PDate = CMD.CreateParameter("Date", adVarChar, adParamInput, 20,
StrDate)
CMD.Parameters.Append PDate
Set RS = New ADODB.Recordset
Set RS = CMD.Execute

'Set column names on worksheet in line 5:
Dim Ctr As Integer
For Ctr = 0 To RS.Fields.Count - 1
wsSheet1.Cells(5, 1 + Ctr).Value = RS.Fields(Ctr).Name
Next Ctr

'Copy all data from recordset into excel worksheet from cells(6,1) (A6):
With wsSheet1
.Cells(6, 1).CopyFromRecordset RS
End With

RS.Close
Set RS = Nothing
Application.EnableEvents = True
Exit Sub
Errhandler:

End Sub

Importing stored procedures into Excel with parameters
 

Dear Tore

I've adapted your query for my situation and added on two more parameters
(please see below). My problem now is that when I run the query I get a
Microsoft Excel message saying Timeout expired.

Do you have any ideas on how I can fix this?

Private Sub CommandButton1_Click()

On Error GoTo Errhandler
Set Wbook = ThisWorkbook
Set wsSheet1 = Wbook.Worksheets.Item("Sheet1")
Set wsSheet2 = Wbook.Worksheets.Item("Sheet2")
If IsNull(wsSheet1.Range("A1")) Then
MsgBox ("You have to select a date")
Exit Sub
End If

wsSheet1.Unprotect
wsSheet2.Range("A2").CurrentRegion.Clear


Dim StrDate As String, StrMonth As String, StrDay As String
Startdate = wsSheet1.Range("A1")
enddate = wsSheet1.Range("B1")
subscriberid = wsSheet1.Range("C1")
Dim Cnxn As New ADODB.Connection, strCnxn As String, CMD As New
ADODB.Command, PStartdate As New ADODB.Parameter, Penddate As New
ADODB.Parameter, Psubscriberid As New ADODB.Parameter, rs As ADODB.Recordset

strCnxn = "Provider='sqloledb.1';Data Source='lnsatechdev';" & _
"Initial Catalog='bwsubsweb'; Connect Timeout=0;Command
Timeout=0;Persist Security info='TRUE'; User ID=sa;Password='buttys'"

Cnxn.Open strCnxn
Set CMD.ActiveConnection = Cnxn


CMD.CommandType = adCmdStoredProc
CMD.CommandText = "sp_AutomatedStatsByMonth"

Set PStartdate = CMD.CreateParameter("@DateFrom", adDBDate,
adParamInput, , Startdate)
CMD.Parameters.Append PStartdate

Set Penddate = CMD.CreateParameter("@DateTo", adDBDate, adParamInput, ,
enddate)
CMD.Parameters.Append Penddate

Set Psubscriberid = CMD.CreateParameter("@subscriberid", adInteger,
adParamInput, , subscriberid)
CMD.Parameters.Append Psubscriberid
Set rs = New ADODB.Recordset
Set rs = CMD.Execute


'Set column names on worksheet in line 5:
Dim Ctr As Integer
For Ctr = 0 To rs.Fields.Count - 1
wsSheet2.Cells(5, 1 + Ctr).Value = rs.Fields(Ctr).Name
Next Ctr

'Copy all data from recordset into excel worksheet from cells(6,1) (A6):
With wsSheet2
.Cells(6, 1).CopyFromRecordset rs
End With

rs.Close
Set rs = Nothing
Set CMD = Nothing
Cnxn.Close
Set Cnxn = Nothing
Application.EnableEvents = True
Exit Sub
Errhandler:
MsgBox (Err.Description)

End Sub







"Tore" wrote:

I have some visual basic code that populates an excel sheet with data from a
stored procedure. The procedure takes one date as parameter. You should
extend to two. Data from stored procedure goes into a recordset RS. RS is
then printed from line 6 in an excel worksheet.

Private Sub ButtonShowData_Click()
On Error GoTo Errhandler
If IsNull(Me.txtDateReport) Then
MsgBox ("You have to select a date")
Exit Sub
End If

'Prepare date to a string that sql server can understand
'Make date format independent of settings in control panel regional settings
'Control txtDateReport is formatted as date in accordance with regional
settitings
'Add leading zero to day and month if they are only one digit:
Dim StrDate As String, StrMonth As String, StrDay As String
StrMonth = Month(Me.txtDateReport.Value)
If Len(StrMonth) = 1 Then
StrMonth = "0" + StrMonth
End If

StrDay = Day(Me.txtDateReport)
If Len(StrDay) = 1 Then
StrDay = "0" + StrDay
End If
StrDate = Year(Date) & "/" & StrMonth & "/" + StrDay


Dim Wbook As Workbook
Dim wsSheet1 As Worksheet


Set Wbook = ThisWorkbook

Set wsSheet1 = Wbook.Worksheets.Item("Sheet5") 'Name of sheet where data
is printed
wsSheet1.Unprotect
wsSheet1.Range("A6").CurrentRegion.Clear

Dim Cnxn As New ADODB.Connection, strCnxn As String, CMD As New
ADODB.Command, PDate As New ADODB.Parameter, RS As ADODB.Recordset

'set up connection string for your own server, here is mine:
strCnxn = "Provider='sqloledb.1';Data Source='tns-server;" & _
"Initial Catalog='Lenovo'; Persist Security info='TRUE';User
ID='TestUser'; Password='TestUser'"

Cnxn.Open strCnxn

Set CMD.ActiveConnection = Cnxn



CMD.CommandType = adCmdStoredProc
CMD.CommandText = "LSP_MonthlyReport"

Set PDate = CMD.CreateParameter("Date", adVarChar, adParamInput, 20,
StrDate)
CMD.Parameters.Append PDate
Set RS = New ADODB.Recordset
Set RS = CMD.Execute

'Set column names on worksheet in line 5:
Dim Ctr As Integer
For Ctr = 0 To RS.Fields.Count - 1
wsSheet1.Cells(5, 1 + Ctr).Value = RS.Fields(Ctr).Name
Next Ctr

'Copy all data from recordset into excel worksheet from cells(6,1) (A6):
With wsSheet1
.Cells(6, 1).CopyFromRecordset RS
End With

RS.Close
Set RS = Nothing
Application.EnableEvents = True
Exit Sub
Errhandler:

End Sub

Importing stored procedures into Excel with parameters
 

To set a timeout of 60 seconds:

CMD.CommandTimeout = 60

Set it to 120 or 180 or whatever you need. Default (if no timeout is set) is
often 30 seconds. Make sure you set it before you execute the stored
procedure, before you do CMD.Execute

Your stored procedure must be quite heavy needing more than 30 seconds.

Regards

Tore

Importing stored procedures into Excel with parameters
 

In your connection string you have set Command Timeout = 0. I would increase
that one too. Connect Timeout = 0 as well, I would increase it.

I do not work that much with connection strings so I cannot be very accurate
here.

Tore