[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

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?
0
agwalsh
Asked:
agwalsh
  • 2
  • 2
2 Solutions
 
Priya SudharsanCommented:
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
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now