sheets.protect interrupts connections.refresh

Diskutiere sheets.protect interrupts connections.refresh im Office Forum im Bereich Microsoft Community Fragen; Hello, I`m new in this community. I`ve already searched on diverse HPs without finding a solution. I really hope, someone here can help me. Using...

MSCom

Neuer Benutzer
Threadstarter
Dabei seit
20.09.2016
Beiträge
1
Hello, I`m new in this community.
I`ve already searched on diverse HPs without finding a solution. I really hope, someone here can help me.

Using VBA in Excel I start a

connections(1).refresh

It is a PowerQuery that is loaded to a Worksheet(1) as table. The user should start the code after finishing his entries.

It is necessary to protect this sheet afterwards. I do it this way:

Sheets(1).protect Password:= pw

But this interrupts the download process. So my question is: How do I get to know whether the refreshing process has finished?

I`ve already tried some things with minor or no success:

.Protect Password:= pw, UserInterFaceOnly:=True
'or
Range("A1").QueryTable.Refresh BackgroundQuery:=False
'or
DoEvents
'or
Application.CalculateUntilAsyncQueriesDone

The refreshing process is interrupted every time since the protection step is applied, while refreshing is still ongoing.

This one works but is very "ugly" because I can`t know whether the procedure is used on a very slow PC:

Application.OnTime Now + TimeValue("00:00:15"), "Sheet_Protect_Sub"

Thanks
Jan_A
 
Thema:

sheets.protect interrupts connections.refresh

sheets.protect interrupts connections.refresh - Ähnliche Themen

Change my ONLINE word language from german: My online office is in german, it asks if I want to change to swedish, I press yes but nothing changes. My account is set to sweden so how can I...
Unlink Fields in Excel and PowerPoint: Hello How can I convert the fields (dates, path...) in powerpoint and excel to text? I know there's a chance to do this in word and it works...
Retrieve old data before refreshing database: Hello I have connected my Power BI to SAP server to access data. Every time I refresh the database, the new values appear on the report. What...
Same self-signed code signing certificate for all users on same computer ?: On my own local PC with Office 2019, I would like all accounts to use the same certificate for Office macros (Word, Excel, Access). Otherwise...
Share to Outlook: Hi everyone, I am using DRAWBOARD PDF on my new SURFACE PRO7, and want to SHARE a PDF to OUTLOOK. Unfortunatly it is only possible to share...
Oben