Have been tasked with fixing something a previous coder supposedly had working that is now no longer working for some reason. We're using Excel2002 and I have no familiarity with Adobe. The following code is failing on the line in bold with error msg that says "Active X component can't create object (429)".
There was no reference to Adobe in the workbook, but even when I added a reference to 'Adobe Acrobat 8.0 Type Library' the error persists. From what I gather, after the PDF is attached to the workbook [elsewhere in the code and working] a new worksheet is to be created and graphics from the PDF are to be copied and pasted into the new worksheet [the copy/paste is what is not working].
Any help or link to help is appreciated.
There was no reference to Adobe in the workbook, but even when I added a reference to 'Adobe Acrobat 8.0 Type Library' the error persists. From what I gather, after the PDF is attached to the workbook [elsewhere in the code and working] a new worksheet is to be created and graphics from the PDF are to be copied and pasted into the new worksheet [the copy/paste is what is not working].
Any help or link to help is appreciated.
Rich (BB code):
Public Function iAddPDFWS(FileName As String, WSNumber As Integer, AttchID As String) As Integer
On Error GoTo ErrHandler
iAddPDFWS = 0
Dim AcApp As Object ' Acrobat.CAcroApp
Dim AcADoc As Object ' Acrobat.CAcroAVDoc
Dim AcPView As Object ' Acrobat.CAcroAVPageView
Dim AcPDoc As Object ' Acrobat.CAcroPDDoc
Dim lRslt As Long, WkSh As Worksheet, LastWkSh As String
ActiveWorkbook.Unprotect
Application.EnableCancelKey = xlDisabled
Set AcApp = CreateObject("AcroExch.App", "") <----- this line highlighted by error
Set AcADoc = CreateObject("AcroExch.AVDoc", "")
lRslt = AcADoc.Open(FileName, "")
Set AcPView = AcADoc.GetAVPageView
Set AcPDoc = AcADoc.GetPDDoc
For lRslt = 1 To AcPDoc.GetNumPages
Set WkSh = AddTheWkSh(FileName, WSNumber, AttchID, CInt(lRslt), LastWkSh)
AcPView.ScrollTo 1, 1
AcPView.GoTo lRslt - 1
AcApp.SetActiveTool "SelectGraphics", -1
AcApp.MenuItemExecute "Edit"
AcApp.MenuItemExecute "SelectAll"
AcApp.MenuItemExecute "Edit"
AcApp.MenuItemExecute "Copy"
WkSh.Paste WkSh.Range(cnPASTE_HERE), False
WkSh.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Set WkSh = Nothing
Next lRslt
iAddPDFWS = AcPDoc.GetNumPages
AcApp.Hide
AcApp.CloseAllDocs
AcApp.Exit
Worksheets(LastWkSh).Activate '"System Suitability - " & WSNumber & " (page 1)").Activate
Set AcPView = Nothing
Set AcPDoc = Nothing
Set AcADoc = Nothing
Set AcApp = Nothing
ErrHandler:
If Err.Number <> 0 Then
modGeneral.StdError Err, "modAddPDFWS.iAddPDFWS"
End If
ActiveWorkbook.Protect Structure:=True
Application.EnableCancelKey = xlInterrupt
End Function