Formatting export file name in macro

[NickP_DF]

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.

N.

[rubenmarin · Accepted solution]

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

[marcus_sommer]

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

[NickP_DF]

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.

  N.

[NickP_DF]

Thank you, Ruben, for your suggestion 🙂

[marcus_sommer · Accepted solution]

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

[NickP_DF]

...fast & easy 😁

Thank you so much, Marcus.

N.