VBA code to update query from a password protected excel workbook and then close workbook

[MrChipper]

I am a new member to the forum. I've looked through the various threads, but can't find a solution to my current problem. I have a query in one workbook that updates from a table in another password protected workbook. A password is required both to open and modify the workbook. I need VBA code to open the password protected workbook, update the query, and then close the workbook, after the query has refreshed.

I have got as far as opening the workbook and refreshing the query. The two areas I need help with are:

1. what is the code to get past the modify password?

2. what is the code to close the workbook only after the query has refreshed? (I can close the workbook, but it does it before the refresh is complete and the refresh therefore fails)

Obviously I have access to all passwords.

Code extract

[I]

Please Login or Register  to view this content.

[TMS]

Welcome to the forum

See: https://msdn.microsoft.com/en-us/lib.../ff194819.aspx


Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, 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

[MrChipper]

Thank you, Trevor, for putting me right on the correct etiquette. Also your link helped me identify the correct syntax for the read/write access. I still need to figure out how to delay closing the opened workbook until after the query refresh has completed.

[TMS]

You're welcome. Thanks for the rep.


You could maybe put in a loop ... with a very high counter, or perhaps use On Time to delay the close.

[MrChipper]

I tried the code below as a timer, but all that happened is that, even though this was added after the refresh instruction, the blue circle spun for 5 minutes after the workbook open, then it closed and then it asked me for the passwords, reopened and then did the refresh after which I had to close the workbook manually.

Please Login or Register  to view this content.

I did wonder whether it had something to do with running the query in background, but when I changed this, the refresh didn't complete.

[MrChipper]

I tried the code below as a timer, but all that happened is that, even though this was added after the refresh instruction, the blue circle spun for 5 minutes after the workbook open, then it closed and then it asked me for the passwords, reopened and then did the refresh after which I had to close the workbook manually.

Please Login or Register  to view this content.

I did wonder whether it had something to do with running the query in background, but when I changed this, the refresh didn't complete.

Ok. The following code worked. I used this Loop to delay the close of the workbook:

Please Login or Register  to view this content.

[TMS]

Thanks for sharing your solution. I'm sure others wll find it useful.