Excel 2013中VBA执行SQL查询报错及连接字符串格式问题

Let's break down your two connection issues and get your SQL queries running smoothly:

1. Resolving "Error 2147467259 - Method 'Execute' of object '_Connection' failed" + Automation Error #

This error typically stems from connection string formatting issues, invalid file paths, or permission conflicts with your Excel file. Let's adjust your code to address these common pitfalls:

First, make sure your report_name variable holds a full, valid file path (like C:\Reports\MyData.xlsx). Then, tweak the connection setup with these fixes:

Dim conn_obj As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
Dim report_name As String

' Set your full file path here
report_name = "C:\Path\To\Your\TargetExcelFile.xlsx"

On Error GoTo err_SQL
Set conn_obj = New ADODB.Connection

With conn_obj
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    ' Fix extended properties formatting and add IMEX to handle mixed data types
    .ConnectionString = "Data Source=" & report_name & ";" & _
                        "Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"
    .Mode = adModeReadWrite ' Use adModeRead if the file is locked as read-only
    .Open
End With

SQL = query ' Double-check your query uses Excel-compatible syntax (e.g., [Sheet1$] for sheet names)
Set rs = conn_obj.Execute(SQL)

' Add your recordset processing code here
' ...

err_SQL:
If Err.Number <> 0 Then
    MsgBox "Error: " & Err.Description & " (" & Err.Number & ")"
    ' Clean up connections to prevent locks
    If Not conn_obj Is Nothing And conn_obj.State = adStateOpen Then
        conn_obj.Close
    End If
    Set conn_obj = Nothing
    Set rs = Nothing
End If

Key improvements here:

• Added IMEX=1 to handle columns with mixed data types (prevents truncation or unexpected errors)
• Explicitly set the connection mode to handle read/write scenarios
• Added proper cleanup logic in the error handler to avoid leaving connections open
• Ensured the extended properties use correct quote escaping ("") for VBA

Also, verify:

• The Excel file isn't open in read-only mode
• You have full access permissions to the file's folder
• Your SQL query uses valid Excel syntax (sheet names must include $ and be wrapped in brackets, e.g., [SalesData$])

2. Fixing "Format of the initialization string does not conform to the OLE DB Specification" #

The core issue here is your connection string was configured for text files, not Excel files. You switched to Microsoft.Jet.OLEDB.4.0 but used Extended Properties='text;...' which is incorrect for Excel. Here's the corrected code:

Dim sSQLString As String
Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset
Dim DBPath As String, sconnect As String

DBPath = report_name ' Ensure this is a full Excel file path (e.g., C:\Data.xls)

' Corrected connection string for Excel using Jet 4.0 (for .xls files)
sconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath & ";" & _
           "Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"";"

' OR use ACE for .xlsx files (recommended for Excel 2013)
' sconnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBPath & ";" & _
'            "Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"

On Error GoTo err_Conn
Conn.Open sconnect

sSQLString = query ' Validate your query syntax before execution
mrs.Open sSQLString, Conn

' Add your recordset processing code here
' ...

err_Conn:
If Err.Number <> 0 Then
    MsgBox "Error: " & Err.Description & " (" & Err.Number & ")"
    ' Clean up connections
    If Not Conn Is Nothing And Conn.State = adStateOpen Then
        Conn.Close
    End If
    Set Conn = Nothing
    Set mrs = Nothing
End If

Key fixes here:

• Changed Extended Properties to use Excel 8.0 (for Jet 4.0 and .xls files) or Excel 12.0 Xml (for ACE and .xlsx files) instead of text
• Fixed quote formatting (use "" instead of ' for extended properties in VBA)
• Added a recommendation to use ACE for newer Excel formats (better compatibility with Excel 2013)

Final Quick Tips #

• Always use full file paths instead of relative paths to avoid ambiguity
• Test your SQL query in Excel's "Data > From Other Sources > From Microsoft Query" first to confirm it's valid
• If you're using ACE.OLEDB.12.0, ensure the Microsoft Access Database Engine 2010 Redistributable is installed (it's required for ACE drivers on some systems)

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