A few folks have asked for an example of creating an Excel Pivot Table using External data.  Here's a simple example of creating a Pivot Table using the Microsoft Northwind Access database.

Since however, there are so many data source possibilities that you may want to gather from, my best advice to you would be to "record a macro" while performing an actual Pivot Table creation while in Excel.  Just step through the wizard of creating a Pivot Table using an External Data source until you have completed the task, then stop the macro recording and copy the recorded macro code into your vbscript file.  You'll need to make some modifications to the recorded macro. If you're interested in the specifics, check out the Excelerate your Scripts series for a detailed break down of what you need to add, remove and modify.

Here's the example:

Const xlExternal = 2
Const xlCmdSql = 2
Const xlPivotTableVersion10 = 1
Const xlDataField = 4

Set XL = CreateObject("Excel.Application")
XL.Workbooks.Add

With XL.ActiveWorkbook.PivotCaches.Add(xlExternal)
 .Connection = _
 "ODBC;DSN=MS Access Database;DBQ=C:\Temp\Northwind.mdb;" & _
 "DefaultDir=C:\Temp;DriverId=25;FIL=MS Access;" & _
 "MaxBufferSize=2048;PageTimeout=5;"
 .CommandType = xlCmdSql
 .CommandText = Array( _
 "SELECT `Alphabetical List of Products`.CategoryName" & _
 Chr(13) & "" & Chr(10) & "FROM " & _
 "`C:\Temp\Northwind`.`Alphabetical List of Products` " & _
 "`Alphabetical List of Products`" & Chr(13) & "" & Chr(10) & _
 "ORDER BY `Alphabetical List of Products`.CategoryName")
 .CreatePivotTable "Sheet1!R1C1",  _
 "PivotTable1", xlPivotTableVersion10
End With
XL.ActiveSheet.PivotTables("PivotTable1").AddFields "CategoryName"
XL.ActiveSheet.PivotTables("PivotTable1").PivotFields _
("CategoryName").Orientation = xlDataField
XL.ActiveWorkbook.ShowPivotTableFieldList = False
XL.Application.CommandBars("PivotTable").Visible = False
XL.Visible = TRUE