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:
-
Option Explicit
-
-
Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
-
-
Private WithEvents PDFCreator1 As PDFCreator.clsPDFCreator
-
-
Private ReadyState As Boolean, DefaultPrinter As String
-
-
Private Sub CommandButton1_Click()
-
Dim outName As String, i As Long
-
If InStr(1, ActiveSheet.Name, ".", vbTextCompare) > 1 Then
-
outName = Mid(ActiveSheet.Name, 1, InStr(1, ActiveSheet.Name, ".", vbTextCompare) - 1)
-
Else
-
outName = ActiveSheet.Name
-
End If
-
CommandButton1.Enabled = False
-
If OptionButton1.Value = True Then
-
With PDFCreator1
-
.cOption("UseAutosave") = 1
-
.cOption("UseAutosaveDirectory") = 1
-
.cOption("AutoSaveDirectory") = 1
-
.cOption("AutoSaveFilename") = 1
-
.cOption("AutosaveFormat") = 0 ' 0 = PDF
-
.cOption("PDFUseSecurity") = 0
-
.cOption("PDFOwnerPass") = 0
-
.cOption("PDFOwnerPasswordString") = "mypass"
-
.cOption("PDFDisallowPrinting") = 0 ' 0 = PDF
-
.cClearCache
-
End With
-
For i = 1 To Application.Sheets.Count
-
Application.Sheets(i).PrintOut Copies:=1, ActivePrinter:="PDFCreator"
-
Next i
-
Do Until PDFCreator1.cCountOfPrintjobs = Application.Sheets.Count
-
DoEvents
-
Sleep 1000
-
Loop
-
Sleep 1000
-
PDFCreator1.cCombineAll
-
Sleep 1000
-
PDFCreator1.cPrinterStop = False
-
End If
-
If OptionButton2.Value = True Then
-
With PDFCreator1
-
.cOption("UseAutosave") = 1
-
.cOption("UseAutosaveDirectory") = 1
-
.cOption("AutoSaveDirectory") = 1
-
.cOption("AutoSaveFilename") = 1
-
.cOption("AutosaveFormat") = 0 ' 0 = PDF
-
.cOption("PDFUseSecurity") = 0
-
.cOption("PDFOwnerPass") = 0
-
.cOption("PDFOwnerPasswordString") = 0
-
.cOption("PDFDisallowPrinting") = 0 ' 0 = PDF
-
.cClearCache
-
End With
-
ActiveSheet.PrintOut Copies:=1, ActivePrinter:="PDFCreator"
-
Do Until PDFCreator1.cCountOfPrintjobs = 1
-
DoEvents
-
Sleep 1000
-
Loop
-
Sleep 1000
-
PDFCreator1.cPrinterStop = False
-
End If
-
End Sub
-
-
Private Sub PrintPage(PageNumber As Integer)
-
Dim cPages As Long
-
cPages = Selection.Information(wdNumberOfPagesInDocument)
-
If PageNumber > cPages Then
-
MsgBox "This document has only " & cPages & " pages!", vbExclamation
-
End If
-
DoEvents
-
ActiveDocument.PrintOut Background:=False, Range:=wdPrintFromTo, From:=CStr(PageNumber), To:=CStr(PageNumber)
-
DoEvents
-
End Sub
-
-
Private Sub PDFCreator1_eError()
-
AddStatus "ERROR [" & PDFCreator1.cErrorDetail("Number") & "]: " & PDFCreator1.cErrorDetail("Description")
-
End Sub
-
-
Private Sub PDFCreator1_eReady()
-
AddStatus "File'" & PDFCreator1.cOutputFilename & "' was saved."
-
PDFCreator1.cPrinterStop = True
-
CommandButton1.Enabled = True
-
End Sub
-
-
Private Sub UserForm1_Initialize()
-
If Len(ActiveSheet.Path) = 0 Then
-
MsgBox "Please save the document first!", vbExclamation
-
End
-
End If
-
Set PDFCreator1 = New clsPDFCreator
-
With PDFCreator1
-
If .cStart("/NoProcessingAtStartup") = False Then
-
CommandButton1.Enabled = False
-
AddStatus "Can't initialize PDFCreator."
-
Exit Sub
-
End If
-
End With
-
AddStatus "PDFCreator initialized."
-
End Sub
-
-
Private Sub AddStatus(Str1 As String)
-
With TextBox1
-
If Len(.Text) = 0 Then
-
.Text = Now & ": " & Str1
-
Else
-
.Text = .Text & vbCrLf & Now & ": " & Str1
-
End If
-
.SelStart = Len(.Text)
-
.SetFocus
-
End With
-
End Sub
-
-
Private Sub UserForm1_QueryClose(Cancel As Integer, CloseMode As Integer)
-
PDFCreator1.cClose
-
Set PDFCreator1 = Nothing
-
Sleep 250
-
DoEvents
-
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!!! :)
