Excel cells passing parameters to SQL Server SP

[bbitzer]

Question:
If I have a stored procedure that runs behind a spreadsheet, how can I enter values into specified cells and the stored procedure will use those values as its parameters. I would like to do this to avoid having users edit the parameters in Microsoft Query directly.

bbitzer@computer.org

 

[Nikita6003]

Hiya,

you can simply pick up the param values straight from a worksheet. We work with a similar option using Sybase stored procs. We have one "Settings" sheet with all param values we need for running the process & the stored procs and read this into a global variable at runtime. The Settings sheet is set up to use Range names for each cell we want to pick up & store in the global variable.
I've added something like this on a separate module sheet

Option Explicit

Public Type Settings
  'General xl objects we need
  wkbCurrent As Workbook
  wksSettings As WorkSheet

  'Params
  datBusinessDate As Date
  sLocation As String
  dPercentile As Double

  'Log file settings
  sErrorLog As String
  sInfoLog As String
End Type

Public g_udtSettings As Settings

Public Sub SetGlobalVariables
  With g_udtSettings
    Set wkbCurrent = ThisWorkBook
    .wksSettings = .wkbCurrent.Sheets("Settings")

    .datBusinessDate = .wksSettings.Range("BusinessDate")
    .sLocation = .wksSettings.Range("Location")
    .dPercentile = .wksSettings.Range("Percentile")

    .sErrorLog = .wksSettings.Range("ErrorLog")
    .sInfoLog = .wksSettings.Range("InfoLog")
  End With
End Sub

End Type

Now when I need the params included in a stored proc, all I do is:

l_sCMDString = "exec getDeltaPerDesk '" & Format(g_udtSettings.datBusinessDate,"yyyymmdd") & "'"

and use that command string to execute the stored proc using ADO

HTH

Cheers
Nikki