+ Reply to Thread
Results 1 to 12 of 12

Download did not complete - query in a protected workbook

  1. #1
    Registered User
    Join Date
    05-20-2020
    Location
    UK
    MS-Off Ver
    Office 16
    Posts
    33

    Download did not complete - query in a protected workbook

    I have a protected workbook (which must stay protected) and on opening a macro runs to unprotect, it calls 2 queries does a few more things then re-protects the workbook. It seemed to be working at outset but doesn't now. I only realised it wasnt working when the data didnt seem right and looked at the show queries and it shows 'download did not complete'. Through the connections background refresh is not enabled, fast upload is ticked, also load to worksheet, not data model. One query pulls data from another unprotected workbook the other query pulls data from another worksheet within the same workbook

    VBA is as follows

    P
    Please Login or Register  to view this content.
    a few other routines run to copy and paste info around sheets, some on the back of the loaded queries then



    Please Login or Register  to view this content.
    It appears as though the macro is completing and re-protecting the workbook before the queries have completed retrieving their data/loading. Is there a way to ensure the queries complete their refresh before moving on to the next task? Does the background refresh need to somehow also need to be hardcoded into the VBA as well?

    If I unprotect the workbook everything works fine
    I have also tried putting the activeworkbook save after the connection refresh command

    NB when I go into connections, where it says ThisWorkbook Data Model background query refresh is enabled there, but it is greyed out so I can't edit it, only the queries themselves
    Last edited by Bazbuyer; 08-14-2020 at 10:06 AM. Reason: additional clarification of problem

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Download did not complete - query in a protected workbook

    Hmm, when I tested something similar, I had no issues.

    Though instead of refreshing individual connections, I just used .RefreshAll.
    Since query dependencies are handled by connection hierarchy

    Sample code that I used.
    Please Login or Register  to view this content.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    05-20-2020
    Location
    UK
    MS-Off Ver
    Office 16
    Posts
    33

    Re: Download did not complete - query in a protected workbook

    thanks I tried replacing the individuals with ActiveWorkbook.RefreshAll, but this doesnt seem to refresh anything. As the last refresh is still showing at the time when I had the individual refreshes that didnt fully complete?

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Download did not complete - query in a protected workbook

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  5. #5
    Registered User
    Join Date
    05-20-2020
    Location
    UK
    MS-Off Ver
    Office 16
    Posts
    33

    Re: Download did not complete - query in a protected workbook

    ok, done thanks

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Download did not complete - query in a protected workbook

    Wait... you have code in the workbook that's being opened itself?

    I'd house the code outside of the workbook and run code to open and manipulate target workbook (as in my code sample in post#2).

    Also, using "ActiveWorkbook" in code is unreliable and prone to context error. I'd recommend setting target workbook as workbook variable.

  7. #7
    Registered User
    Join Date
    05-20-2020
    Location
    UK
    MS-Off Ver
    Office 16
    Posts
    33

    Re: Download did not complete - query in a protected workbook

    Many thanks for your reply. I've got rid of the active sheets and used a target as you suggest. Still getting same error, however, not sure what you mean by housing the other code outside the worksheet? Unfortunately my VBA knowledge stems from googling looking for code that will 'do things' rather than any understanding so it tends to be bits and pieces cobbled together. I now have this code in the ThisWorkbook section.

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    05-20-2020
    Location
    UK
    MS-Off Ver
    Office 16
    Posts
    33

    Re: Download did not complete - query in a protected workbook

    I've also now tried removing all the surplus code, so just running a macro to open, unprotect, refresh and protect and still the same error/warning. Additionally I've put the macro in a separate workbook to try and run from there and its still not working?
    thanks

  9. #9
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Download did not complete - query in a protected workbook

    Does your code work if you remove workbook protection manually and ran without the Unprotect/Protect?

    If it doesn't... problem likely is outside of code. Without knowing your environment, network security etc, bit hard to help...

    Make sure that folder/location where file(s) are stored is set as trusted location.

  10. #10
    Registered User
    Join Date
    05-20-2020
    Location
    UK
    MS-Off Ver
    Office 16
    Posts
    33

    Re: Download did not complete - query in a protected workbook

    Yes the code works up to the reprotecting, so if I comment that out it works. It’s as though protecting the sheet at the end is too early, the vba does know the query is still running and doesn’t let it complete that step. So it starts but doesn’t finish albeit you don’t know that until the data looks funny or you check data>show queries

  11. #11
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Download did not complete - query in a protected workbook

    I can't replicate that issue on my end. Process works fine and protect/unprotect will wait until query is completed and data updated.

    Sorry, don't think I can help you further.

  12. #12
    Registered User
    Join Date
    05-20-2020
    Location
    UK
    MS-Off Ver
    Office 16
    Posts
    33

    Re: Download did not complete - query in a protected workbook

    Thank you very much for trying to help , there does seem to be a lot of posts online with people with similar issues, using application wait routines, do events and similar, code for background query etc but like me there are a log of unsolved. I’m sure mine worked it the beginning so suspect it must have something to do with the complicity or size of the query that as it grows surpasses a certain point where it can’t complete.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Error problem, Importing table from pass protected workbook Power Query or other?
    By tta.akmal in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 03-29-2018, 03:59 AM
  2. [SOLVED] VBA code to update query from a password protected excel workbook and then close workbook
    By MrChipper in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-14-2017, 07:41 AM
  3. VBA code to wait until file download from IE is complete
    By Yash20 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-10-2016, 04:10 PM
  4. Refresh all query & save. not enough time for query to complete.
    By mortphil in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-08-2015, 04:26 AM
  5. Download text file from web without complete filename
    By osubuckeye2007 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-21-2014, 12:48 PM
  6. Create Query from Password Protected Workbook
    By Joy C in forum Excel General
    Replies: 0
    Last Post: 03-25-2011, 05:46 AM
  7. Refresh query with a password protected workbook.
    By Joy C in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-02-2011, 12:31 PM

Tags for this Thread

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