How to remove a PDF Object that is embedded in excel

A

abowers

New Member
Joined
Jan 30, 2017
Messages
8
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Hi & Welcome to MrExcel Board!
Try this template of the code:
Rich (BB 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
 
Last edited:
Upvote 0
​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?
 
Upvote 0
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!!
 
Upvote 0
abowers said:
...Object variable or With block variable not set
Click to expand...
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.
Click to expand...
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"
 
Last edited:
Upvote 0
ZVI said:
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
Click to expand...
 
Upvote 0
Code: 
Const OLE = "Object 1"        ' Name or index of the PDF OleObject

Is that line correct? Ie is your Acrobat OLE called "Object 1"?
 
Last edited:
Upvote 0
ScottR said:
Code: 
Const OLE = "Object 1"        ' Name or index of the PDF OleObject

Is that line correct? Ie is your Acrobat OLE called "Object 1"?
Click to expand...

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.
 
Upvote 0
Could you please confirm that your original code in post #1 worked well with the same code line Set pdDoc = avDoc.GetPDDoc ?
 
Upvote 0
Also try debugging step by step via F8 key.
Or try this code with 3 seconds waiting for a complete document loading:
Rich (BB code): 

  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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Create Drop Down Menu in PDF
Replies
1
Views
604
John_w
J
J
  • Question Question
Copy Picture - VBA
2
Replies
10
Views
271
Logit
Logit
V
  • Question Question
Excel VBA - How to modify my code to include a command that prints data to PDF
Replies
1
Views
541
VanGirl
V
H
  • Question Question
Macro: Convert pdf into Excel and extract certain data to put together with file name
Replies
1
Views
3K
HeinrichPaul
H
R
  • Solved
Macro that copies Excel data into pdf field doesn't always paste all the data
Replies
3
Views
1K
RickOlson
R

Forum statistics

Threads
1,224,828
Messages
6,181,206
Members
453,022
Latest member
RobertV1609

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top