Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Anderson, Indiana
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivottable autorefresh (Excel 2003)

    I have a pivottable that automatically refreshes upon open, but I get a message two messages. The fist is a security warning about a macro in the file (a macro I created, so I have no security concers) so I must choose accept macros and then a dialog box about Query refresh asking if I want to perform an automatic refresh. How do I stop both of these messages?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivottable autorefresh (Excel 2003)

    How is the file being opened and the query table refreshed?

    If you open the file with a macro it should not prompt about the macros. You could use refresh method in the code and you shouldn't get the message.

    <pre>Sub OpenAndRefresh()
    Workbooks.Open FileName:="c:MyQueryFile.xls"
    Worksheets(1).QueryTables(1).Refresh BackgroundQuery:=False
    End Sub</pre>


    Change filename, sheet reference, and query reference as appropriate

    Steve

  3. #3
    New Lounger
    Join Date
    Mar 2002
    Location
    Anderson, Indiana
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivottable autorefresh (Excel 2003)

    The file is opened by choosing the File > Open. Then, when the file opens, I get the first message about macros being in the workbook, and I must choose to Enable macros. Then I get the autorefresh dialog box.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivottable autorefresh (Excel 2003)

    As I said you can do it with the code I suggested and you get no dialogs.

    If you want to open it manually you can "lower" your security settings to get rid of the "enable macros" (to medium and "sign" your code.) I am not sure of all the details, perhaps this will help, or someone else might "chime in". I use XL97 I have either warning or no warning, 2003 has (I think) 3 levels.

    If you do choose manually you can add the code to the thisworkbook object (and remove the autorefresh) and it will be refreshed without a prompt (again change the sheet and table as appropriate)

    <pre>Private Sub Workbook_Open()
    Worksheets(1).QueryTables(1).Refresh BackgroundQuery:=False
    End Sub</pre>


    Steve

  5. #5
    New Lounger
    Join Date
    Mar 2002
    Location
    Anderson, Indiana
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivottable autorefresh (Excel 2003)

    I have tried cutting and pasting the code into the file, but am not sure what needs to be changed.

    The sheet name is: Results
    The pivottalble name is: PivotTable2

    Can you help with what changes need to be made?

    Thanks for your patience.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivottable autorefresh (Excel 2003)

    Try:

    <pre>Private Sub Workbook_Open()
    Worksheets("Results").QueryTables("PivotTable2").R efresh BackgroundQuery:=False
    End Sub
    </pre>

    Legare Coleman

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivottable autorefresh (Excel 2003)

    Or (if it is a Pivot table and not a query table).

    <pre>Private Sub Workbook_Open()
    Worksheets("Results").PivotTables("PivotTable2").R efresh
    End Sub</pre>


    I assumed querytable since you talked refreshing the query.


    Steve

  8. #8
    New Lounger
    Join Date
    Mar 2002
    Location
    Anderson, Indiana
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivottable autorefresh (Excel 2003)

    OK .. Thanks for the update.. I made the chnages to the code but still get the attached dialog box upon file open.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivottable autorefresh (Excel 2003)

    Read this MS Article on the message and I think will answer your question.

    Steve

  10. #10
    New Lounger
    Join Date
    Mar 2002
    Location
    Anderson, Indiana
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivottable autorefresh (Excel 2003)

    GREAT! THAT DID IT1

    THANK YOU SO MUCH FOR YOUR HELP AND PATIENCE.

    Greg

  11. #11
    New Lounger
    Join Date
    Mar 2002
    Location
    Anderson, Indiana
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivottable autorefresh (Excel 2003)

    Edited by HansV to replace huge screenshot with very little info by smaller one. The large version caused horizontal scrolling.
    In fact, there was no need for a screenshot, you could have copied and pasted the 6 or 7 lines of code into your reply directly.


    I have attached the code from the file. Thanks for your help.

Posting Permissions

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