Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
dariof
Contributor II
Contributor II

macro ExportEx excel, no columns header...

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.

 

 

Labels (1)
1 Solution

Accepted Solutions
dariof
Contributor II
Contributor II

Ciao to everyone.

I've tried the suggestions that you gave to me (no updating Qlik server), but the only one that worked was to recreate from scratch the graph!

Now I can export (XLS or CSV) with the header without problems.

Thank you very much for your help.

Dario

View solution in original post

10 Replies
dariof
Contributor II
Contributor II

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
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
dariof
Contributor II
Contributor II

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
MVP & Luminary
MVP & Luminary

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
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
dariof
Contributor II
Contributor II

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
Contributor II
Contributor II

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
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
marcus_sommer
MVP & Luminary
MVP & Luminary

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