Transfer criteria from Excel to SQL - possible or not?

hi
I've been asked a question..basically is it possible to set up a set of criteria in Excel e.g. customer IDS, date ranges etc and use that as the basis for a query in SQL?
LVL 1
agwalshAsked:
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.

Priya SudharsanProgrammer AnalystCommented:
Yes it is possible. You can form the criteria as a String, append it to SQL query in the macro and execute in the server.
0
agwalshAuthor Commented:
Great stuff...can you give me an actual example of that?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Yes.  Here's a copy-paste of an Excel VBA project I did a couple of months ago connecting to SQL Server Stored Procedure, using named ranges as 'criteria', and VBA code executes the SP and passes that 'criteria' as parameters.
Sub btn_detail_go_Click()

Application.Cursor = xlWait

'User hit Go! in the Summary tab.  

'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

I've got an article in the works that spells this out in greater detail, and I'll send you a message when that's done.
0

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
agwalshAuthor Commented:
Great to know this can be done. Code much appreciated as with the forthcoming 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
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 Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.