+ Reply to Thread
Results 1 to 15 of 15

Create Pivot Table: Cannot Open Pivot Table Source File

  1. 01-04-2010, 11:39 AM #1
    jomili
    jomili is offline
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    4,062

    Create Pivot Table: Cannot Open Pivot Table Source File

    I'm trying to write a macro that will create a pivot table, and am getting an Error code 1004: Cannot Open Pivot Table Source File "Sheetname". My code is below. I've tried to note what each section does, and it all seems to work well except for the Pivot Table creation. Please advise. 
    Please Login or Register  to view this content.
    Last edited by jomili; 01-06-2010 at 09:13 AM.
    Reply With QuoteRegister To Reply
  2. 01-04-2010, 11:59 AM #2
    romperstomper
    romperstomper is offline
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,996

    Re: Create Pivot Table: Cannot Open Pivot Table Source File

    Is that the exact error message? Which line does it actually occur on?
    Everyone who confuses correlation and causation ends up dead.
    Reply With QuoteRegister To Reply
  3. 01-04-2010, 12:04 PM #3
    jomili
    jomili is offline
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    4,062

    Re: Create Pivot Table: Cannot Open Pivot Table Source File

    The error occurs here: 
    Please Login or Register  to view this content.
    and says this
    Please Login or Register  to view this content.
    APRData DOES exist; I use it in previous macros in the same workbook.
    Reply With QuoteRegister To Reply
  4. 01-04-2010, 12:08 PM #4
    romperstomper
    romperstomper is offline
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,996

    Re: Create Pivot Table: Cannot Open Pivot Table Source File

    Is the sheet called 'APRData' or 'APR Data' with a space in the middle?
    Reply With QuoteRegister To Reply
  5. 01-04-2010, 12:10 PM #5
    jomili
    jomili is offline
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    4,062

    Re: Create Pivot Table: Cannot Open Pivot Table Source File

    APRData, no space, first 3 letters capitalized.
    Reply With QuoteRegister To Reply
  6. 01-04-2010, 12:18 PM #6
    romperstomper
    romperstomper is offline
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,996

    Re: Create Pivot Table: Cannot Open Pivot Table Source File

    Which workbook is the sheet in? You are referring to Activeworkbook in the code - it would be better to specify either SrcWkb or DestWkb.
    Reply With QuoteRegister To Reply
  7. 01-04-2010, 12:22 PM #7
    jomili
    jomili is offline
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    4,062

    Re: Create Pivot Table: Cannot Open Pivot Table Source File

    Thanks for trying. I changed 'ActiveWorkbook' to DstWbk as shown below: 
    Please Login or Register  to view this content.
    I still got the same error at the same place.
    Reply With QuoteRegister To Reply
  8. 01-04-2010, 12:30 PM #8
    Andy Pope
    Andy Pope is offline
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,481

    Re: Create Pivot Table: Cannot Open Pivot Table Source File

    Try using the R1C1 notation for the range reference.

    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info
    Reply With QuoteRegister To Reply
  9. 01-04-2010, 12:34 PM #9
    romperstomper
    romperstomper is offline
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,996

    Re: Create Pivot Table: Cannot Open Pivot Table Source File

    I assumed that was R1C1!

    The only way I can produce that error is if the sheet is not in the specified workbook.
    Reply With QuoteRegister To Reply
  10. 01-04-2010, 12:37 PM #10
    jomili
    jomili is offline
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    4,062

    Re: Create Pivot Table: Cannot Open Pivot Table Source File

    Strange. I changed the code as you suggested, and now I get a 'Run-Time Error 9: Subscript out of Range' at the 
    Please Login or Register  to view this content.
    line.
    Reply With QuoteRegister To Reply
  11. 01-04-2010, 12:38 PM #11
    romperstomper
    romperstomper is offline
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,996

    Re: Create Pivot Table: Cannot Open Pivot Table Source File

    Which means that sheet is not in the active workbook...
    Reply With QuoteRegister To Reply
  12. 01-04-2010, 12:44 PM #12
    jomili
    jomili is offline
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    4,062

    Re: Create Pivot Table: Cannot Open Pivot Table Source File

    Okay, we're getting closer. I tried this code: 
    Please Login or Register  to view this content.
    and now I get this message

    "Run-Time Error 1004:
    AddFields method of PivotTable Class Failed"

    at this line: 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  13. 01-04-2010, 12:50 PM #13
    Andy Pope
    Andy Pope is offline
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,481

    Re: Create Pivot Table: Cannot Open Pivot Table Source File

    This works for me when 2 workbooks are used.
    Code and data in Thisworkbook, pivottable created in Activeworkbook

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  14. 01-04-2010, 01:05 PM #14
    jomili
    jomili is offline
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    4,062

    Re: Create Pivot Table: Cannot Open Pivot Table Source File

    I've got it "almost" working now. I realized part of my problem was that the Pivot Table was only selected the data in Column C, whereas it should select all the data from A1 to W~20000. I changed my code as noted below, and now it creates the pivot table, but now I'm getting an error when trying to paste it. The error is 
    Please Login or Register  to view this content.
    Any ideas on how to get past that one? The error occurs in my code here: 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  15. 01-05-2010, 05:11 AM #15
    Andy Pope
    Andy Pope is offline
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,481

    Re: Create Pivot Table: Cannot Open Pivot Table Source File

    You need to check the input and output ranges to make sure the output is large enough to take the information.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1