VBA函数开发需求：将动态列数据转换为SQL IN子句文本字符串

Hey there, let's tweak your existing function so it can handle a whole column (or any range) of material IDs instead of just a single cell. Your original code works for comma-separated values in one cell, but we'll adjust it to iterate over multiple cells and build the correct SQL IN clause you need.

Modified VBA Function #

Function CreateSQLAndQry(field_input As String, input_range As Range) As String
    Dim cell As Range
    Dim cleaned_id As String
    Dim id_list As Collection
    Set id_list = New Collection
    
    ' Collect all non-empty, cleaned IDs from the target range
    For Each cell In input_range
        cleaned_id = Replace(Trim(cell.Value), " ", "") ' Trim + remove internal spaces
        If cleaned_id <> "" Then
            id_list.Add "'" & cleaned_id & "'" ' Wrap ID in single quotes
        End If
    Next cell
    
    Dim new_string As String
    new_string = ""
    
    ' Build reversed string (matches your requested output order: test2 first, test1 second)
    For i = id_list.Count To 1 Step -1
        new_string = new_string & id_list(i) & ","
    Next i
    
    ' Finalize the SQL clause
    Dim new_qry As String
    new_qry = ""
    If id_list.Count > 0 Then
        new_string = Left(new_string, Len(new_string) - 1) ' Remove trailing comma
        new_qry = " AND " & field_input & " IN (" & new_string & ") "
    End If
    
    CreateSQLAndQry = new_qry
End Function

Key Changes Explained #

• Range Input Support: Swapped the input_string parameter for input_range As Range so you can pass an entire column (like A:A) or a specific range of cells directly.
• Non-Empty Cell Filter: Skips blank cells to avoid invalid empty quotes in your SQL.
• Order Preservation: Keeps your original reversed output logic (so cells with test1 then test2 produce ('test2','test1') as you requested).
• Robust Cleaning: Added Trim() to handle leading/trailing spaces in cells, plus your original space-removal logic for internal spaces.
• Edge Case Handling: Returns an empty string if there are no valid IDs, so you don't end up with a broken IN () clause.

How to Use It #

In VBA Code #

If your material IDs are in column A and your target database field is MaterialID, call it like this:

Dim sql_filter As String
sql_filter = CreateSQLAndQry("MaterialID", Range("A:A"))
' Insert sql_filter into your full ODBC query string

Directly in Excel Cells #

Test it by entering this formula in a cell (assuming IDs are in A1:A2):
=CreateSQLAndQry("MaterialID", A1:A2)
With A1=test1 and A2=test2, this returns:
AND MaterialID IN ('test2', 'test1')

Optional: Revert to Normal Order #

If you don't want the reversed ID order, just adjust the loop to iterate forward:

For i = 1 To id_list.Count
    new_string = new_string & id_list(i) & ","
Next i

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