By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,949 Members | 946 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,949 IT Pros & Developers. It's quick & easy.

Problem with an Excel-VBA Script using PDF Creator

P: 1
Hello Everyone,

Not only am I new to the forum I am entirely new to programming in VBA. I am having a problem getting some sample code working. To give you background on what I want it to do:

I need the script to print an active worksheet to PDF Creator and assign the WorkSHEET name as the filename.

So far I can get the for to be called and then a get a dreaded Runtime Error:

Runtime Error '91':
Object variable or With block variable not set.

Here is my code:
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
  3. Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
  4.  
  5. Private WithEvents PDFCreator1 As PDFCreator.clsPDFCreator
  6.  
  7. Private ReadyState As Boolean, DefaultPrinter As String
  8.  
  9. Private Sub CommandButton1_Click()
  10.  Dim outName As String, i As Long
  11.  If InStr(1, ActiveSheet.Name, ".", vbTextCompare) > 1 Then
  12.    outName = Mid(ActiveSheet.Name, 1, InStr(1, ActiveSheet.Name, ".", vbTextCompare) - 1)
  13.   Else
  14.    outName = ActiveSheet.Name
  15.  End If
  16.  CommandButton1.Enabled = False
  17.  If OptionButton1.Value = True Then
  18.   With PDFCreator1
  19.     .cOption("UseAutosave") = 1
  20.     .cOption("UseAutosaveDirectory") = 1
  21.     .cOption("AutoSaveDirectory") = 1
  22.     .cOption("AutoSaveFilename") = 1
  23.     .cOption("AutosaveFormat") = 0 ' 0 = PDF
  24.     .cOption("PDFUseSecurity") = 0
  25.     .cOption("PDFOwnerPass") = 0
  26.     .cOption("PDFOwnerPasswordString") = "mypass"
  27.     .cOption("PDFDisallowPrinting") = 0                        ' 0 = PDF
  28.    .cClearCache
  29.   End With
  30.   For i = 1 To Application.Sheets.Count
  31.    Application.Sheets(i).PrintOut Copies:=1, ActivePrinter:="PDFCreator"
  32.   Next i
  33.   Do Until PDFCreator1.cCountOfPrintjobs = Application.Sheets.Count
  34.    DoEvents
  35.    Sleep 1000
  36.   Loop
  37.   Sleep 1000
  38.   PDFCreator1.cCombineAll
  39.   Sleep 1000
  40.   PDFCreator1.cPrinterStop = False
  41.  End If
  42.  If OptionButton2.Value = True Then
  43.   With PDFCreator1
  44.     .cOption("UseAutosave") = 1
  45.     .cOption("UseAutosaveDirectory") = 1
  46.     .cOption("AutoSaveDirectory") = 1
  47.     .cOption("AutoSaveFilename") = 1
  48.     .cOption("AutosaveFormat") = 0 ' 0 = PDF
  49.     .cOption("PDFUseSecurity") = 0
  50.     .cOption("PDFOwnerPass") = 0
  51.     .cOption("PDFOwnerPasswordString") = 0
  52.     .cOption("PDFDisallowPrinting") = 0                        ' 0 = PDF
  53.    .cClearCache
  54.   End With
  55.   ActiveSheet.PrintOut Copies:=1, ActivePrinter:="PDFCreator"
  56.   Do Until PDFCreator1.cCountOfPrintjobs = 1
  57.    DoEvents
  58.    Sleep 1000
  59.   Loop
  60.   Sleep 1000
  61.   PDFCreator1.cPrinterStop = False
  62.  End If
  63. End Sub
  64.  
  65. Private Sub PrintPage(PageNumber As Integer)
  66.  Dim cPages As Long
  67.  cPages = Selection.Information(wdNumberOfPagesInDocument)
  68.  If PageNumber > cPages Then
  69.   MsgBox "This document has only " & cPages & " pages!", vbExclamation
  70.  End If
  71.  DoEvents
  72.  ActiveDocument.PrintOut Background:=False, Range:=wdPrintFromTo, From:=CStr(PageNumber), To:=CStr(PageNumber)
  73.  DoEvents
  74. End Sub
  75.  
  76. Private Sub PDFCreator1_eError()
  77.  AddStatus "ERROR [" & PDFCreator1.cErrorDetail("Number") & "]: " & PDFCreator1.cErrorDetail("Description")
  78. End Sub
  79.  
  80. Private Sub PDFCreator1_eReady()
  81.  AddStatus "File'" & PDFCreator1.cOutputFilename & "' was saved."
  82.  PDFCreator1.cPrinterStop = True
  83.  CommandButton1.Enabled = True
  84. End Sub
  85.  
  86. Private Sub UserForm1_Initialize()
  87.  If Len(ActiveSheet.Path) = 0 Then
  88.   MsgBox "Please save the document first!", vbExclamation
  89.   End
  90.  End If
  91.  Set PDFCreator1 = New clsPDFCreator
  92.  With PDFCreator1
  93.   If .cStart("/NoProcessingAtStartup") = False Then
  94.    CommandButton1.Enabled = False
  95.    AddStatus "Can't initialize PDFCreator."
  96.    Exit Sub
  97.   End If
  98.  End With
  99.  AddStatus "PDFCreator initialized."
  100. End Sub
  101.  
  102. Private Sub AddStatus(Str1 As String)
  103.  With TextBox1
  104.   If Len(.Text) = 0 Then
  105.     .Text = Now & ": " & Str1
  106.    Else
  107.     .Text = .Text & vbCrLf & Now & ": " & Str1
  108.   End If
  109.   .SelStart = Len(.Text)
  110.   .SetFocus
  111.  End With
  112. End Sub
  113.  
  114. Private Sub UserForm1_QueryClose(Cancel As Integer, CloseMode As Integer)
  115.  PDFCreator1.cClose
  116.  Set PDFCreator1 = Nothing
  117.  Sleep 250
  118.  DoEvents
  119. End Sub
I've bolded out the part that is giving me the error.

If you can help me with any insight into why this is happening I would greatly appreciate it...I don't know if that's incentive enough, but hey, you'll be helping NASA!!! :)
Apr 10 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.