topic Re: Macro to delete empty sheets in active workbook in QlikView

Macro to delete empty sheets in active workbook

stevietm — Fri, 15 Nov 2024 23:34:59 GMT

Good day all,

I have an old school macro to export multiple data tables to excel on multiple tabs with multiple names. Once exported I do the normal format etc. etc. What I want to do is loop through all the sheets exported and delete all the sheets that has no data in row A2. This may change based on the selection in the qlik model. I have tried the below where I insert the scrip at the end of the current "export" and "format " sub, but when I run the macro via button - at the end it just jumps back to the macro scrip with no errors. Any help would be greatly appreciated as my macro "VBA" knowledge is not great . Thanks in advance.

Dim Sheets 'As Worksheet

Application.DisplayAlerts = False
For Each Sheets In ActiveWorkbook.Worksheets
If MySheets.Range("A2") = "" Then
Sheets.Delete
End If
Next
Application.DisplayAlerts = True

Also I know this is an old school solution to Nprinting and Nprinting would be the way to go, but our company will not invest in Nprinting for now. Maybe in the future but for now I need the alternative solution. Thanks 🙂

Re: Macro to delete empty sheets in active workbook

marcus_sommer — Mon, 23 Nov 2020 11:10:25 GMT

The VBS from Qlik is quite near to the VBA from Excel but it's not the same. In this sense I'm not sure that you could simply call the Excel instance with application and using Excel features like ActiveWorkbook or similar. This means you should specify the calls explicitly. Anywhere within the macro you have created the instance and the workbook and you should use their references, something like:

...
set appExcel = createobject(...)
...

...
appExcel.DisplayAlerts = False
...

Such approach may not always necessary but it avoids some problems.

Further you name the sheets once Sheets and once MySheets which is probably not correct.

Beside this you may bypass the whole deleting-stuff if you would skip the export of empty tables. For this you could query how many rows/columns the Qlik object has but I think it may be easier to count the number of available dimensions-values of your objects within a variable.

Many good examples and some explanations to the Qlik macros could you find within the APIGuide.qvw within your Qlik install-folder by automation.

- Marcus

Re: Macro to delete empty sheets in active workbook

stevietm — Mon, 23 Nov 2020 11:28:59 GMT

Hi Marcus,

Thanks for the reply, The "Sheet and MySheet" is just a typo, sorry about that.

The reason why it exports empty table is because I have 10+ companies. One user can log in with one company and they might not have data for that table but the macro just exports the object. Will go check out APIGuide.qvw thanks.