Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Microsoft Query refresh (Excel 2002)

    Hi,
    I have a microsoft query that downloads our AS400 data into Excel, and then I have a macro that I'd like to run AFTER the query refreshes, however my macro tends to run BEFORE, or at the same time as the refreshing is occuring. Is there a line of code that will make my macro wait to run until AFTER the ms query is done refreshing?
    Thanks!
    Lana

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Microsoft Query refresh (Excel 2002)

    Open the workbook and click anywhere in the data.
    Select Data | Get External Data | Data Range Properties...
    Clear the check box "Enable background refresh".
    Click OK.
    This will cause Excel to finish the refresh before continuing.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Microsoft Query refresh (Excel 2002)

    Ahh... it seems that I already had the properties for ONE of the TWO ms queries the way you suggested... I have fixed the other one now. Thanks so much Hans! On a related note, I get the following message when I open this file "This workbook contains queries to external data that refresh automatically"... how do I get this to go away. None of my other files with ms queries have this message when I open the file.
    Thanks!
    Lana

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Microsoft Query refresh (Excel 2002)

    The Data Range Properties dialog also has a check box to specify whether the data should be refreshed automatically when the workbook is opened. If this check box is ticked, you'll get the warning you mention.

    If you want to leave the check box ticked and suppress the warning, you must change a registry setting, as described in MSKB article You are prompted to enable automatic refresh when you open a workbook in Excel.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Microsoft Query refresh (Excel 2002)

    I had previously seen the box and made sure the box for each query was unchecked, so I was still wondering why I kept getting the message. I just found the problem a minute ago... I had the box "Refresh automatically when cell value changes" checked in the parameters area... I unchecked it and I no longer get the message when I open the file. Thanks for all your help Hans!! Have a great weekend!
    Lana

Posting Permissions

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