macro ExportEx excel, no columns header...

[dariof]

Hello.

I've a problem with Qlikview and the exporting to Excel via macro with obj.ExportEx.

We have Qlikview Server (11.20 SR17) but the exporting is done by a Windows Server schedule and a cmd with "C:\Program Files\QlikView\Qv.exe" /r "D:\test.qvw" (because exporting it is fired by reload and on QVS there is no other way, at least that I know, to fire the OnReload macro).

The code is simple and it is like this:

ActiveDocument.ClearAll false
ActiveDocument.Fields("DATE").Select "*"
set obj = ActiveDocument.GetSheetObject("CH613")
obj.ExportEx "c:\test.xls", 5

but on the exported Excel there is no header!!!

I read many forum posts about delete the header, that the header is present by default and so on...but I have the opposite problem.

Any idea?

Thanks.

[dariof]

Hi.

I forgot to mention that I set the easteregg ExcelExportMixedAsText to 0 on the client and on the settings.ini for the server.

Moreover, I am trying to check with a button that execute the same macro and I've noticed now that if I change the file type

obj.ExportEx "c:\test.xls", 1

or

obj.ExportEx "c:\test.xls", 2

etc...

and then, without exit or saving, if I put again

obj.ExportEx "c:\test.xls", 5

the columns header are in the file.

Then, if I save the QVW and press the same button again, the columns header are gone.

Maybe it is a bug?

I am becoming mad.

ps: I cannot use Excel.Application because I've no excel on the server.

[Brett_Bleess]

Best I can offer is the API Guide QVW for QlikView:

QlikView API Guide QVW 

Check the APIs available and their properties there, I am not exactly sure to what you are referring otherwise, screenshot would likely be the most helpful.

Regards,
Brett

[dariof]

Ciao.

Thanks, I really appreciated the doc you have attached.

Anyway my problem is that using ExportEx there is no header on the saved file (5=biff/excel format).

If I change something and run the export manually or scheduled for the first time, the header is present, next times there is no header.

On the same Qvw with other graphs the exports save the file with the header.

I think it is a bug of the version we have (11.20 sr17) because I have no other explanations on that behaviour!

ciao

[marcus_sommer]

You could try to call your batch with a different parameter, like:

"C:\Program Files\QlikView\Qv.exe" /l "D:\test.qvw"

ActiveDocument.ClearAll false
ActiveDocument.Fields("DATE").Select "*"
set obj = ActiveDocument.GetSheetObject("CH613")
obj.ExportEx "c:\test.xls", 5

rem ** quit QlikView **
ActiveDocument.GetApplication.Quit

The parameter /l reloads the app and doesn't closed and saved the app - therefore the additionally quit-statement here (for saving you would need a further statement).

Also a trial worth could be to adjust the easter egg settings again to check if it changed the matter.

Further you may consider to export the data to a csv which could be easily be opened with Excel (quite usually is Excel the default-app to open a csv).

Another approach (with your data maybe not easy to implement but generally possible) would be to add your header as values for the very first row in your table.

- Marcus

[Brett_Bleess]

The most current release of 11.20 is SR20, just FYI, so you could pull the latest build and try things there.  I do not believe R&D would likely entertain addressing this defect if that is what it is, so I would probably recommend you consider moving/testing in 12.20 or later in that case to see what you get there.  Hopefully what Marcus presented may help though.

Regards,
Brett

[dariof]

Ciao!

I've already tried to cancel the EasterEgg with no result if I remember well, this is why I have set it again, but anyway I will retry.

Already tried with other formats, like CSV, and also here there is no header... I mean I had the same behaviour of XLS format.

The last solution is to try to manually add the header, but I can't do this with my data on Qlik, probably I can do something external that repair the CSV/XLS.

Tomorrow I will try/retry everything you suggested.

Thank you very much.

[dariof]

Yes I know that there is 12 version, but this is a server where the Qlik installation is not done by me and I do not have enough skill to take the resposability to upgrade it but I am the first person that will probably suggest an upgrade , at least to the last SR of 11 version.

If no suggested options from Marcus will give a result, I will read documentation on server upgrade and will plan the upgrade .

Thanks!

[Brett_Bleess]

Most useful link for you I think:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Server/Content/QV_Server/QlikView-Server/Q...

We are working on some further documentation, but likely will not post until later this month.  Doing the SR update on the existing environment is pretty simply, you pull the installer and run it, it will upgrade in-place, really easy to do, but would recommend stopping the services, run the installer to upgrade things, then restart the services and you should be back in business.  Shout if you have questions, can try to provide further info if needed.

Regards,
Brett

[marcus_sommer]

You wrote that there were situations in which the header was exported. This indicates that's in general working but that there are any obstacles which prevent a correct working - nevertheless you should try the export with a complete new created chart (copy & paste of expressions and similar is ok. but none of any object-properties because you would copy the issue, too if it's object-related).

One of the obstacles might be that object isn't always finished with the calculation and rendering and loose therefore the header. Further impact could come from any other actions like OnChangeSelection or OnChangeVariable or similar - just check for such things and try to disable/bypass them. To make sure that everything is properly calculated add the following lines within your export-routine:

ActiveDocument.ClearAll false
ActiveDocument.GetApplication.WaitForIdle
ActiveDocument.Fields("DATE").Select "*"
ActiveDocument.GetApplication.WaitForIdle
set obj = ActiveDocument.GetSheetObject("CH613")
obj.Restore
ActiveDocument.GetApplication.WaitForIdle
obj.ExportEx "c:\test.xls", 5

- Marcus