Results 1 to 2 of 2

Thread: Let PowerQueries don't have time to finish with VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Let PowerQueries don't have time to finish with VBA

    Hi

    I have a VBA script that is supposed to remove the protection of the sheets, update two PowerQueries and then re-protect the sheets.
    When I run my macro, the queries do not finish, which causes me to get errors in the result.
    I would like script to pause until the first query is finished, then the next query will run, a new break and then the rest of the macro will continue.
    Query2 is based on the result from Query1. So I need the first to finish before I run the next one.
    How do I accomplish this?



    Code below:


    Sheets ("SheetA"). Unprotect
    Sheets ("SheetB"). Unprotect


    Worksheets ("SheetA"). ListObjects ("Query1"). QueryTable.Refresh BackgroundQuery: = False
    Worksheets ("SheetB"). ListObjects ("Query2"). QueryTable.Refresh BackgroundQuery: = False



    Sheets ("SheetA"). Protect DrawingObjects: = True, Contents: = True, Scenarios: = True
    Sheets ("SheetB"). Protect DrawingObjects: = True, Contents: = True, Scenarios: = True

  2. #2
    Administrator starl's Avatar
    Join Date
    Aug 2002
    Location
    Everywhere!
    Posts
    5,859
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Let PowerQueries don't have time to finish with VBA

    There may be a way to do it with some Query property - but I don't use them often, so I don't know.
    But a general pause can be done using Application.Wait.

    So, after your first query, you can do
    Application.Wait (Now + TimeValue("0:00:10"))
    Which tells the code to wait 10 seconds before going on to the next line.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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