0

I want to refresh a connection on a protected sheet. Refreshing starts but gets interrupted in the protection-step, so my connection doesn't finish its update. I already searched for hours.

I already set the OLEDBConnection.BackgroundQuery = False, Checking for Application.CalculationState or Application.CommandBars.GetEnabledMso("RefreshStatus") doesn't work either.

Sub ShortV()
    Sheet1.Unprotect
    ActiveWorkbook.Connections(1).Refresh
    Sheet1.Protect
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Jan A
  • 1

1 Answers1

0

Protect your worksheet using the parameter UserInterFaceOnly:=True so the protection does only affect the user interface but not any VBA code. That means VBA can edit the protected worksheets while the user cannot.

You can then run .Refresh with VBA even on a protected worksheet.

.Protect Password:="passw", UserInterFaceOnly:=True

You might need to do the protection of the worksheet on (re)-opening of the workbook (eg Workbook_Open() event). See VBA Excel: Sheet protection: UserInterFaceOnly gone

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • @jan A I have clarified my explanation. See this answer again. – Pᴇʜ Jan 18 '19 at 10:08
  • hm, could you tell me on which point you use UserInterFaceOnly:=True in the code? Maybe I did it wrong. – Jan A Jan 18 '19 at 11:00
  • In the [Worksheet.Protect method](https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.protect) that you use to protect your sheet. Don't protect your sheet manually protect it using the `Workbook_Open()` event. – Pᴇʜ Jan 18 '19 at 11:02
  • @PEH: the error message in my german version is: "Der Download wurde nicht abgeschlossen" [roughly translated: the download was not completed] even if I am writing UserInterFaceOnly:=True before the .refresh. I`m getting a little bit desperate about it. Usually I find solutions to my problems within minutes, this one took me already days... – Jan A Jan 18 '19 at 11:09
  • @PEH: I unprotected my sheet, then protected it with UserInterFaceOnly:=True, then ran my code to refresh, still the same result. :/ – Jan A Jan 18 '19 at 11:12
  • Have a look here: https://stackoverflow.com/questions/20349607/protect-excel-worksheet-for-read-only-but-enable-external-data-refresh – Pᴇʜ Jan 18 '19 at 11:15
  • Thank you again. It still doesn`t work. The code of the link boils down to my 5 lines of code with UserInterFaceOnly:=True added. If I comment the .protect out everythings works fine. – Jan A Jan 18 '19 at 11:37