Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does a Pivot External Source to SQL Server stay connected?
I have a routine that passes different parameters to a database in a loop,
and for each parameter, it creates a pivot table on a separate tab. I was previously connecting to an MS Access database, but I converted the Pivot Table's VBA so that it could read from SQL Server... With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal) .Connection = _ "ODBC;DRIVER=SQL Server;SERVER=" & strServerName & ";UID=" & strUID & ";PWD=" & strPWD & ";MaxBufferSize=2048;PageTimeout=10;" .CommandType = xlCmdSql .CommandText = Array(strSQL) .CreatePivotTable TableDestination:= _ "'[" & ThisWorkbook.Name & "]" & PivotLocation, TableName:=strPivotName, _ DefaultVersion:=xlPivotTableVersion10 End With But now, it only succeeds in the first passed parameter of the loop. i.e. the next parameter returns an empty pivot table. The reason I haven't provided the code for the loop, is because I only changed the above code. What is it about SQL Server that would change being able to loop this vs being able to loop this in MS Access? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Pivot Table connected to an Access Database Data Source | Excel Discussion (Misc queries) | |||
How do I refresh Pivot Table from an external source ? | Excel Worksheet Functions | |||
excel is connected to sharepoint server and says too many members | Excel Discussion (Misc queries) | |||
Pivot and changing External data source | Excel Discussion (Misc queries) | |||
group pivot table from external source | Excel Programming |