使用VBA和数据验证框从SQL表向Excel导入数据异常排查

Let's walk through the most likely issues causing your code to return no data (even without throwing errors) and how to fix them:

1. Fix SQL String Syntax (Missing Spaces) #

The first critical issue is missing spaces in your SQL string concatenation, which creates invalid SQL syntax. Look at how you're joining the [CUSTOMER] column and FROM clause:

sSQL = sSQL & "[MGMT_APPROVED],[AUTHORIZATION_Y_N],[CUSTOMER]"
sSQL = sSQL & "FROM [dbo].[vwRG_Schedule]"

This results in [CUSTOMER]FROM [dbo].[vwRG_Schedule] in your final query—SQL Server can't parse this correctly. Even if it doesn't throw an explicit error, it might return an empty recordset.

Fix this by adding spaces at the end of each line (before concatenating the next part):

sSQL = "SELECT [ORDER #],[SCENARIO],[LOCATION],[FORM],[STATUS],"
sSQL = sSQL & "CAST([Date Ordered] AS DATE),"
sSQL = sSQL & "[Design],[Cost],"
sSQL = sSQL & "CAST([Critical Obligation Date] AS DATE),"
sSQL = sSQL & "[MGMT_APPROVED],[AUTHORIZATION_Y_N],[CUSTOMER] " ' Space added here
sSQL = sSQL & "FROM [dbo].[vwRG_Schedule] " ' Space added here
sSQL = sSQL & "WHERE [SCENARIO] = '" & Scenario & "' "
sSQL = sSQL & "ORDER BY [ORDER #]"

2. Verify the Scenario Variable is Properly Populated #

Your code uses a Scenario variable, but it's not clear where it's being assigned. If this variable is empty, or doesn't match any values in your SQL table, you'll get no results.

• First, declare and assign the variable explicitly (e.g., if it's coming from a data validation cell):

  Dim Scenario As String
  ' Replace with the actual cell that holds your user's selection
  Scenario = ThisWorkbook.Worksheets("YourSheet").Range("A1").Value
  

• If Scenario might contain single quotes (e.g., Scenario O'Neil), the SQL query will break. Escape single quotes by replacing them with two single quotes:

  Scenario = Replace(Scenario, "'", "''")
  

3. Test the SQL Query Directly in SQL Server #

To rule out data-related issues, print the full SQL string in VBA and run it in SQL Server Management Studio (SSMS):
Add this line right before conn.Execute(sSQL):

Debug.Print sSQL ' Open the Immediate Window (Ctrl+G in VBA Editor) to see the query

Copy that query into SSMS, replace 'YourScenarioValue' with a known valid value from your vwRG_Schedule view, and execute it.

• If SSMS returns no data: The problem is with your data (no matching records for that scenario), not your code.
• If SSMS returns data: The issue is in how VBA is handling the Scenario variable or query execution.

4. Check for Case Sensitivity or Hidden Spaces #

• SQL Server collations can be case-sensitive. If your SCENARIO column uses a case-sensitive collation, scenario1 won't match Scenario1. Use LOWER() to standardize:

  WHERE LOWER([SCENARIO]) = LOWER('" & Scenario & "')
  

• Trailing/leading spaces in either the variable or the SQL column can prevent matches. Trim both sides:

  ' Trim the variable in VBA
  Scenario = Trim(Scenario)
  ' Or trim in SQL
  WHERE LTRIM(RTRIM([SCENARIO])) = LTRIM(RTRIM('" & Scenario & "'))
  

5. Confirm Connection Details #

Double-check your connection string to ensure you're connecting to the correct server and database:

sConnString = "Provider=SQLOLEDB;Data Source=SQL\SQLEXPRESS;Initial Catalog=Sample;Trusted_Connection=yes;"

• Data Source: Make sure this matches your SQL Server instance name (e.g., maybe it's just SQLEXPRESS instead of SQL\SQLEXPRESS).
• Initial Catalog: Verify Sample is the correct database where vwRG_Schedule exists.

Full Modified Code Example #

Here's your code with the key fixes applied:

Sub PullRGData()
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String
    Dim sSQL As String
    Dim ceRg As Worksheet
    Dim Scenario As String ' Explicitly declare variable
    
    Set ceRg = ThisWorkbook.Worksheets("RG Schedule")
    
    If ceRg.FilterMode Then ceRg.ShowAllData
    ceRg.Range("A3").CurrentRegion.ClearContents ' Clear all old data, not just A3
    
    ' Assign Scenario from your data validation cell (update the range!)
    Scenario = ceRg.Range("B1").Value ' Example cell - replace with your actual cell
    Scenario = Replace(Scenario, "'", "''") ' Escape single quotes
    
    ' Create connection string
    sConnString = "Provider=SQLOLEDB;Data Source=SQL\SQLEXPRESS;Initial Catalog=Sample;Trusted_Connection=yes;"
    
    ' Create objects
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    ' Open connection and build query
    conn.Open sConnString
    sSQL = "SELECT [ORDER #],[SCENARIO],[LOCATION],[FORM],[STATUS],"
    sSQL = sSQL & "CAST([Date Ordered] AS DATE),"
    sSQL = sSQL & "[Design],[Cost],"
    sSQL = sSQL & "CAST([Critical Obligation Date] AS DATE),"
    sSQL = sSQL & "[MGMT_APPROVED],[AUTHORIZATION_Y_N],[CUSTOMER] "
    sSQL = sSQL & "FROM [dbo].[vwRG_Schedule] "
    sSQL = sSQL & "WHERE LTRIM(RTRIM([SCENARIO])) = LTRIM(RTRIM('" & Scenario & "')) "
    sSQL = sSQL & "ORDER BY [ORDER #]"
    
    Debug.Print sSQL ' For debugging
    
    Set rs = conn.Execute(sSQL)
    
    ' Check for data
    If Not rs.EOF Then
        ceRg.Range("A3").CopyFromRecordset rs
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If
    
    ' Cleanup
    rs.Close
    If conn.State = adStateOpen Then conn.Close
    Set conn = Nothing
    Set rs = Nothing
End Sub

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