PivotTable and connection string

L

lc

With Excel 2003, I'm trying to replace connection string and make query
dynamic but am getting "strange" errors. This is what I have:

Private Sub refreshData(dFrom As Date, dTo As Date)
Dim pt As PivotTable
Dim pc As PivotCache

Set pt = Sheet1.PivotTables(1)
Set pc = ActiveWorkbook.PivotCaches(pt.CacheIndex)

pc.Connection = "SQLOLEDB.1;User ID=sa;Initial Catalog=QuantumPlan;Data
Source=myServer"
pc.CommandText = "select .... '"

pt.PivotCache.Refresh
End Sub

I'm getting 1004 error on pc.Connection line. Anyone has any idea why and
how to get around it?

Thanks a lot.

lc
 
Top