Excel Add-In for Google Sheets

Build 24.0.9060

Calling Stored Procedures

public bool CallSP(string queryString, object paramNames, object paramValues)

This calls a stored procedure. It returns true on success, false otherwise. You can pass the name and value pair of each parameter to CallSP as two arrays. This is useful when working with many parameters and XML aggregates. In this case, the query string argument to CallSP must be the name of the parameter:

 CallSP("DownloadDocument",nameArray,valueArray)
The following example shows how to pass inputs to a stored procedure call and iterate through any output parameters.
Dim nameArray
nameArray = Array("Column1", "Column2")
Dim valueArray
valueArray = Array("Jon Doe", "Bob")
Dim module As New ExcelComModule
module.SetProviderName ("GoogleSheets")
module.SetConnectionString ("Spreadsheet=NorthwindOrders")
Query = "INSERT INTO Spreadsheet1_Sheet1 (Column1, Column2) VALUES (@Column1, @Column2)"
If module.Insert(Query, nameArray, valueArray) Then
  MsgBox "The insertion was successful."
Else
  MsgBox "The insertion failed."
End If
module.Close
Dim module As New ExcelComModule
module.SetProviderName ("GoogleSheets")
module.SetConnectionString ("Spreadsheet=NorthwindOrders")
Dim nameArray
nameArray = Array("Id")
Dim valueArray
valueArray = Array("7")
StoredProc = "DownloadDocument"
Cursor = Application.Cursor
Application.Cursor = xlWait
If module.CallSP(StoredProc, nameArray, valueArray) Then
  MsgBox "Execution was successful."
  Dim ColumnCount As Integer
  ColumnCount = module.GetColumnCount
  For Count = 0 To ColumnCount - 1
    Application.ActiveSheet.Cells(1, Count + 1).Value = module.GetColumnName(Count)
  Next 
  Dim RowIndex As Integer
  RowIndex = 10 
  While (Not module.EOF)
    For columnIndex = 0 To ColumnCount - 1
      If Conversion.CInt(module.GetColumnType(columnIndex)) = Conversion.CInt(vbDate) And Not IsNull(module.GetValue(columnIndex)) Then
        Application.ActiveSheet.Cells(RowIndex, columnIndex + 1).Value = Conversion.CDate(module.GetValue(columnIndex))
      Else
        Application.ActiveSheet.Cells(RowIndex, columnIndex + 1).Value = module.GetValue(columnIndex)
      End If
    Next
    module.MoveNext
    RowIndex = RowIndex + 1
  Wend
Else
  MsgBox "Execution failed."
End If
Application.Cursor = Cursor
module.Close

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9060