How to remove a PDF Object that is embedded in excel

[abowers]

I have a pdf form attached to a spreadsheet. Currently I am able to grab a pdf from the computer within vba, add user submitted text and other things and place it into the pdf and then save it in a new location. This program will be placed on multiple computers. I want the excel file to host this pdf and then be able to extract it from a worksheet, edit it with the submitted text and then save it to a harddrive location. Any ideas??

currently this is what I have.

Public Sub CommandButton6_Click()
Dim RevReqDate As String
RevReqDate = Date


'Check if output directory exists and if not create it


If Len(Dir("c:\Matrix Auto Forms", vbDirectory)) = 0 Then
MkDir "c:\Matrix Auto Forms"
End If




Dim FileNm, gApp, avDoc, pdDoc, jso




If OptionButton2.Enabled Then
FileNm = c:\Matrix Auto Forms\P053220 'Maintenance Form ' I want this to be the document attached to spreadsheet 4 (its called object1 in excel)
Else
FileNm = "c:\Matrix Auto Forms\P053220-222.pdf" 'Operations Form 'This will be another document attached to spreadsheet 4


End If


OutFileName = "C:\Matrix Auto Forms\P053220" & "_" & ComboBox1.Value & "_" & TextBox5.Text & ".pdf"
Set gApp = CreateObject("AcroExch.app")


Set avDoc = CreateObject("AcroExch.AVDoc")
If avDoc.Open(FileNm, "") Then
Set pdDoc = avDoc.GetPDDoc()
Set jso = pdDoc.GetJSObject


jso.getField("topmostSubform[0].Page1[0].EmployeeName[0]").Value = TextBox11.Text
jso.getField("topmostSubform[0].Page1[0].EmployeeNum[0]").Value = TextBox12.Text
jso.getField("topmostSubform[0].Page1[0].Station[0]").Value = TextBox13.Text
jso.getField("topmostSubform[0].Page1[0].Dept[0]").Value = TextBox14.Text
jso.getField("topmostSubform[0].Page1[0].TextField1[0]").Value = TextBox15.Text
jso.getField("topmostSubform[0].Page1[0].Requestdate[0]").Value = RevReqDate
jso.getField("topmostSubform[0].Page1[0].ManualName[0]").Value = ComboBox1.Value
jso.getField("topmostSubform[0].Page1[0].Chap-sec-sub[0]").Value = ListBox3.List(i)
jso.getField("topmostSubform[0].Page1[0].Discription[0]").Value = " ." & ListBox3.List(i, 2)

jso.flattenPages = 1
pdDoc.Save 1, OutFileName 'Save changes as new PDF
pdDoc.Close
End If


'Close the PDF; the True parameter prevents the Save As dialog from showing
avDoc.Close (True)


'Some cleaning
Set gApp = Nothing
Set avDoc = Nothing
Set pdDoc = Nothing
Set jso = Nothing


End Sub

 

[ZVI]

Hi & Welcome to MrExcel Board!
Try this template of the code:

Sub JsoWithEmbeddedPdf()
'ZVI:2017-01-31 https://www.mrexcel.com/forum/excel-questions/988452-how-remove-pdf-object-embedded-excel.html
 
  ' --> Settings, change to suit
  Const SH = 1                  ' Name or index of the sheet with embedded PDF
  Const OLE = "Object 1"        ' Name or index of the PDF OleObject
  Const FN = "P053220-222.pdf"  ' Filename of the PDF
  '<-- End of the settings
 
  Dim avDoc As Object 'As AcroAVDoc
  Dim pdDoc As Object 'As AcroPDDoc
  Dim jso As Object
 
  Sheets(SH).OLEObjects(OLE).Activate '<-- This opens the embedded PDF in Acrobat application
 
  Set avDoc = CreateObject("AcroExch.App").GetActiveDoc
  Set pdDoc = avDoc.GetPDDoc
  Set jso = pdDoc.GetJSObject
 
  ' The code for PDF form editing
'  With jso
'    .getField("topmostSubform[0].Page1[0].EmployeeName[0]").Value = TextBox11.Text
'    .getField("topmostSubform[0].Page1[0].EmployeeNum[0]").Value = TextBox12.Text
'    .getField("topmostSubform[0].Page1[0].Station[0]").Value = TextBox13.Text
'    .getField("topmostSubform[0].Page1[0].Dept[0]").Value = TextBox14.Text
'    .getField("topmostSubform[0].Page1[0].TextField1[0]").Value = TextBox15.Text
'    .getField("topmostSubform[0].Page1[0].Requestdate[0]").Value = RevReqDate
'    .getField("topmostSubform[0].Page1[0].ManualName[0]").Value = ComboBox1.Value
'    .getField("topmostSubform[0].Page1[0].Chap-sec-sub[0]").Value = ListBox3.List(i)
'    .getField("topmostSubform[0].Page1[0].Discription[0]").Value = " ." & ListBox3.List(i, 2)
'    .flattenPages = 1
'  End With
 
  ' Save and close PDF file
  pdDoc.Save 1, ThisWorkbook.Path & Application.PathSeparator & FN
  avDoc.Close True
  
  ' Release the memory of all object variables
  Set jso = Nothing
  Set pdDoc = Nothing
  Set avDoc = Nothing
 
End Sub

 

[abowers]

​Hello ZVI! Thank for helping me with this. It seems though when I use this template I'm getting a runtime error "91" ... Object variable or With block variable not set for "Set pdDoc = avDoc.GetPDDoc

any suggestions on that?

 

[abowers]

I think i was able to get the prior question fixed. I'm still uncertain on how to get it to save the pdf embedded object to a predetermined file path or static file path. This won't change but it has to go to a specific location such as (C:\Matrix Form\P053220.pdf) something like this.. not saving the new document within the excel file or overwriting it. I want this to be ran behind the program .. no manual interaction with the file. Thanks again guys!!

 

[ZVI]

...Object variable or With block variable not set

This warning means syntax error in your code, for example in case of Dim pdDoc As Object was missing.
Nice to know you have sorted this out!

... it has to go to a specific location such as (C:\Matrix Form\P053220.pdf) something like this.

Instead of this code line: pdDoc.Save 1, ThisWorkbook.Path & Application.PathSeparator & FN
use something like that one: pdDoc.Save 1, "C:\Matrix Form\P053220.pdf"

 

[abowers]

This warning means syntax error in your code, for example in case of Dim pdDoc As Object was missing.
Nice to know you have sorted this out!


Well I though I had figured it out. I guess not.. It is still throwing and error saying "Object Variable or With Block variable not set.. Sorry I'm not really familiar with using Adobe commands within Excel. .. I will show the code I currently have:

' --> Settings, change to suit
Const SH = 4 ' Name or index of the sheet with embedded PDF
Const OLE = "Object 1" ' Name or index of the PDF OleObject
Const FN = "P053220.pdf" ' Filename of the PDF
'<-- End of the settings

Dim avDoc As Object 'As AcroAVDoc
Dim pdDoc As Object 'As AcroPDDoc
Dim jso As Object

Sheets(SH).OLEObjects(OLE).Activate '<-- This opens the embedded PDF in Acrobat application

Set avDoc = CreateObject("AcroExch.App").GetActiveDoc
Set pdDoc = avDoc.GetPDDoc
Set jso = pdDoc.GetJSObject

'The code for PDF form editing
With jso
' .getField("topmostSubform[0].Page1[0].EmployeeName[0]").Value = TextBox11.Text
' .getField("topmostSubform[0].Page1[0].EmployeeNum[0]").Value = TextBox12.Text
' .getField("topmostSubform[0].Page1[0].Station[0]").Value = TextBox13.Text
' .getField("topmostSubform[0].Page1[0].Dept[0]").Value = TextBox14.Text
' .getField("topmostSubform[0].Page1[0].TextField1[0]").Value = TextBox15.Text
' .getField("topmostSubform[0].Page1[0].Requestdate[0]").Value = RevReqDate
' .getField("topmostSubform[0].Page1[0].ManualName[0]").Value = ComboBox1.Value
' .getField("topmostSubform[0].Page1[0].Chap-sec-sub[0]").Value = ListBox3.List(i)
' .getField("topmostSubform[0].Page1[0].Discription[0]").Value = " ." & ListBox3.List(i, 2)
' .flattenPages = 1
End With

' Save and close PDF file
pdDoc.Save 1, "C:\Matrix Auto Forms\P053220.pdf"
avDoc.Close True

' Release the memory of all object variables
Set jso = Nothing
Set pdDoc = Nothing
Set avDoc = Nothing

 

[ScottR]

Const OLE = "Object 1"        ' Name or index of the PDF OleObject

Is that line correct? Ie is your Acrobat OLE called "Object 1"?

 

[abowers]

Const OLE = "Object 1"        ' Name or index of the PDF OleObject

Is that line correct? Ie is your Acrobat OLE called "Object 1"?

Yes, the above line "Sheets(SH).OLEObjects(OLE).Activate causes the object to activate showing it in Adobe.
I'm guessing that would mean the code is set correctly since it is pulling the file.

Right after that is when the syntex error occurs.

 

[ZVI]

Could you please confirm that your original code in post #1 worked well with the same code line Set pdDoc = avDoc.GetPDDoc ?

 

[ZVI]

Also try debugging step by step via F8 key.
Or try this code with 3 seconds waiting for a complete document loading:

  Set avDoc = CreateObject("AcroExch.App").GetActiveDoc
 
  '--> Wait a bit
  Dim t As Single
  t = Timer + 3
  While Timer < t: DoEvents: Wend
  '<--
 
  Set pdDoc = avDoc.GetPDDoc