+ Reply to Thread
Results 1 to 9 of 9

Run-time error '1004'... Cannot open PivotTable source file

  1. #1
    Registered User
    Join Date
    05-16-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2007
    Posts
    63

    Run-time error '1004'... Cannot open PivotTable source file

    I have searched all over the forums and haven't been able to find a post that addresses my specific issue.
    I work in payroll outsourcing. I have a client I deal with and my co-workers have other clients. We are responsible for identifying when the clients are out of balance on their FICA filings. In order to do this we have SAP output a .dat(text) file. Formatting and analyzing this is a very LONG and monotonous task.

    I am trying to develop a macro to do this that ANY coworker can use ANYTIME on ANY client file. The data format output from SAP is always the same. I have been successful in creating the macro using one of my files; however, based on this error it looks like I have to make everyone else use the same file name in order for the macro to run successfully. Keep in my mind, I do not know have any VB(or any other) coding experience, but I am a techie.

    Here is the error: OLqQ9.png

    Below is the code. The error occurs in the "ActiveWorkbook" section. I'm assuming it is because there is because each client will have a different tab name and file name than "582_03142012_AMOUNTS". Is there a way to make this generic so the file name or tab name do not affect the macro?

    Please Login or Register  to view this content.


    Thanks,


    Craig
    Last edited by csh8428; 05-16-2012 at 11:27 AM.

  2. #2
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Run-time error '1004'... Cannot open PivotTable source file

    hi Craig, welcome to ExcleForum, prior you get any reply you need to modify code posting under Forum Rules:http://www.excelforum.com/forum-rule...rum-rules.html

    3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Select your code and click the [#] button at the top of the post window (if you are editing an existing post, press Go Advanced to see the [#] button). The result will appear like this in the post window:

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    05-04-2012
    Location
    Stamford,Connecticut,USA
    MS-Off Ver
    Excel 2003
    Posts
    105

    Re: Run-time error '1004'... Cannot open PivotTable source file

    Here is a simple solution:
    Type this at the top of the code:
    FiletoProcess=Inputbox("Please enter File Name")
    Then modify this section:
    SourceData:= _
    "FiletoProcess"&"!R1C1:R1048576C13",

  4. #4
    Registered User
    Join Date
    05-16-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2007
    Posts
    63

    Re: Run-time error '1004'... Cannot open PivotTable source file

    Thanks waterserv!

    I have made the edits per your request.

  5. #5
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Run-time error '1004'... Cannot open PivotTable source file

    try this add on to your code: it will pop up file choose dialog (the .dat file to open) and sets variable fname holding filename and range to be the base for pivot:

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    05-16-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2007
    Posts
    63

    Re: Run-time error '1004'... Cannot open PivotTable source file

    waterserv.

    Thanks for the quick reply. I don't think that will work in my case because this process actually has 7 macros and this macro is actually the 2nd one. By the time this macro is launched, the file has already been opened. I know this is probably not programming best practice, but like I said... I'm no coder. This was all built using macro recorder.

    Would it be helpful to add the first macro code as well? Also, I have pasted the all the code from macro in question, I only posted the first half the first time.

    File is opened by user(not a macro)
    The macros are as follows:
    Add first row headers and delete extraneous data
    Create a main pivot table(the one with the error in this thread)
    Change main pivot table to criteria A and copy values of main pivot table to new tab(for analysis)
    Change main pivot table to criteria B and copy values of main pivot table to new tab(for analysis)
    Apply calculations to "new tables"
    Apply conditional formatting to 2 "new tables"
    Sort "new tables" by out of balance
    Master Macro that runs all previous macros in sequence

    Thanks,

    Craig
    Last edited by csh8428; 05-16-2012 at 11:39 AM.

  7. #7
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Run-time error '1004'... Cannot open PivotTable source file

    when user opens .dat file excel opens it on a sheet named by the file opened. As far as I can see the code runs on the same sheet. So the originally posted code can be built this way:

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    05-16-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2007
    Posts
    63

    Re: Run-time error '1004'... Cannot open PivotTable source file

    Macro Design.pdfMacro Design.pdfwatersev.
    I pasted the code as such. Did I paste it wrong?
    Please Login or Register  to view this content.
    I get the error
    Runtime error '1004'
    This command requires at least two rows of source data. You cannot use the command on a selection in only one row. Try the following:

    if you're using an advanced filter, select a range of cells that contains at least two rows of data. Then click the Advanced Filer command again.
    If you're creating a PivotTable report or PivotChart report type a
    (it ends there, doesn't any more)

    Not sure if this helps, but I have attached a document to demonstrate what it is I'm trying to do.
    Thanks,

    Craig
    Last edited by csh8428; 05-16-2012 at 02:14 PM.

  9. #9
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Run-time error '1004'... Cannot open PivotTable source file

    this error means that there is no data on the activesheet to build pivot on. Delete these two lines:
    Please Login or Register  to view this content.
    and try again. If it errors out then

    change this line:

    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.
    Sheet(1) - named after opened .dat file

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1