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...
M

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

  • Tournament Sheet

    Tournament Sheet: Dear EXCEL Expert, this is the challenge: I would like to organize with help of EXCEL a Supermelee Boule-Torunament. Supermelee means, that...
  • EXCEL Sheet by VBA code printed as PDF - lines are missing in PDF file

    EXCEL Sheet by VBA code printed as PDF - lines are missing in PDF file: I have an created EXCEL sheet with many boxes. Each box represents an mechanical element. I fill the boxes according to the fault count of the...
  • How to consolidate in one Pivot Table data from more excel sheets

    How to consolidate in one Pivot Table data from more excel sheets: I have launched PowerPivot and managed to Import 4 tables (Jan Apr billings), but I cannot run a Pivot where I can Show a Consolidated table for...
  • Dateneingabe über ein Excel-Formular mit automatischer Speicherung in einem Excel-Sheet

    Dateneingabe über ein Excel-Formular mit automatischer Speicherung in einem Excel-Sheet: Ich würde gern ein Formular erstellen, das einige Eingabefelder enthält. Bei jedem Speichern des Formulars sollen die Daten automatisch in eine...
  • Ähnliche Themen
  • Tournament Sheet

    Tournament Sheet: Dear EXCEL Expert, this is the challenge: I would like to organize with help of EXCEL a Supermelee Boule-Torunament. Supermelee means, that...
  • EXCEL Sheet by VBA code printed as PDF - lines are missing in PDF file

    EXCEL Sheet by VBA code printed as PDF - lines are missing in PDF file: I have an created EXCEL sheet with many boxes. Each box represents an mechanical element. I fill the boxes according to the fault count of the...
  • How to consolidate in one Pivot Table data from more excel sheets

    How to consolidate in one Pivot Table data from more excel sheets: I have launched PowerPivot and managed to Import 4 tables (Jan Apr billings), but I cannot run a Pivot where I can Show a Consolidated table for...
  • Dateneingabe über ein Excel-Formular mit automatischer Speicherung in einem Excel-Sheet

    Dateneingabe über ein Excel-Formular mit automatischer Speicherung in einem Excel-Sheet: Ich würde gern ein Formular erstellen, das einige Eingabefelder enthält. Bei jedem Speichern des Formulars sollen die Daten automatisch in eine...
  • Oben