topic Re: Macro Date in QlikView

Macro Date

realpixel — Wed, 26 Feb 2014 11:18:21 GMT

Hello,

It’s a part of macro to generate a report in PDF

Sub Dagrapport

ActiveDocument.reload

ActiveDocument.save

vReport = "RP01" 'Set report
vName = "Rapport_Synthese_Sarissa" 'Name of output pdf
ActiveDocument.PrintReport(vReport), "Bullzip PDF Printer", false 'Printreport
reportFile = "c:\temp\" & vName &"_" & Date & ".pdf" 'Setting outputname
MyPrintPDFWithBullZip(reportFile) 'Call pdf printer
ActiveDocument.GetApplication.Sleep 5000
zendDagrapport
End sub

But when I run this macro, I have the following message.

It’s seem that the problem is due to the format of the date DD/MM/YYYY

I use in my script value “& Date &” but this function take the date at format DD/MM/YYYY, if I remove this function, it’s work perfectly.

In need to have the date at the format DDMMYYYY, there are an issue to solve my problem?

Re: Macro Date

Anonymous — Wed, 26 Feb 2014 20:56:58 GMT

See this with some changes in bold:

Sub Dagrapport
dim M

if len(month(Date))=1 then
M = "0"&month(Date)
else
M = month(Date)
end if

ActiveDocument.reload

ActiveDocument.save

vReport = "RP01" 'Set report
vName = "Rapport_Synthese_Sarissa" 'Name of output pdf
ActiveDocument.PrintReport(vReport), "Bullzip PDF Printer", false 'Printreport
reportFile = "c:\temp\" & vName &"_" & day(Date) & M & year(Date) & ".pdf" 'Setting outputname
MyPrintPDFWithBullZip(reportFile) 'Call pdf printer
ActiveDocument.GetApplication.Sleep 5000
zendDagrapport
End sub

Regards,

Michael

Re: Macro Date

Clever_Anjos — Wed, 26 Feb 2014 21:02:23 GMT

try changing

=reportFile = "c:\temp\" & vName &"_" & Date & ".pdf" 'Setting outputname

reportFile = "c:\temp\" & vName &"_" & replace(cstr(Date),"/","") & ".pdf" 'Setting outputname

Re: Macro Date

Anonymous — Wed, 26 Feb 2014 21:19:02 GMT

Clever Anjos,

You solution will most likely work for realpixel judjing by the format of the date in the error message. It doesn't work for me because my Windows settings for the date format is M/D/yyyy, hence the result is 2262014. Had to do some formatting...

Regards,

Michael

Re: Macro Date

realpixel — Thu, 27 Feb 2014 10:27:40 GMT

Hello Clever,

Thank you for your assistance, it's works fine, but I have a last question.

I must to send this report by email.

The report generate is named "Rapport_Synthese_Sarissa - _27022014.pdf"

I use the code under, If I put the name "Rapport_Synthese_Sarissa - _27022014.pdf" in the function "objEmail.AddAttachment" it's work perfectly

But my problem that this report is reloaded every day, and the file name will change daily depending on the date (tomorrow the file will be Rapport_Synthese_Sarissa - _28022014.pdf)

How to add in function objEmail.AddAttachment the name and the date of the file?

I try this command

objEmail.AddAttachment "c:\temp\" & vName &"_" & replace(cstr(Date),"/","") & ".pdf" ' Attachement

and also that

objEmail.AddAttachment "c:\temp\" & vName &".pdf" ' Attachement

But it's not work.

objEmail.To = "tes@1.2.3" 'Email Recipient

objEmail.From = "qlikview.report@1.2.3" 'Email Sender

objEmail.Subject = "TEST1" ' Subject

objEmail.TextBody = "TEST 2" 'Text Body

'objEmail.AddAttachment "c:\temp\Rapport_Synthese_Sarissa.pdf" ' Attachement

'objEmail.AddAttachment "c:\temp\" & vName &"_" & replace(cstr(Date),"/","") & ".pdf" ' Attachement

objEmail.AddAttachment "c:\temp\" & vName &".pdf" ' Attachement

objEmail.Send

Set objFlds = Nothing

Set objConf = Nothing

Set objEmail = Nothing

' msgbox ("Test Mail Sent")

end function

Re: Macro Date

Clever_Anjos — Thu, 27 Feb 2014 19:51:22 GMT

Try

reportFile = "c:\temp\" & vName &"_" & replace(cstr(Date),"/","") & ".pdf"

objEmail.AddAttachment reportFile