Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. Property let procedure not defined error when accessing PivotTable SourceData Property

Property let procedure not defined error when accessing PivotTable SourceData Property

Author: Jan Karel Pieterse MVP

View products that this article applies to.

Symptoms

The following code will show the symptom when run when a worksheet with at least one Pivot table is active:
�
Sub Demonstrate()
��� Dim oPivot As Object 'Note this line!!
��� Dim oSh As Worksheet
��� Set oSh = ActiveSheet
��� Set oPivot = oSh.PivotTables(1)
��� MsgBox oPivot.SourceData(1)
��� MsgBox oPivot.SourceData(2)'etcetera
End Sub
�
The code below does not show the symptom:
�
Sub DemonstrateOK()
��� Dim oPivot As PivotTable
��� Dim oSh As Worksheet
��� Set oSh = ActiveSheet
��� Set oPivot = oSh.PivotTables(1)
��� MsgBox oPivot.SourceData(1)
��� MsgBox oPivot.SourceData(2)'etcetera
End Sub
�
The basis of the problem lies with the declaration, when declared as an Object, the oPivot.SourceData Property cannot be accessed.
�
The problem also applies to Excel 97 and 2000.

↑ Back to the top

Cause

Microsoft has confirmed this is a bug.

↑ Back to the top

Resolution

A workaround (if one needs to declare oPivot as an Object rather than as a PivotTable object) is to use the Connection and the CommandText property instead of the SourceData property:
�
Sub DemonstrateWorkAround()
��� Dim oPivot As Object 'Note this line!!
��� Dim oSh As Worksheet
��� Set oSh = ActiveSheet
��� Set oPivot = oSh.PivotTables(1)
��� MsgBox oPivot.PivotCache.Connection
�� �MsgBox oPivot.PivotCache.CommandText 'etcetera
End Sub

↑ Back to the top

Properties

COMMUNITY SOLUTIONS CONTENT DISCLAIMER
MICROSOFT CORPORATION AND/OR ITS RESPECTIVE SUPPLIERS MAKE NO REPRESENTATIONS ABOUT THE SUITABILITY, RELIABILITY, OR ACCURACY OF THE INFORMATION AND RELATED GRAPHICS CONTAINED HEREIN. ALL SUCH INFORMATION AND RELATED GRAPHICS ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT AND/OR ITS RESPECTIVE SUPPLIERS HEREBY DISCLAIM ALL WARRANTIES AND CONDITIONS WITH REGARD TO THIS INFORMATION AND RELATED GRAPHICS, INCLUDING ALL IMPLIED WARRANTIES AND CONDITIONS OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, WORKMANLIKE EFFORT, TITLE AND NON-INFRINGEMENT. YOU SPECIFICALLY AGREE THAT IN NO EVENT SHALL MICROSOFT AND/OR ITS SUPPLIERS BE LIABLE FOR ANY DIRECT, INDIRECT, PUNITIVE, INCIDENTAL, SPECIAL, CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER INCLUDING, WITHOUT LIMITATION, DAMAGES FOR LOSS OF USE, DATA OR PROFITS, ARISING OUT OF OR IN ANY WAY CONNECTED WITH THE USE OF OR INABILITY TO USE THE INFORMATION AND RELATED GRAPHICS CONTAINED HEREIN, WHETHER BASED ON CONTRACT, TORT, NEGLIGENCE, STRICT LIABILITY OR OTHERWISE, EVEN IF MICROSOFT OR ANY OF ITS SUPPLIERS HAS BEEN ADVISED OF THE POSSIBILITY OF DAMAGES.

↑ Back to the top

Community solutions content disclaimer

Microsoft corporation and/or its respective suppliers make no representations about the suitability, reliability, or accuracy of the information and related graphics contained herein. All such information and related graphics are provided "as is" without warranty of any kind. Microsoft and/or its respective suppliers hereby disclaim all warranties and conditions with regard to this information and related graphics, including all implied warranties and conditions of merchantability, fitness for a particular purpose, workmanlike effort, title and non-infringement. You specifically agree that in no event shall Microsoft and/or its suppliers be liable for any direct, indirect, punitive, incidental, special, consequential damages or any damages whatsoever including, without limitation, damages for loss of use, data or profits, arising out of or in any way connected with the use of or inability to use the information and related graphics contained herein, whether based on contract, tort, negligence, strict liability or otherwise, even if Microsoft or any of its suppliers has been advised of the possibility of damages.

↑ Back to the top

Keywords: KB555165, kbhowto, kbpubmvp, kbpubtypecca

↑ Back to the top

Article Info
Article ID : 555165
Revision : 1
Created on : 7/27/2004
Published on : 7/27/2004
Exists online : False
Views : 417