Excel中的VBA无法通过PDFCreator在循环后打印
问题描述:
我已经编写了一些代码,用于从选项卡1(1000行数据)的3列中获取信息以在第二个选项卡上填充数据(以基于帐户信息地址等)。代码输入Do While,并在代码继续循环时将PDF发送到队列后发生问题。只有在运行代码时才会出现该错误,而单步执行代码时则不会出现问题。Excel中的VBA无法通过PDFCreator在循环后打印
我在2003年和2007年相似的结果试过这个(2003年将打印3个文件,我已经得到了2007年到打印多达6档)
我也尝试添加手动延迟与
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
和作业后进入打印队列
Sleep 3000
我还添加了一个做,直到循环等待打印作业,深入到零,但没有成功。
完整的代码是:
Sub PlaceData()
Dim accountNumber As String
Dim partyID As String
Dim ClientAddress As String
Dim bRestart As Boolean
Dim totalAccounts As Long
Dim pdfjob As PDFCreator.clsPDFCreator
Dim dataPage As Worksheet
Dim letterPage As Worksheet
Dim CB As Workbook 'CB = ClientBook
Set CB = ThisWorkbook
Set dataPage = CB.Sheets("Data")
Set letterPage = CB.Sheets("Letter")
'will iterate through the account numbers down
therow = 1
'where the loop starts
totalAccounts = dataPage.Cells(Rows.Count, 1).End(xlUp).Row
Do While therow < totalAccounts
therow = therow + 1
'for the form letter
letterPage.Range("F4").FormulaR1C1 = dataPage.Range("A" & therow)
letterPage.Range("F5").FormulaR1C1 = dataPage.Range("C" & therow)
letterPage.Range("B10").FormulaR1C1 = dataPage.Range("B" & therow)
'accountnumber minus one digit for the file name
accountNumber = letterPage.Range("F4").Text
accountNumberShort = Mid(accountNumber, 1, 8)
On Error GoTo EarlyExit
Application.ScreenUpdating = False
Set pdfjob = New PDFCreator.clsPDFCreator
sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
'Check if PDFCreator is already running and attempt to kill the process if so
Do
bRestart = False
Set pdfjob = New PDFCreator.clsPDFCreator
If pdfjob.cStart("/NoProcessingAtStartup") = False Then
'PDF Creator is already running. Kill the existing process
Shell "taskkill /f /im PDFCreator.exe", vbHide
DoEvents
Set pdfjob = Nothing
bRestart = True
End If
Loop Until bRestart = False
With pdfjob
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = sPDFPath
.cOption("AutosaveFilename") = accountNumberShort
.cOption("AutosaveFormat") = 0 ' 0 = PDF
.cClearCache
End With
'Delete the PDF if it already exists
If Dir(sPDFPath & accountNumberShort) = accountNumberShort Then Kill (sPDFPath & accountNumberShort)
'Print the document to PDF
letterPage.PrintOut copies:=1, ActivePrinter:="PDFCreator"
'Wait until the print job has entered the print queue
Do Until pdfjob.cCountOfPrintjobs = 1
DoEvents
Loop
pdfjob.cPrinterStop = False
'Wait until PDF creator is finished then release the objects
Do Until pdfjob.cCountOfPrintjobs = 0
DoEvents
Loop
Loop
'where the loop will end and where the issue is (I think)
'cleanups
Cleanup:
'Release objects and terminate PDFCreator
Set pdfjob = Nothing
Shell "taskkill /f /im PDFCreator.exe", vbHide
On Error GoTo 0
Application.ScreenUpdating = True
Exit Sub
EarlyExit:
'Inform user of error, and go to cleanup section
MsgBox "There was an error encountered. PDFCreator has" & vbCrLf & _
"has been terminated. Please try again.", _
vbCritical + vbOKOnly, "Error"
Resume Cleanup
Set CB = Nothing
Set dataPage = Nothing
Set letterPage = Nothing
Set pdfjob = Nothing
End Sub
感谢您的任何输入或建议,
答
我想补充的正确处置的PDFCreator(PDFC)后,已完成其工作,因为不正确的处理会导致重复运行PDFC(剩余流程,资源等)中的问题。随PDFC一起发运的所有代码样本至少在最少的情况下处置,即它们至少调用clsPDFCreator.cClose()
方法。
例如,检查示例文件C:\Program Files (x86)\PDFCreator\COM\VB6\Sample1\Form1.frm
(如果将PDFC安装到不同的目录中,请调整路径)。代码见样本:
If noStart = False Then
PDFCreator1.cClose ' sending cleanup&exit request to PDFCreator
While PDFCreator1.cProgramIsRunning ' loop while PDFCreator is not found finished
DoEvents
Sleep 100 ' polling interval 100 ms
Wend
End If
Set PDFCreator1 = Nothing
Set pErr = Nothing
Set opt = Nothing
其他运送的样品有时会显示一些额外的技巧,但没有丢失处理部分。
绝不是这是一个解决方案,但在杀死PDFCreator帮助的同时杀死spoolsv.exe? – jon 2012-01-30 23:12:33
@jonlester没有看到帮助我的问题。 spoolsv.exe看起来不会像我看到的一样发生。我想我会试验更多的延迟,因为我认为代码只是在一些非常实验性的调试(那里有一个甲虫)后才允许该进程真正开始 – jamesC 2012-01-31 20:06:19
。我认为我已经发现这些行的主要罪魁祸首,它们一直挂起:Set pdfjob = New PDFCreator.clsPDFCreator If pdfjob.cStart(“/ NoProcessingAtStartup”)= False Then – jamesC 2012-01-31 22:07:41