Max Value as part of the name of a file

[inigoelv]

Hi:

I have a macro which works very well to print reports as pdf; but i would like in the name of the file generated to introduce the maximun value of a field name "MonthYear" : sPDFName = ReportName & " - " & FieldValue& " - " & MonthYear

Per example if in a report I filter by January2014, February2014 and March2014 the MonthYear will be March2014.

The code is:

Sub PrintReport()

Dim FieldName

FieldName = "Supplier Description"

Dim mySelections

set mySelections = _

ActiveDocument.Fields(FieldName).GetPossibleValues

Dim i

for i = 0 to mySelections.Count - 1

Dim FieldValue

FieldValue = mySelections.Item(i).text

ActiveDocument.Fields(FieldName).Select FieldValue

Print_PDF FieldValue, "My Report", "RP05"

Next

End Sub

Sub Print_PDF(FieldValue, ReportName, ReportID)

' This is mostly reference code from the

' PDF Creator documentation

' Designed for early bind, set reference to PDFCreator

Dim pdfjob

Dim sPDFName

Dim sPDFPath

'/// Change the output file name here! ///

sPDFName = ReportName & " - " & FieldValue

sPDFPath = "C:\PDFReports"

Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")

With pdfjob

If .cStart("/NoProcessingAtStartup") = False Then

If .cStart("/NoProcessingAtStartup", True) = _

False Then

Exit Sub

End if

.cVisible = True

End If

.cOption("UseAutosave") = 1

.cOption("UseAutosaveDirectory") = 1

.cOption("AutosaveDirectory") = sPDFPath

.cOption("AutosaveFilename") = sPDFName

.cOption("AutosaveFormat") = 0 ' 0 = PDF

.cClearCache

End With

' Print the QlikView Report

ActiveDocument.PrintReport ReportID, "PDFCreator"

' Wait until the print job has entered the print queue

Do Until pdfjob.cCountOfPrintjobs = 1

ActiveDocument.GetApplication.Sleep 20

' in VBScript use WScript.Sleep(20)

Loop

pdfjob.cPrinterStop = False

' Wait until PDF creator is finished

' then release the objects

Do Until pdfjob.cCountOfPrintjobs = 0

ActiveDocument.GetApplication.Sleep 20

Loop

pdfjob.cClose

Set pdfjob = Nothing

End Sub

Thanks in advance.

[shane_spencer]

use the follwing to get the Maxium MonthYear date:

Let max(MonthYear) = MaxMY

Then use the variable $(MaxMY) when saving the report.

[inigoelv]

Hi Shane:

Could you insert it in the code I supplied?

I have not very experience in Visual Basic.

What I would like is when the pdf will be exported the name of the file will be like:

SupplierEvaluation-Supplier A-March2014.pdf

Best regards,

[shane_spencer]

The following would be part of the QlikView script. Insert it after the main data load.

Let max(MonthYear) = MaxMY

Not sure exactly how you'd use the variable in a VB macro.

[shane_spencer]

I think this topic describes something similar Save export file name with variable input