Create an Excel VBA Macro to Update Links and Reprotect All Sheets in WPS Office
Question details
Create one Excel VBA procedure that unprotects every worksheet, updates links to remote workbooks, and protects the worksheets again.
- Product
- WPS Office Spreadsheets and Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user maintains an Excel workbook with protected sheets and external links that must be refreshed periodically.
- Observed behavior
- A single macro should unprotect every worksheet, update all Excel links, then protect the worksheets again with the same password.
Before running the macro, confirm that the workbook contains the external links you intend to update and that every worksheet uses the same protection password. In WPS Office, save the file as a macro-enabled workbook (.xlsm) so the VBA project remains available.
Create a single update-links-and-reprotect macro with helper procedures
Use this when you want a repeatable, easy-to-read macro that reuses your existing protect and unprotect routines.
This approach separates the unprotect and protect actions into Private helper procedures. The main macro calls them, updates links, and then restores sheet protection.
Open the workbook in WPS Office Spreadsheets, click the Developer tab, and select Visual Basic. If the Developer tab is not visible, enable it from File > Options > Customize Ribbon.
In the Visual Basic editor, locate the workbook project in the Project Explorer, right-click it, and choose Insert > Module. Double-click the new module to open it.
Paste the following code into the module, replacing "ops" with your worksheet password: Private Sub UnprotectSheets() Dim wsheet As Worksheet For Each wsheet In ActiveWorkbook.Worksheets wsheet.Unprotect Password:="ops" Next wsheet End Sub Private Sub ProtectSheets() Dim wsheet As Worksheet For Each wsheet In ActiveWorkbook.Worksheets wsheet.Protect Password:="ops" Next wsheet End Sub Sub UpdateLinksInSheets() UnprotectSheets ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks ProtectSheets End Sub
Place the cursor inside UpdateLinksInSheets and press F5, or return to the spreadsheet and click Developer > Macros, select UpdateLinksInSheets, and choose Run.
Use a single self-contained loop to unprotect, update, and protect sheets
Use this if you prefer one compact procedure and do not need separate helper macros.
Automate link updates and worksheet protection with a WPS VBA macro
Create and run a VBA macro in WPS Office Spreadsheets to unprotect all sheets, refresh Excel workbook links, and protect the sheets again automatically.
- 1. Open WPS Office Spreadsheets: Download and open WPS Office, then open the macro-enabled workbook that contains your protected sheets and external links.
- 2. Open the VBA editor: Click the Developer tab > Visual Basic, or press Alt+F11, to open the VBA editor.
- 3. Insert and paste the macro: Right-click the workbook project, choose Insert > Module, and paste the UpdateLinksInSheets macro with the helper procedures.
- 4. Run the macro: Press F5 inside the macro, or from the worksheet click Developer > Macros, select UpdateLinksInSheets, and click Run.
- 5. Confirm the result: Check under Data > Edit Links that the links show updated values, and confirm each worksheet is protected again.
Frequently Asked Questions
Can I run this Excel VBA macro in WPS Office Spreadsheets?
Yes, WPS Office Spreadsheets includes a VBA editor. Open the workbook, click Developer > Visual Basic or press Alt+F11, insert a module, and paste the macro. Macro support may vary by WPS version, so test the macro on a copy first.
What do I change in the code if my sheet password is different?
Replace "ops" or "PASSWORD" in the Unprotect and Protect statements with your actual worksheet password. Use the same password for every sheet, otherwise the macro stops when it cannot unprotect a sheet.
Why does ActiveWorkbook.UpdateLink not update anything?
The workbook must have at least one existing external link. Check Data > Edit Links in WPS Office to confirm the link appears; if LinkSources is empty, add the link to the remote workbook first.
How do I hide the helper procedures from the Macros dialog?
Declare their subroutines as Private Sub, for example Private Sub UnprotectSheets(). This keeps them callable from the main macro but hides them from the macro list.
Does this macro protect the workbook structure too?
No. The example protects worksheets only. If the workbook structure is protected, unprotect it separately before running the macro or add a line such as ActiveWorkbook.Unprotect Password:="ops".
