Export to PDF with possible/selected values in the filename

[thierrytt1102]

Dear,

I'm using a macro to loop and print a Field Value (Sector). It's working fine; I have a pdf for each Sector named:  France.pdf, Italy.pdf, ...

BUT

I'd would like to add in the name of my pdf the current Year selection.

It's surely not difficult but my knowledge in VBS is weak.

Thank you very much for your help

sub PrintAllPDF

      Dim FieldName
    FieldName = "Sector"


    set mySelections = ActiveDocument.Fields(FieldName).GetPossibleValues ("10000")



    Dim i


    for i = 0 to mySelections.Count - 1




        Dim FieldValue

        FieldValue = mySelections.Item(i).text        
        ActiveDocument.ClearCache
        ActiveDocument.GetApplication.WaitForIdle
        ActiveDocument.Fields(FieldName).Select FieldValue
       ActiveDocument.ClearCache
        ActiveDocument.GetApplication.WaitForIdle

        Print_PDF FieldValue, "Report", "RP01"' 

        ActiveDocument.ClearCache
        ActiveDocument.GetApplication.WaitForIdle

    Next
  msgbox "PDF reports have been printed"  
end sub




Sub Print_PDF(FieldValue,  ReportName, ReportID, fileNameSuffix)


    Dim pdfjob
    Dim sPDFName
    Dim sPDFPath
       Dim out 

    sPDFName = FieldValue
    sPDFPath ="C:\Users\6_Exports"



    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


    ActiveDocument.PrintReport ReportID, "PDFCreator"


    Do Until pdfjob.cCountOfPrintjobs = 1
     ActiveDocument.GetApplication.Sleep 20

    Loop
    pdfjob.cPrinterStop = False


    Do Until pdfjob.cCountOfPrintjobs = 0
     ActiveDocument.GetApplication.Sleep 20
    Loop
    pdfjob.cClose
    Set pdfjob = Nothing


  ActiveDocument.ClearCache
        ActiveDocument.GetApplication.WaitForIdle
End Sub

[marcus_sommer]

You could extend here the filename, for example:

...

FieldValue = mySelections.Item(i).text & "_" & year(now())

...

or if it's also a selection:

...

set myYearSelections = ActiveDocument.Fields(Year).GetPossibleValues ("10000")

...

FieldValue = mySelections.Item(i).text & "_" & mySelections.Item(0).text

...

- Marcus

[thierrytt1102]

Thanks Marcus but if I change the fieldvalue, I 'll have a problem here :

   ActiveDocument.Fields(FieldName).Select FieldValue

it won't find the field value anymore. so it didn't work.

[marcus_sommer]

Then:

....

FieldValue = mySelections.Item(i).text         

ActiveDocument.ClearCache

ActiveDocument.GetApplication.WaitForIdle 

ActiveDocument.Fields(FieldName).Select FieldValue

FieldValue = FieldValue & "_" & year(now())

...

or you used two variables - one for the selection of a fieldvalue and one for the filename.

- Marcus

[thierrytt1102]

Marcus,

Thank you for your answer. it's almost done. I tried you method and it works when you select 1 year. (with the field Year).  But when I select 2 values, I need to have :  France_2015_2016.pdf.

This means I should have smthg like:

vYearValue = myYearSelections.Item(0).text&" "&myYearSelections.Item(1).text

but this won't work if I select 1 year.

is there a function like : concat(distinct Year, '-') ?

Thanks a lot!

[marcus_sommer]

For this you could use a loop within the macro which runs through all possible values - like in your mySelections - and make a string-concatenation like:

myYearSelections = myYearSelections & "_" & mySelections.Item(ii).text

but easier would be to create these value within the gui in a variable:

vYear:

= concat(distinct Year)

and then to query these variable, like:

myYearSelections = ActiveDocument("vYear").GetContent.String

- Marcus

[tamilarasu · Accepted solution]

Hi Thierrytt,

Add this line in your macro script

Year = ActiveDocument.Evaluate("Concat(Distinct Year,'_')")

And change the below line

.cOption("AutosaveFilename") = sPDFName

.cOption("AutosaveFilename") = sPDFName & "_" &  Year

[thierrytt1102]

Thks for your help,

I surely did something wrong but the solution of Tamil worked directly.

thank you