Pivot Table Connection

S

Steven Taylor

I am trying to change the data source of my pivot table.
The code that I normally use is:

ActiveCell.PivotTable.PivotCache.Connection
= "ODBC;DSN=GENERAL LEDGER;"

However I keep getting the follwoing message:

Run-time error '1004':
Application-defined or object-defined error

Does anybody have any suggestions as to my this might be
happening? TIA.

Steven
 
D

Dick Kusleika

Steven

You will get that error if the PivotTable which contains the activecell is
not an External Data pivot table. You may get that error under other
circumstances, but that was the only way that I could reproduce it.
 
S

Steven Taylor

It is definately an external data pivot table because when
I type:

?ActiveCell.PivotTable.PivotCache.Connection

in the debug window, VB returns the current connection.
 
D

Dick Kusleika

Steven

Where is the code, standard or class module? Are you running it from a
commandbutton or some other activex control? What is the context around
that line?

Try changing it to call out the cell directly and see what that does.

Range("C10").PivotTable....
 
S

Steven Taylor

Yes. I tried that but that didn't work. In the end I
decided to recreate the pivot table using my new data
connection, but all the values were returned as 0. This
was incorrect. I wondered if there may be some other
problem with the workbook so I saved the pivot's query and
reopened the query in another workbook and it has worked
fine! Strange? Thanks anyway!
 
D

Dick Kusleika

Steven

That is strange. Must have been corrupted. I'm glad you got it sorted.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top