Refresh External Data on Protected Sheet

[caelhonig]

Hi all,

Hideously poor newbie at VBA here. Need a macro that unprotects the sheet "Email", refreshes the external data connections on the sheet (refresh all), then protects the "Email" Sheet again. Any help appreciated.

Thank you.

 

[tusharm]

In most cases, you can get Excel to give you the necessary code:

Turn on the macro recorder, do whatever you want through the UI, turn off the recorder, and check the VB Editor.

Hi all,

Hideously poor newbie at VBA here. Need a macro that unprotects the sheet "Email", refreshes the external data connections on the sheet (refresh all), then protects the "Email" Sheet again. Any help appreciated.

Thank you.

 

[caelhonig]

In most cases, you can get Excel to give you the necessary code:

Turn on the macro recorder, do whatever you want through the UI, turn off the recorder, and check the VB Editor.

Thanks.
Tried this:

Sub Macro2()'
' Macro2 Macro
'


'
    ActiveWorkbook.Unprotect
    Sheets("OPR").Select
    Sheets("Email").Visible = True
    ActiveWorkbook.RefreshAll
    Sheets("Email").Select
    ActiveWindow.SelectedSheets.Visible = False
    ActiveWorkbook.Protect Structure:=True, Windows:=False
End Sub

Getting error: The password you supplied is not corried (run-time error '1004'):
ActiveWorkbook.Unprotect

 

[tusharm]

If you look at the code, you will see there is no password provided in the Unprotect method.

Apparently, Excel does not record the password.

Just add the password in both the Unprotect and the Protect statements:

ActiveWorkbook.Unprotect "mypassword"

and

ActiveWorkbook.Protect "mypassword", Structure:=True, Windows:=False

Thanks.
Tried this:

Sub Macro2()'
' Macro2 Macro
'


'
    ActiveWorkbook.Unprotect
    Sheets("OPR").Select
    Sheets("Email").Visible = True
    ActiveWorkbook.RefreshAll
    Sheets("Email").Select
    ActiveWindow.SelectedSheets.Visible = False
    ActiveWorkbook.Protect Structure:=True, Windows:=False
End Sub

Getting error: The password you supplied is not corried (run-time error '1004'):
ActiveWorkbook.Unprotect