求助：如何正确使用PivotTableWizard方法的connection参数？

Hey there! Let’s break down how to properly use the connection parameter in Excel’s PivotTableWizard() method—this is a common sticking point, so I’m glad you asked. The key here is matching the parameter’s value to where your pivot table data is coming from: internal Excel ranges or external data sources.

1. Using connection for Internal Excel Data #

When your pivot source is a range in the current workbook or another open Excel file, the connection parameter accepts a valid cell reference string (in A1 or R1C1 format) or a named range.

Note: For internal data, you’ll usually pair this with SourceType:=xlDatabase. Often, you can just use the SourceData parameter instead, but connection is useful for dynamic references or named ranges.

Example with a Cell Range: #

ActiveSheet.PivotTableWizard _
    SourceType:=xlDatabase, _
    Connection:="=Sheet1!$A$1:$D$100", ' Explicit reference to the data range
    TableDestination:=Range("F1")

Example with a Named Range: #

If you’ve defined a named range called SalesData covering your source cells:

ActiveSheet.PivotTableWizard _
    SourceType:=xlDatabase, _
    Connection:="=SalesData", ' Use the named range directly
    TableDestination:=Range("F1")

2. Using connection for External Data Sources #

For external data (like SQL Server, Access, or a CSV file), connection needs either a full ODBC/OLEDB connection string or the name of an existing workbook connection. You’ll also set SourceType:=xlExternal here.

Example with a Connection String (SQL Server): #

Dim sqlConnStr As String
' Windows Authentication connection string
sqlConnStr = "ODBC;DRIVER=SQL Server;SERVER=MyServerName;DATABASE=MySalesDB;Trusted_Connection=Yes;"

ActiveSheet.PivotTableWizard _
    SourceType:=xlExternal, _
    Connection:=sqlConnStr, _
    Sql:="SELECT Region, SaleDate, Amount FROM SalesRecords", ' Your SQL query
    TableDestination:=Range("A1")

Example with an Existing Workbook Connection: #

If you’ve already set up a connection in Excel (via the Data tab > Connections), you can reference its name directly:

ActiveSheet.PivotTableWizard _
    SourceType:=xlExternal, _
    Connection:="MySavedSQLConnection", ' Name of your existing connection
    TableDestination:=Range("A1")

Common Pitfalls to Avoid #

• Mismatched SourceType: Never use xlDatabase with an external connection string, or xlExternal with an internal range reference—this will throw errors.
• Invalid Reference Strings: For internal data, double-check your range syntax (include the sheet name if referencing another sheet, use absolute references where needed).
• Permission Issues: For external connections, ensure your connection string includes valid credentials (or uses trusted authentication) and that you have access to the data source.

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