Need VBA for Excel parameter value assignation

Hi
I have a workbook which fetches a table of data from SQL, it does this via linking to an Office Data Connection file (which contains the conn. info such as server, database, Stored Proc. command)

The calling of the Stored Proc. takes the form of: {call dbo.myproc(?,?)} where the ? characters refer to SQL paramaters for the Stored Proc.
These variables are manually mapped to cell locations (eg; cells A1 and B1) in the Excel sheet when I am creating the connection.
However, I would like to determine the VBA code that could dynamically change these references (eg; to cells A2 and B2)
I tried turning on the macro recorder during this process to see what VBA was being generated, however there isn't any code for the mapping of the parameters to the cell values.
Thanks for your help!
Fergal
fjkilkenAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Here's a copy-paste of an Excel VBA connecting to SQL project where I pulled that off, using named ranges.  You'll have to figure out the 'dynamic' part...
Sub btn_detail_go_Click()

Application.Cursor = xlWait

'User hit Go! in the Summary tab.  Change the Details tab settings.
Range("dtStart_summary").Value = Range("dtStart_details").Value
Range("dtEnd_summary").Value = Range("dtEnd_details").Value
Range("sTimeType_summary").Value = Range("sTimeType_details").Value

'Refresh the table
Call fn_execute(Range("sTimeType_details").Value, Range("dtStart_details").Value, Range("dtEnd_details").Value)

ex:
    On Error Resume Next
    Exit Sub

eh:
    MsgBox "An error occured: " & Err.Number & ", " & Err.Description
    Resume ex


End Sub

Public Function fn_execute(sTimeType As String, dtStart As Date, dtEnd As Date)

'Execute the SQL Server Stored Proc xrpt_fuel_burn with parameters entered by the user
'dates_by - UTC or Local, used in the WHERE clause to filter
'@dtStart and @dtEnd - user-defined dates
'@run_by - System user, logged to xrpt_log
'@version - A number used to track the 'version' of the SP, and force users to use only the related Excel spreadsheet.

''05-22-14  jim.horn  Original

Application.Cursor = xlWait
Application.DisplayAlerts = False

Dim cn As WorkbookConnection
Dim ocn As OLEDBConnection

Set cn = ThisWorkbook.Connections("xrpt_fuel_burn")
Set ocn = cn.OLEDBConnection

Dim sCommandText As String
sCommandText = "xrpt_fuel_burn "
sCommandText = sCommandText & "@dates_by='" & sTimeType & "', "
sCommandText = sCommandText & "@dtStart='" & dtStart & "', "
sCommandText = sCommandText & "@dtEnd='" & dtEnd & "', "
sCommandText = sCommandText & "@run_by='" & Application.UserName & "', "
sCommandText = sCommandText & "@version=1.3"

With ocn
    .Connection = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=the_server;Data Source=wahoo"
    .CommandText = sCommandText
    .BackgroundQuery = False
    .Refresh
End With

Application.Cursor = xlDefault

'ActiveWorkbook.RefreshAll

'Refresh all pivot tables
Dim sh As Worksheet, pt As PivotTable
For Each sh In ThisWorkbook.Worksheets
    For Each pt In sh.PivotTables
        pt.RefreshTable
        pt.Update
    Next
Next

ex:
    On Error Resume Next
    Application.DisplayAlerts = True
    Application.Cursor = xlDefault
    Set ocn = Nothing
    Set cn = Nothing
    Exit Function

eh:
    MsgBox "An error occured: " & Err.Number & ", " & Err.Description
    Resume ex

End Function

Open in new window

0
 
fjkilkenAuthor Commented:
thanks Jim, looks quite interesting indeed, let me have a look through that code today and advise progress asap
Fergal
0
 
fjkilkenAuthor Commented:
great  - thanks a lot Jim!
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  I have an article in the works that illustrates this, and I'll send you a message when it's published.
0
 
fjkilkenAuthor Commented:
excellent - looking forward to the article :-)
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
The article is finished...

Microsoft Excel & SQL Server:  Self service BI to give users the data they want
If you like what you see, please click the 'Yes' button next to 'Was this article helpful?', and provide some feedback.

Thanks in advance.
Jim
0
 
fjkilkenAuthor Commented:
Thanks for that Jim, a very concise and easy-to-follow overview.
My utility works in a very similar way to yours, and it is just perfect for our users who are comfortable using Excel. Before this solution, we were using SSRS and simply exporting the data to Excel to manipulate and aggregate etc.. the data, now with the data coming straight into Excel, that redundant/time-wasting and non-value step is eliminated.
0
All Courses

From novice to tech pro — start learning today.