Excel 2016：如何将表格日期参数传入SQL查询语句

Got it, let's break down how to replace hardcoded date values in your SQL query with parameters pulled directly from an Excel spreadsheet—this will make way easier for end users who don’t want to dig into SQL code every time they need to update dates.

First, swap out any hardcoded date values in your query with parameter placeholders (the syntax varies slightly by database, but we’ll use common examples). For instance, if your original query had WHERE BNH_TIER1_MAINT_DT >= '2024-01-01', replace that with a parameter like @StartDate or ?.

Here’s a cleaned-up version of your query with parameter placeholders (adjust the placeholder syntax to match your database: @ for SQL Server, : for Oracle, ? for MySQL/Access):

SELECT * FROM (
    SELECT 
        TEMP.BNH_PROCESSING_GRP,
        TEMP.BNH_SUPPLIER_NO,
        TEMP.BNH_PROCESSING_FLAG,
        TEMP.BNH_CREATE_DT,
        TEMP.BNH_TIER1_USER_ID,
        TEMP.BNH_TIER1_NAME,
        TEMP.BNH_TIER1_MAINT_DT,
        TEMP.BNH_TIER2_USER_ID,
        TEMP.BNH_TIER2_NAME,
        TEMP.BNH_TIER2_MAINT_DT,
        TEMP.BNH_TIER3_USER_ID,
        TEMP.BNH_TIER3_NAME,
        -- Replace any hardcoded dates in subqueries too
        (SELECT MAX(ABA_bank_H.BNH_TIER3_MAINT_DT) 
         FROM ABA_bank_H 
         WHERE ABA_bank_H.BNH_SUPPLIER_NO = TEMP.BNH_SUPPLIER_NO
           AND ABA_bank_H.BNH_TIER3_MAINT_DT BETWEEN @StartDate AND @EndDate) AS LAST_TIER3_UPDATE
    FROM YourBaseTable TEMP
    -- Use parameters in your main filter logic
    WHERE TEMP.BNH_CREATE_DT BETWEEN @StartDate AND @EndDate
) AS FinalResult

We’ll cover two methods: one for non-technical users (no code) and one for more flexible automation.

2.1 Excel Power Query (No Code Required) #

This is perfect for end users who just want to input dates and refresh data:

• Step 1: Set up parameter cells in Excel. Pick two cells (e.g., Sheet1!A1 for start date, Sheet1!A2 for end date) and format them as dates. Name these cells for clarity: go to Formulas > Define Name, name one StartDateParam and link it to Sheet1!A1, repeat for EndDateParam and Sheet1!A2.
• Step 2: Connect to your database. Go to Data > Get Data > From Database > [Your Database Type] (e.g., SQL Server). Enter your database connection details.
• Step 3: Use your parameterized SQL. In the navigator, click Advanced Options, paste the parameterized query you wrote earlier. When prompted, select "Get value from Excel" for each parameter, and pick the named cells you set up.
• Step 4: Load and refresh. Load the data into Excel. Now users just need to update the date cells and click Data > Refresh All to pull the latest results.

2.2 VBA for Automated/Controlled Workflows #

If you need auto-refresh or custom logic, use VBA to pull Excel dates into your SQL query:

• Step 1: Set up date input cells (same as above: Sheet1!A1 = start date, Sheet1!A2 = end date).
• Step 2: Open the VBA editor (press Alt+F11), insert a new module, and paste this code (adjust the connection string and SQL to match your database):

Sub RefreshSQLWithExcelDates()
    Dim conn As Object, rs As Object, cmd As Object
    Dim sqlStr As String
    Dim startDate As Date, endDate As Date
    
    ' Pull dates from Excel cells
    startDate = ThisWorkbook.Sheets("Sheet1").Range("A1").Value
    endDate = ThisWorkbook.Sheets("Sheet1").Range("A2").Value
    
    ' Build parameterized SQL (use ? for ADO parameter binding)
    sqlStr = "SELECT * FROM (" & _
             "SELECT TEMP.BNH_PROCESSING_GRP, TEMP.BNH_SUPPLIER_NO, TEMP.BNH_PROCESSING_FLAG," & _
             "TEMP.BNH_CREATE_DT, TEMP.BNH_TIER1_USER_ID, TEMP.BNH_TIER1_NAME," & _
             "TEMP.BNH_TIER1_MAINT_DT, TEMP.BNH_TIER2_USER_ID, TEMP.BNH_TIER2_NAME," & _
             "TEMP.BNH_TIER2_MAINT_DT, TEMP.BNH_TIER3_USER_ID, TEMP.BNH_TIER3_NAME," & _
             "(SELECT MAX(ABA_bank_H.BNH_TIER3_MAINT_DT) FROM ABA_bank_H " & _
             "WHERE ABA_bank_H.BNH_SUPPLIER_NO = TEMP.BNH_SUPPLIER_NO " & _
             "AND ABA_bank_H.BNH_TIER3_MAINT_DT BETWEEN ? AND ?) AS LAST_TIER3_UPDATE" & _
             " FROM YourBaseTable TEMP " & _
             "WHERE TEMP.BNH_CREATE_DT BETWEEN ? AND ?) AS FinalResult"
    
    ' Set up database connection (adjust for your database type)
    Set conn = CreateObject("ADODB.Connection")
    conn.ConnectionString = "Provider=SQLOLEDB;Data Source=YourServerName;Initial Catalog=YourDB;Integrated Security=SSPI;"
    conn.Open
    
    ' Bind parameters to avoid SQL injection
    Set cmd = CreateObject("ADODB.Command")
    cmd.ActiveConnection = conn
    cmd.CommandText = sqlStr
    ' Add parameters in the order they appear in the SQL
    cmd.Parameters.Append cmd.CreateParameter("SubQueryStart", 7, 1, , startDate) ' 7 = adDate type
    cmd.Parameters.Append cmd.CreateParameter("SubQueryEnd", 7, 1, , endDate)
    cmd.Parameters.Append cmd.CreateParameter("MainQueryStart", 7, 1, , startDate)
    cmd.Parameters.Append cmd.CreateParameter("MainQueryEnd", 7, 1, , endDate)
    
    ' Run query and write results to Excel
    Set rs = cmd.Execute
    ThisWorkbook.Sheets("Results").Range("A1").CopyFromRecordset rs
    
    ' Clean up resources
    rs.Close: conn.Close
    Set rs = Nothing: Set conn = Nothing: Set cmd = Nothing
    
    MsgBox "Data refreshed with your Excel dates!", vbInformation
End Sub

• Step 3: Add a button for users. Go to Developer > Insert > Button, draw it on your sheet, and link it to the RefreshSQLWithExcelDates macro. Now users just enter dates and click the button.

• Avoid SQL Injection: Always use parameterized queries (not string concatenation) to pass dates—this keeps your database safe and avoids date format errors.
• Date Formatting: Make sure Excel’s date cells are formatted as actual dates (not text) so the database can read them correctly.
• Database Compatibility: Adjust parameter syntax to match your database (e.g., @Param for SQL Server, :Param for Oracle).

内容的提问来源于stack exchange，提问作者Ben.