Transfer criteria from Excel to SQL - possible or not?

Posted on 2014-08-28
Last Modified: 2014-09-15
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?
Question by:agwalsh
    LVL 2

    Assisted Solution

    by:Priya Sudharsan
    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.

    Author Comment

    Great stuff...can you give me an actual example of that?
    LVL 65

    Accepted Solution

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

    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.

    Author Closing Comment

    Great to know this can be done. Code much appreciated as with the forthcoming article.
    LVL 65

    Expert Comment

    by:Jim Horn
    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.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Suggested Solutions

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now