Need VBA for Excel parameter value assignation

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!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft 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)

    On Error Resume Next
    Exit Sub

    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
End With

Application.Cursor = xlDefault


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

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

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

End Function

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fjkilkenAuthor Commented:
thanks Jim, looks quite interesting indeed, let me have a look through that code today and advise progress asap
fjkilkenAuthor Commented:
great  - thanks a lot Jim!
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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.
fjkilkenAuthor Commented:
excellent - looking forward to the article :-)
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.
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.