Start a New Discussion Ask a New Question

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Excel VBA code and PDFCreator Settings
  • I have tried to use VBA code to generate PDFCreator PDFs, in lieu of native Excel 2010 PDF output.   The workbook values, used in the code, below, work for the native excel PDF, but the files are huge, and I would like the additional manipulations that PDFCreator has.

    I have PDFCreator set to autosave, and have a default and another profile.  These output to a known directory, using a file name constructed according to the stored convention.  Thus, I can print, manually, to PDFCreator, and get an expected file name, in an expected directory

    When I used code to save a file, using what appears to be commonly referenced code that I've found, the options settings in VBA appear to be ignored, and the output is based on the profiles -- the file name, for example, is based on the profile stored, rather than my VBA coding. 

    My first guess is that something about multiple profiles in the new PDFCreator versions, or the existence of profiles, breaks the code.  I am aware that settings aren't supposed to stick in the ^O Settings in PDFCreator, but aren't the settings in code supposed to assign setting values to the PDFCreator Class printer....?

    What am I missing?  Any suggestions?  [bolded code/lines, below, are the ones that don't seem to work...though I haven't track down which other settings don't stick]

    =====================

    Dim PdfCreator1 As PDFCreator.clsPDFCreator Set PdfCreator1 = New PDFCreator.clsPDFCreator  'to PDF printer  -- THIS code works, as plain old output to the PDFCreator Printer
    '   Worksheets("BILL").PrintOut _
    '      ActivePrinter:= _
    '         Worksheets("Pref").Range("c8").Value _
    '         , Copies:=1, Collate:=True
    ' But, if I use this code, it does not work     
      With PdfCreator1
       MsgBox .cOption("autosavedirectory") 'this puts out a blank, and when put lower in the code, it put out expected value
        .cOption("UseAutosave") = 1
       .cOption("UseAutosaveDirectory") = 1
        .cOption("AutosaveDirectory") = "C:\Users\abcd\Desktop\" 'yet, this doesn't 'stick' when executed
       .cOption("AutosaveFilename") = "Bill--" _
            & ActiveWorkbook.Names.Item("xlsFileAndType").RefersToRange.Value

       .cOption("AutosaveFormat") = 0                            ' 0 = PDF
       .cClearCache
      End With

      '         ActiveSheet.PrintOut Copies:=1, ActivePrinter:="_PDFCreator"
         Worksheets("BILL").PrintOut _
          ActivePrinter:= _
             Worksheets("Pref").Range("c8").Value _
             , Copies:=1, Collate:=True 'evalutes to "_PDFCreator" , the name of my printer

    etc...