VBA code required to refresh multiple queries on different tabs, unprotecting and then reapplying the password

Jmorrison67

New Member
Joined
Aug 20, 2021
Messages
43
Office Version
  1. 2016
Platform
  1. Windows
Good evening MrExcel Community,

I'm looking for a bit of help with VBA (again) :)
I thought it would be quite straight forward what i'm trying to do having managed alrightish with my 1st VBA project the other week....so here goes:

Original VBA for refresh based on one tab, with output in the tab a power query
(tab names and passwords changed)

Sub Refresh_Report()
Sheets("Peter").Unprotect Password:="PETER"
ActiveWorkbook.Connections("Query - PeterDist").Refresh
Sheets("Peter").Protect Password:="PETER"
MsgBox "All data has now refreshed"
End Sub


(Above works fine)

I have 3 more tabs which have their own queries. On each of the tabs I have a ‘refresh’ button which has the above code – switching out the tab and query names. Everything runs fine.

What I have now been asked to do is create a macro which allows the user just to hit ‘refresh’ button once, not 4 times (once per tab, which has a different query on it), so I thought it would just be a case of stacking the code up as follows:

Sub Refresh_ALL()

Sheets("Peter").Unprotect Password:="PETER"

Sheets("James").Unprotect Password:="JAMES"

Sheets("Toby").Unprotect Password:="TOBY"

Sheets("Robert").Unprotect Password:="ROBERT"


ActiveWorkbook.Connection("Query - PeterDist").Refresh

ActiveWorkbook.Connection("Query - JamesDist").Refresh

ActiveWorkbook.Connection("Query - TobyDist").Refresh

ActiveWorkbook.Connection("Query - RobertDist").Refresh


Sheets("Peter").Protect Password:="PETER"

Sheets("James").Protect Password:="JAMES"

Sheets("Toby").Protect Password:="TOBY"

Sheets("Robert").Protect Password:="ROBERT"


MsgBox "All data has now refreshed"


End Sub



When I run this and assign the code to a refresh button 1. The ‘sub bit at the start turns a maroon colour with a dot in the left margin – I have no idea what that mean??

If I try and run the macro in excel the sub turns yellow and nothing happens:

1630616250132.png




Can someone advise if the code is completely wrong?

Ultimate goal is to allow the user to simply click a refresh button which gives them the most up to view / position. All the tabs Peter, James, Toby, Robert are protected with a password and the code should be unlocking each of the tabs, refreshing the data and then reapplying the passwords. I’m sure there is probably an easier way of doing it.

Any help would be much appreciated :)
KR Jmorrison67
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Jmorrison67

New Member
Joined
Aug 20, 2021
Messages
43
Office Version
  1. 2016
Platform
  1. Windows
Sheets("Peter").Unprotect Password:="PETER"

Sheets("James").Unprotect Password:="JAMES"

Sheets("Toby").Unprotect Password:="TOBY"

Sheets("Robert").Unprotect Password:="ROBERT"

Sheets("Peter").Protect Password:="PETER"

Sheets("James").Protect Password:="JAMES"

Sheets("Toby").Protect Password:="TOBY"

Sheets("Robert").Protect Password:="ROBERT"


Apologies - the password is the same for all sheets - not different for each one - that might help :)
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,980
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Firstly the Maroon dot is a Bookmark....click on the little round Maroon dot again to remove it and the code will run.
Second I don't have PowerQuesry, but you could have simply written the code x number of times and then simply called each macro
VBA Code:
Sub Refresh_Peter()
Sheets("Peter").Unprotect Password:="PETER"
ActiveWorkbook.Connections("Query - PeterDist").Refresh
Sheets("Peter").Protect Password:="PETER"
MsgBox "All data has now refreshed"
End Sub
Sub Refresh_Robert()
Sheets("Robert").Unprotect Password:="ROBERT"
ActiveWorkbook.Connections("Query - RobertDist").Refresh
Sheets("Robert").Protect Password:="ROBERT"
MsgBox "All data has now refreshed"
End Sub
Then used
VBA Code:
Sub RefreshQuery()
Call Refresh_Peter
Call Refresh_Robert
end sub
 

Jmorrison67

New Member
Joined
Aug 20, 2021
Messages
43
Office Version
  1. 2016
Platform
  1. Windows
Firstly the Maroon dot is a Bookmark....click on the little round Maroon dot again to remove it and the code will run.
Second I don't have PowerQuesry, but you could have simply written the code x number of times and then simply called each macro
VBA Code:
Sub Refresh_Peter()
Sheets("Peter").Unprotect Password:="PETER"
ActiveWorkbook.Connections("Query - PeterDist").Refresh
Sheets("Peter").Protect Password:="PETER"
MsgBox "All data has now refreshed"
End Sub
Sub Refresh_Robert()
Sheets("Robert").Unprotect Password:="ROBERT"
ActiveWorkbook.Connections("Query - RobertDist").Refresh
Sheets("Robert").Protect Password:="ROBERT"
MsgBox "All data has now refreshed"
End Sub
Then used
VBA Code:
Sub RefreshQuery()
Call Refresh_Peter
Call Refresh_Robert
end sub
Good morning Michael,

Thanks for your reply.

This is my 2nd ever VBA project so still coming to terms with navigating around.

Doing this part, does the job - but wonder if there is any improvements I/we can make to it?
VBA Code:
Sub RefreshQuery()
Call Refresh_Peter
Call Refresh_Robert
end sub

Just one follow up - I have the refresh button on the 'Contents' page - when I run this macro, I watch it going through its 'refresh' process by flicking through the tabs Peter, Robert, James, Toby and then it finishes on the tab which I have 'call' last in the list. Is there a way to run the macro and staying on the contents page?

Also is there a way I can change the code in the 4 seperate macro's Refresh_Peter, Refresh_Robert, Refresh_James, Refresh_Toby so that when the 'Call' macro runs the user doesnt have to click the 'All data has now refreshed' 4 times, just once at the end? One final thing, my manager is quite reluctant to use power query's and macro's as prefers basic excel and is concerned with the time running this which is c30 seconds (i'm pro this route and what's a 30 sec delay :), just wondering if there is anything I can do to speed up the macro?

Your insight is most valuable

KR Jmorrison67
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,980
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

Is it ALL sheets except for the "Contents" Sheet ??
 

Jmorrison67

New Member
Joined
Aug 20, 2021
Messages
43
Office Version
  1. 2016
Platform
  1. Windows
Is it ALL sheets except for the "Contents" Sheet ??
Good morning Michael,

My workbook has 19 tabs.

Contents is on tab 1
'Peter' is on tab 19
'Robert' is on tab 5
'Toby' is on tab 6
'James' is on tab 7

So the call job finishes on tab 7, but need the job either not to move tabs as its running or return to contents page when its done. Hope that makes sense

Appreciate your help

KR Jmorrison67
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,980
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

So there are other worksheets that don't need to be "Refreshed" ??
AND
It's only 4 sheets to update ??
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,980
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
I don't have Excel ATM, but I believe you will need something like this (UNTESTED)
VBA Code:
Sub Refresh_ALL()
Dim ws As Worksheet
For Each ws In Worksheets
    If ws.Name = "James" Or ws.Name = "Robert" Or ws.Name = "Toby" Or ws.Name = "Peter" Then
        With ws
                .Unprotect Password:=UCase(ws.Name)
                ActiveWorkbook.Connection("Query - " & ws.Name & "Dist").Refresh
                .Protect Password:=UCase(ws.Name)
        End With
    End If
    Next ws
MsgBox "All data has now refreshed"
End Sub
Hopefully someone else can provide further information if required
 

Jmorrison67

New Member
Joined
Aug 20, 2021
Messages
43
Office Version
  1. 2016
Platform
  1. Windows
So there are other worksheets that don't need to be "Refreshed" ??
AND
It's only 4 sheets to update ??
Morning Michael,

Yes just 4 Power Query outputs on just 4 tabs. The other tabs are user input info, with the main Power Query 'Peter' consolidating all the info into one sheet. Peter is the most important of all the outputs.

Regards
Jmorrison67
 

Jmorrison67

New Member
Joined
Aug 20, 2021
Messages
43
Office Version
  1. 2016
Platform
  1. Windows
I don't have Excel ATM, but I believe you will need something like this (UNTESTED)
VBA Code:
Sub Refresh_ALL()
Dim ws As Worksheet
For Each ws In Worksheets
    If ws.Name = "James" Or ws.Name = "Robert" Or ws.Name = "Toby" Or ws.Name = "Peter" Then
        With ws
                .Unprotect Password:=UCase(ws.Name)
                ActiveWorkbook.Connection("Query - " & ws.Name & "Dist").Refresh
                .Protect Password:=UCase(ws.Name)
        End With
    End If
    Next ws
MsgBox "All data has now refreshed"
End Sub
Hopefully someone else can provide further information if required
Good morning Michael,

Thanks - i'll give it a crack this morning. Just to clarify, do I need to write this part 4 times or is there an AND or comma function I can use or is it simply just as the code reads? and where you have (ws.Name) do I enter what the worksheet is called?

With ws
.Unprotect Password:=UCase(ws.Name)
ActiveWorkbook.Connection("Query - " & ws.Name & "Dist").Refresh
.Protect Password:=UCase(ws.Name)

Example:
With ws
.Unprotect Password:= PETER(ws.Peter)
ActiveWorkbook.Connection("Query - " & ws.Peter & "Dist").refresh
.Protect Password:= PETER(ws.Peter)

Thanks again

KR Jmorrison67
 

Forum statistics

Threads
1,143,689
Messages
5,720,310
Members
422,275
Latest member
Maria95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top