COM Object References?

My first post here - boy I hope that word wrap goes well.  Now a line just
continues off into the gray!  (IE 9 in a VM).

Is there more detailed help available for using the COM object other than the lists for the class.

I'm working in Excel VBA and want to output portions of sheets as JPEG file and not as PDF.

Would really like to get more details on parameters, and even recommended sequences of calls to make to:

save the current active printer so I can restore that later,
initialize the PDFCreator printer (I think I have that from Excel Forms demo)
Print section/selection as JPEG
"kill off" the PDFCreator gracefully
Restore the original active printer as the active printer if needed.

Any help at all is very much appreciated.  As payback, I could make any developed code available - I'm not bad at coding, been doing it for 30 yrs, and have the privilege of currently being a Microsoft MVP in their Excel group - so it might actually turn out to be something others could use?


Alright, answered part of my question by trial and error:

.cOption("AutosaveFormat") = 2 ' 0=PDF, 1=PNG, 2=JPEG

But I have a couple of added problems/questions:

#1 - when I single step through the code I have, which is basically out of the UserForm example, modified to run from a button on a worksheet, it all works fine and I get the file created.  But if I just execute the code normally, NO FILE IS GENERATED - seems to be a timing issue but I don't know where I should try to slow things down to let PDFCreator actually do the job.

#2 - I need to change the paper size to a really wide sheet, there are over 120 columns of information I need to capture in the graphic, and with other tools similar, I've found I can need a sheet that is set up like 4-feet wide.  Yeah, outrageous, but it's what I need and have found to work with other pdf printers.

This is all being done as a donated project which will be made available to many organizations and I can't afford to donate the "for $$" products that I've tested and found to work for the output needed.  I was happy to find that PDFCreator is now able to output jpg files, now just hoping I can get it to work the way we need it to.

Thanks again, your help with this is greatly appreciated.


in order to change the paper size to a custom one, you wil need to create that custom size in the Windows Print Management and then assign it to the PDFCreator printer.
Will have to ask a dev about the timing problem, could you post your code here or email it to us?



Here is the code that I've been trying to use - perhaps you can tell me where my attempted adaptation has gone off into the ethernet.  As I mentioned earlier - code in your userform works very well, its my adapted code here that has to be single-stepped through to get an output file.

It almost becomes a curiousity thing now - since we may have decided to go with a .html file output of the one worksheet in question.  But I'm always ready to learn something new.

Thank you


Option Explicit

Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)

' Add a reference to PDFCreator
'notice that I am not using WithEvents now.  Creates an error
'outside of a class module anyhow.

'Private WithEvents PDFCreator1 As PDFCreator.clsPDFCreator

Private PDFCreator1 As Object

Private ReadyState As Boolean, DefaultPrinter As String
Private pdfStarted As Boolean

Sub PrintToPDFCreator()
'This is the main routine that would be called when
'I need to create a JPEG file of the active sheet
'Single step (F8) through the code and it works,
'let it just execute and no error is generated, but neither is the output file
  Dim outName As String, i As Long
  If Not pdfStarted Then
  End If
  If InStr(1, ActiveWorkbook.Name, ".", vbTextCompare) > 1 Then
    outName = Mid(ActiveWorkbook.Name, 1, InStr(1, ActiveWorkbook.Name, ".", vbTextCompare) - 1)
    outName = ActiveWorkbook.Name
  End If
'only need to output the current Active sheet, never the entire workbook
  Sleep 1000
  With PDFCreator1
    .cOption("UseAutosave") = 1
    .cOption("UseAutosaveDirectory") = 1
    .cOption("AutosaveDirectory") = ActiveWorkbook.Path
    Debug.Print outName & "-" & ActiveSheet.Name
    .cOption("AutosaveFilename") = outName & "-" & ActiveSheet.Name
    '.cOption("AutosaveFormat") = 0                            ' 0 = PDF
    '.cOption("AutosaveFormat") = 1                            ' 1 = PNG
    .cOption("AutosaveFormat") = 2                            ' 2 = JPEG (.jpg)
  End With
  ActiveSheet.PrintOut Copies:=1, ActivePrinter:="PDFCreator"
  Sleep 1000
  Do Until PDFCreator1.cCountOfPrintjobs = 1
    Sleep 1000
  Sleep 1000
  PDFCreator1.cPrinterStop = False
End Sub

Private Sub PDFCreator1_eError()
  AddStatus "ERROR [" & PDFCreator1.cErrorDetail("Number") & "]: " & _
End Sub

Private Sub PDFCreator1_eReady()
 AddStatus "File'" & PDFCreator1.cOutputFilename & "' was saved."
 PDFCreator1.cPrinterStop = True
End Sub

Sub StartPDFPrinter()
 If Len(ActiveWorkbook.Path) = 0 Then
  MsgBox "Please save the document first!", vbExclamation
 End If
 Set PDFCreator1 = New clsPDFCreator
 With PDFCreator1
  If .cStart("/NoProcessingAtStartup") = False Then
   AddStatus "Can't initialize PDFCreator."
   Exit Sub
  End If
 End With
 AddStatus "PDFCreator initialized."
 pdfStarted = True
End Sub

Private Sub AddStatus(Str1 As String)
 Sheet1.Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Now() & ":" & Str1
End Sub

Private Sub ClosePDFPrinter()
 Set PDFCreator1 = Nothing
 Sleep 250
 pdfStarted = False
 AddStatus "PDFCreator Closed"
End Sub