Confused with VBA - Macro works fine in one workbook, but when I copy same code into another workbook, it doesn't work

[Jmorrison67]

Good morning All,

I'm looking for some help with what is probably a simple bit of VBA coding. I am not familiar with VBA and got the code from online biccountant i think it was.

What the code is intended to do is:
Unprotect a worksheet named "Consol" >>> Consol is the output of a power query
Refresh the query
Protect the worksheet again

It works fine one the 1st workbook i tried it in but when i use the name code, but changing the query name, it doesnt work....tried googling for 3+ hours to find a solution but REALLY struggling.

Code that works in workbook 1:

Sub Refresh_Report()

Sheets("Consol").Unprotect Password:="PASS"

ActiveWorkbook.Connections("Query - ConsolPSC").Refresh

Sheets("Consol").Protect Password:="PASS"

MsgBox "All data has now refreshed"

End Sub


Code that doesnt work in workbook 2:

Sub Refresh_Report()

Sheets("Consol").Unprotect Password:="PASS"

ActiveWorkbook.Connections("Query - ConsolDist").Refresh

Sheets("Consol").Protect Password:="PASS"

MsgBox "All data has now refreshed"

End Sub



Only thing i changed was the query name from ConsolPSC to ConsolDist as the ConsolPSC is the name of the query in workbook 1 and Consol Dist the name in workbook 2. My thinking was that i would need to define the 'Activeworkbook', maybe rename it to whatever the workbook is called - i'm sure it is something really simple.

The error i get in the 2nd workbook is:
which is ofcourse the standard message you get when you try to enter into a protected worksheet.

Any help would be appreciated

Kind regards
Jmorrison67

 

[sykes]

In workbook 2, is there actually a sheet called "Consol" (case sensitive).
Also, is it actually protected?
If so, is the password actually "PASS" (also case sensitive)?

 

[Jmorrison67]

In workbook 2, is there actually a sheet called "Consol" (case sensitive).
Also, is it actually protected?
If so, is the password actually "PASS" (also case sensitive)?

Hi Sykes,
Thanks for your reply.

1. Yes - see below:

2. Yes - protected - see below:
This is the refresh icon i put in, which i assigned the macro to

when i click the icon - this happens:

Message i would expect to see if it's actually updated

Followed by this:

I'm just confused as it is exactly the same code as in my other workbook - the other workbook is for a different part of the business, but has the same layout, tabs etc its just called something different



3. Yes - password is all caps PASS

 

[sykes]

May I suggest unprotecting the sheet manually, then running your code again, but without the protection lines, thus:
(I've commented-out the lines, so they won'[t run).

Sub Refresh_Report()

'Sheets("Consol").Unprotect Password:="PASS"

ActiveWorkbook.Connections("Query - ConsolDist").Refresh

'Sheets("Consol").Protect Password:="PASS"

MsgBox "All data has now refreshed"

End Sub

 

[Jmorrison67]

May I suggest unprotecting the sheet manually, then running your code again, but without the protection lines, thus:
(I've commented-out the lines, so they won'[t run).

Sub Refresh_Report()

'Sheets("Consol").Unprotect Password:="PASS"

ActiveWorkbook.Connections("Query - ConsolDist").Refresh

'Sheets("Consol").Protect Password:="PASS"

MsgBox "All data has now refreshed"

End Sub

Hi Sykes,

I removed the unprotect / protect bits and ran the code and worked fine i.e. refreshed the query as would expect to see.

But the ultimate goal is to get the code to unprotect the sheet, refresh it and then protect it (the scenario is to make a file user friendly for non IT / technophobes and so they dont change any of the data on the 'Consol' tab in the workbook. Is there any alternative to these lines:

'Sheets("Consol").Protect Password:="PASS"
'Sheets("Consol").Unprotect Password:="PASS"

It is strange (to me) how it works fine in one workbook but not another 0- would there be a conflict somewhere?

 

[sykes]

... so it's purely a matter of getting the sheet unprotected, programmatically.

With the sheet manually protected (also try to manually change a cell's content, to confirm protection is on), run just the one line of code:

Sheets("Consol").Unprotect Password:="PASS"

...then manually try to change a cell's contents, again.
Does the protection come off the sheet?

Also, where is this code residing? Is it in the 2nd workbook's VBA project?
Sheet module, or a standalone module?

 

[Jmorrison67]

... so it's purely a matter of getting the sheet unprotected, programmatically.

With the sheet manually protected (also try to manually change a cell's content, to confirm protection is on), run just the one line of code:

Sheets("Consol").Unprotect Password:="PASS"

...then manually try to change a cell's contents, again.
Does the protection come off the sheet?

Also, where is this code residing? Is it in the 2nd workbook's VBA project?
Sheet module, or a standalone module?

OK
So i run the code just as above :
Sheets("Consol").Unprotect Password:="PASS"
And it unprotected the sheet
and yes i can manually change the contents of the cells (protection comes off)

FYI - this is literally my 1st ever attend at VBA, as i was asked by my manager to come up with this button thing for the non IT guys to just hit to refresh the data on that Consol tab

The below is where i have it running
steps i found online were:
Alt + F11 to open the VBA window
Insert > Module
Copy the code as above
Press save

Go back to excel
assign a macro to the refresh icon
run

(I have blocked out confidential info below)

 

[sykes]

I've just had a thought...
If your query's set to "Background refresh" (one of its properties) then the rest of your code will be allowed to run, even though the query hasn't necessarily completed.
If this was the case, the protection will come off your sheet, the refresh will start, the message Box will display, and the sheet will be re-protected, all possibly before the refresh has finished (or even started) updating the data in your table.
In DATA, go to "Queries & connections", find your query, on the RHS, right-click, then you should get this:

...ensure that "Enable Background Refresh" is unchecked, and try the full routine, again.

 

[sykes]

... well done on your first VBA efforts by the way - bit daunting when you first start!

also - Welcome to Mr. Excel!

 

[Jmorrison67]

Sykes!!!
YOU
ARE
AMAZING!

This is what i had:

Unchecked it, re-entered the full code and hey presto!!
Phew - i am so relieved now - you've topped off my friday / week


Thanks you so much
from a very grateful accountant