Macro Date

[realpixel]

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?

[Anonymous]

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

[Clever_Anjos]

try changing

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

to

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

[Anonymous]

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

[realpixel]

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

[Clever_Anjos · Accepted solution]

Try

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

objEmail.AddAttachment  reportFile