Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programmatically setting pivot sourcedata
Hey,
I am trying to set the pivot SourceData property to a connection string. When I look at the pt.SourceData I see 2 values. Thre is a pt.SourceData(1) and pt.SourceData(2). The first one is the connection string which in this case does not have the password specified. pt.SourceData(1) = "DSN=SDW_PRD_ALLVM;UID=USERID;;DATABASE=SDW_PRD_AL LVM;" The second one is the SQL: pt.SourceData(1) = SELECT *FROM sdw_prd_qmtbls.TBL_SO_Non_Sls_Rpt My goal is to set the connection string (the first one) equal to this: DSN=SDW_PRD_ALLVM;UID=USERID;PWD=PASSWORD;DATABASE =SDW_PRD_ALLVM; I've tried setting it using the following 2 methods and each have failed: Method 1: Set pt.SourceData(1) = TargetConString 'Gives me error 424 Object Required Method 2: pt.SourceData(1) = TargetConString 'Doesnt give me an error but also never sets it As an alternative I've tried setting the cache: If I look at pt.PivotCache.Connection I see its equal to ODBC;DSN=SDW_PRD_ALLVM;UID=rth218;;DATABASE=SDW_PR D_ALLVM; However when I try to set that connection using the set keyword I get the same 424 Object required error. When I remove the set word and run this: pt.PivotCache.Connection = "" I get Error 1004 Application-defined or object-defined error. Can anyone tell me how to set the SourceData for this pivot table?" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Modify Pivot Table SourceData range | Excel Programming | |||
SourceData in pivot table | Excel Programming | |||
Reling Pivot sourcedata to network drive | Excel Programming | |||
Setting up a SourceData:= in VB | Excel Programming | |||
Dynamic Array as pivot table SourceData..? | Excel Programming |