docs.healthdata.beAt the end of each year, a number of steps need to be taken to ensure the weekly SLA reporting continues to run automatically and uninterrupted during the following year. These steps have already been completed to prepare for the year 2024 but will need to be followed for all subsequent years.
STEP ONE - Create a new folder for the new year
Create a new folder in the following location on Dropbox as shown below:
HD_Reports\HD_Service_Level_Report\HD_Service_Level_Report_MSA_Year_Week
The folder should have the same naming convention as for others.
For example, in 2025 the folder should be named: HD_Service_Level_Report_MSA_Weekly_2025
STEP TWO - Copy and rename Excel and Powerpoint files
Navigate to the following folder on Dropbox:
HD_Reports\HD_Service_Level_Report\HD_Service_Level_Report_MSA_Year_Week\Calculations
Copy the Excel and Powerpoint files for the current year.
At the end of 2024 these will be called: HD_Service_Level_Report_MSA_Weekly_2024
Rename these two files by adding a suffix with the following year as shown below.
STEP THREE - Change year names in the newly created Excel file
Open the newly created Excel file.
If the security warning shown below is displayed, ensure to click "Enable Content" to preserve the links with the spreadsheet that is automatically generated by SAS.
Now use the find and replace function to change all references to the current year to the following year.
Ensure that the "Within: Workbook" option is selected and click "Replace All". There should be 102 replacements.
When this is done, save the Excel file and keep it open as it will be necessary for future steps.
STEP FOUR - Open Powerpoint file and break existing links
Open the newly created Powerpoint file. If prompted to update links, select "Cancel".
We want to break all existing links as they point to the Excel file from the current year.
Click the "Edit Links to Files" button displayed under the taskbar. If this button is not immediately visible, you can right-click on the taskbar, click "Customize the Ribbon" and place it there yourself.
In this pop-up, you will see a list of all the external links - this represents every graph in the document.
You must break each link individually - approximately 50 in total.
Select the first link and then click "Break Link" consecutively until all the links have disappeared - as shown below.
STEP FIVE - Copy graphs from Excel to Powerpoint as links
The next step is the most time consuming. We need to delete the 50 graphs in the Powerpoint which are currently linked to the Excel for the current year (e.g. 2024) - and replace them individually with graphs that are linked to the Excel file we just created for the following year (e.g. 2025).
Each tab in the Excel corresponds to a page in the Powerpoint. You can match them up by looking at the indicator number in the top left of the Excel tab, and the title of the Powerpoint page. Ignore the Excel tab names as these are not correct. In the example shown below we are copying HDI00053.
Select the graph, right-click, and click "Copy" as shown below. Then navigate to the appropriate page in the Powerpoint.
As we want to keep an automatic link between the two documents, we must use "Paste Special" (ctrl + alt + v).
Then select "Paste link", click "Microsoft Excel Chart Object" and click "OK" as shown below.
STEP SIX - Check the newly created links
You can check that all the links have been created correctly by again going to the "Edit Links to Files" button.
There should be a total of 50 links. If everything was copied in the right order, the first link should end with HDI0003 and then they should proceed sequentially until HDI00052.
Also ensure that the "Automatic Update" option is checked.
If a link is missing or appears more than once, go to the relevant page and check, repeating the steps above if necessary.
STEP SEVEN - Change all references to the current year
Use the replace function (ctrl + h) to replace all references to the current year (e.g. 2024) with references to the following year (e.g. 2025).
Then save the document and close.
STEP EIGHT - Update the Excel file with the Sciensano office closure days
If not done so already, update the Sciensano_office_closure_days Excel file.
Public holidays and bridge days for the following year need to be added to the document - following the instructions detailed in the first tab.
This file can be found at the following location: HD_Reports\HD_Service_Level_Report\Input_Files
