This information will assist you to create a Macro in Excel to pick up the Report Titles and use them for the Sheet Names in Excel after you have exported a Bundle from Calxa.
Note 💬 This information is provided as is, with no warranty on fitness for purpose or reliability
Set up Calxa Bundle
Please note that for this to work, you will need to have the Report Bundle in Calxa set as Optimised for Export to Excel:
Set up Excel for the Macro
To set up the Excel to run the Macro, you will need to store it in a Personal.xlsb file and set it to be accessed from any spreadsheet that you open.
See Microsoft documentation on how to add the Developer tab in Excel
Create a Personal.xlsb macro workbook, see Microsoft help on how to create that
This Macro assumes that report names in Calxa are valid for sheet names in Excel.
The Macro will fail if invalid characters are included
Edit Report names: Bundle > More Options > Edit Properties >Edit Report Title
Add this instruction to the Module created in your Personal.xlsb file
Copy and paste the following into the Module:
Dim ws As Worksheet
Dim n As Integer
Dim newName As String
Dim break As String
break = "—" 'this is the separator between the report title and the organisation name
For Each ws In Worksheets
n = InStr(ws.Range("A1").Text, break)
If n > 0 Then
newName = Left(ws.Range("A1"), n - 1)
Else
newName = ws.Range("A1")
End If
If Len(newName) > 0 Then
ws.Name = newName
End If
Next ws
End Sub
Run your Bundle and Export to Excel as normal
Click the Developer tab
Macros
Select the Macro and Run
Tab names will update with the Report Title in cell A1 on each sheet