Pivot Table Connection
 

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

Pivot Table Connection
 

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.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Steven Taylor" wrote in message
...
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

Pivot Table Connection
 

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.

-----Original Message-----
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.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Steven Taylor"
wrote in message
...
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


.

Pivot Table Connection
 

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....


--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Steven Taylor" wrote in message
...
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.

-----Original Message-----
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.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Steven Taylor"
wrote in message
...
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


.

Pivot Table Connection
 

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!


-----Original Message-----
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....


--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Steven Taylor"
wrote in message
...
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.

-----Original Message-----
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.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Steven Taylor"
wrote in message
...
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


.



.

Pivot Table Connection
 

Steven

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

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Steven Taylor" wrote in message
...
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!


-----Original Message-----
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....


--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Steven Taylor"
wrote in message
...
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.

-----Original Message-----
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.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Steven Taylor"
wrote in message
...
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


.



.