Excel Waiting for Another Application to Complete an OLE Action


A

Alan

I have some VBA code, shown below, that runs in Excel and invokes
Word and PDFCreator. It is an adaptation of the Word VBA code found
at http://www.excelguru.ca/node/95 adapted for single Word files.

When I run the first Sub listed below, I get the error " Microsoft
Excel is waiting for another application to complete an OLE action."
I went to the Windows Task Manager. Both PDFCreator and Word
(winword.exe) were still running. I killed each of them, but Excel
was still inoperable. I had to kill Excel to stop it, as Ctrl-Break
did not work.

I did search for the error on Google. I founds lots of hits,
but none of them were enlightening to me.

I am using Excel 2007 on Windows Vista.

Any suggestions on what the problem might be, or where to start?

Thanks, Alan

Sub TESTPrintWordToPDF()
PrintWordToPDFCreator ("C:\Users\Alan\Desktop\Link Test.doc")
End Sub

Sub PrintWordToPDFCreator(WordDocPath As String)
Dim pdfjob As PDFCreator.clsPDFCreator
Dim sPDFName As String, sPDFPath As String
Dim pos As Integer, sWordName As String
Dim sPrinter As String
Dim bRestart As Boolean
Dim bBkgrndPrnt As Boolean
Dim WordApp As Word.Application
Dim WordDoc As Word.Document

' Set file and path name
sWordName = FileName(WordDocPath)
pos = InStr(1, sWordName, ".doc")
If (pos = 0) Then
DisplayErrorMessage (sWordName & " is not a Word document")
Exit Sub
Else
sPDFName = Replace(sWordName, ".doc", ".pdf")
sPDFPath = FolderName(WordDocPath)
End If

If Not CheckFileExist(WordDocPath) Then
MsgBox "The file " & WordDocPath & " does not exist"
Exit Sub
End If
'
' Check that the file is not already open ***********
'
Set WordApp = New Word.Application
Set WordDoc = WordApp.Documents.Open(WordDocPath)

'Activate error handling, capture properties and set req'd
settings
On Error GoTo EarlyExit
With WordApp
sPrinter = CStr(.ActivePrinter)
.ActivePrinter = "PDFCreator"
bBkgrndPrnt = .Options.PrintBackground
.Options.PrintBackground = False
.ScreenUpdating = False
End With

'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

'Assign settings for PDF job
With pdfjob
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = sPDFPath
.cOption("AutosaveFilename") = sPDFName
.cOption("AutosaveFormat") = 0 ' 0 = PDF
.cClearCache
End With

'Print document to PDF
WordDoc.PrintOut copies:=1

'Wait until the print job has entered the print queue
Do Until pdfjob.cCountOfPrintjobs = 1
DoEvents
Loop

pdfjob.cPrinterStop = False

'Wait until the file shows up before closing PDF Creator
Do
DoEvents
Loop Until Dir(sPDFPath & sPDFName) = sPDFName
' Close Word document
WordDoc.Close

Cleanup:
'Release objects and terminate PDFCreator
pdfjob.cClose
Set pdfjob = Nothing
Shell "taskkill /f /im PDFCreator.exe", vbHide
On Error GoTo 0
'Reset all application settings to user's original settings
With WordApp
.ScreenUpdating = True
.ActivePrinter = sPrinter
.Options.PrintBackground = bBkgrndPrnt
End With
WordApp.Quit
Set WordApp = Nothing
Set WordDoc = Nothing
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
End Sub
 
Ad

Advertisements

J

Jacob Skaria

Cells(1,1) = CheckBox1.Value & "," & CheckBox2.Value & "," & CheckBox3.Value

If this post helps click Yes
 
Ad

Advertisements

J

Jacob Skaria

Sorry Alan, I have wrongly posted this here..


Jacob Skaria said:
Cells(1,1) = CheckBox1.Value & "," & CheckBox2.Value & "," & CheckBox3.Value

If this post helps click Yes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top