Results 1 to 5 of 5
  1. #1
    Steve Nance
    Guest

    Excel Pivot Table (2K SR1)

    I am exporting an Excel file, opening it up, formatting and then building a Pivot Table from the Excel data. But, I can't get the Pivot table code to work. Can anyone point me to some code that works?

    I know this is not new, but I searched and couldn't find anything in the archives.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Pivot Table (2K SR1)

    Sorry, but are you doing this in Access or in Excel? Why don't you post the code that doesn't work so someone can tweak it for you.
    Charlotte

  3. #3
    Steve Nance
    Guest

    Re: Excel Pivot Table (2K SR1)

    Good Idea. Code works until it gets to the 1st pivot call (***) and says 'Invalid procedure call or argument'.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel Pivot Table (2K SR1)

    Hi,
    I think you need to change that line to:
    With xl.ActiveWorkbook.PivotCaches.Add(SourceType:=xlDa tabase, SourceData="VendorCodes!R1C1:R294C6")
    .CreatePivotTable TableDestination:="", TableName:="Pivot"
    End With
    as the SourceData property is read-only. You have to specify it as an argument of the Add method. (Note: I'm assuing that VendorCodes is the name of the sheet as well as the workbook? Otherwise I suspect that will cause an error too.)
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Steve Nance
    Guest

    Re: Excel Pivot Table (2K SR1)

    Rory,
    That did the trick.

    Thanks,
    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •