topic Re: Formatting export file name in macro in QlikView

Formatting export file name in macro

NickP_DF — Sat, 21 Nov 2020 09:33:15 GMT

Hi guys,

I need to create an exported file by using a macro and I'd like that its format name would contain the current date/time with this format:

ExportAS_YYYYMMDD_HHMMSS.csv

My line command is:

sObject.Export "ExportAS_" & Day(Date()) & Month(Date()) & Year(Date()) & "_" & Hour(Time()) & Minute(Time()) & Second(Time()) & ".csv", ";"

but the length of every part of the file name should unwanted change (eg. at 9 o'clock it should be ExportAS_20201121_93000 instead of ExportAS_20201121_093000 ) .

Could someone help me, please?

Thanks.

Re: Formatting export file name in macro

rubenmarin — Sat, 21 Nov 2020 10:57:40 GMT

Hi @NickP_DF, maybe there is a better way but as a solution you can crete a function to add the zero:

function CheckZero(value) if value<10 then CheckZero="0"&value else CheckZero=value end if end function

and call this function in each aprt that can have a precedent zero:

CheckZero(Day(Date())) & CheckZero(Month(Date())) & Year(Date()) & "_" & CheckZero(Hour(Time())) & CheckZero(Minute(Time())) & CheckZero(Second(Time()))

Re: Formatting export file name in macro

marcus_sommer — Mon, 23 Nov 2020 13:54:22 GMT

You could consider to do this within a variable in the UI with something like: timestamp(now(), 'YourFormat') and calling then this variable within the macro instead of struggling with the limited vbs date/formating-features.

- Marcus

Re: Formatting export file name in macro

NickP_DF — Mon, 23 Nov 2020 17:34:55 GMT

Hi Marcus,

I'm gonna grab your suggestion, but there's something which I miss:

I made two variables:

vExportDay

Timestamp(Now(), 'DDMMYYYY')

vExportTime

Timestamp(Now(), 'HHmmSS')

In the macro I've substituted the previous instruction with this one:

sObject.Export ActiveDocument.GetVariable("vAS400DataFolder").getContent.string & "ExportAS_" & ActiveDocument.GetVariable("vExportDay").getContent.string & "_" & ActiveDocument.GetVariable("vExportTime").getContent.string & ".csv", ";"

But the result is

ExportAS_Timestamp(Now(), 'DDMMYYYY')_Timestamp(Now(), 'HHmmSS').csv

🙄

Pls, forgive me for my clumsiness...

Thanks.

Re: Formatting export file name in macro

NickP_DF — Mon, 23 Nov 2020 17:37:08 GMT

Thank you, Ruben, for your suggestion 🙂

Re: Formatting export file name in macro

marcus_sommer — Tue, 24 Nov 2020 07:09:52 GMT

These variables needs to be evaluated within the UI which is enable by adding an equal-sign before, like:

vExportDay

=Timestamp(Now(), 'DDMMYYYY')

vExportTime

=Timestamp(Now(), 'HHmmSS')

- Marcus

Re: Formatting export file name in macro

NickP_DF — Wed, 25 Nov 2020 17:00:30 GMT

...fast & easy 😁

Thank you so much, Marcus.