| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Affect a new connection to a pivot table | Excel Discussion (Misc queries) | |||
| Connection of Excel 07 pivot table to Access Query makes DB read o | Excel Discussion (Misc queries) | |||
| Pivot Table loses Data Connection | Excel Discussion (Misc queries) | |||
| Pivot Table Connection String - How to hide it? | Excel Discussion (Misc queries) | |||
| Copy pivot table and get rid of connection | Excel Discussion (Misc queries) | |||
![Steven Taylor[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
