Copy pdf metadata (title, year, author...etc) into excel cells

Grann · May 1st 2021

I know and sure about that, but I post it in case some one like syrian are blocked to enter even some science sites because of world politics.

Adobe is forced to do that and lose some selling products, as we forced to do that even if this threats our privacy and security.

it ends up, we leave in 2011 while world is trying every thing new, I will never accebpt that what ever the way is.

Before 2011 we were able to bye all products, now world bad people trying to use us against regime by blocking every thing they can.

Thanks sir again and again wish you all the best.

**royUK** · May 2nd 2021

I've removed the link, do not post these type of links again.

Grann · May 2nd 2021

Quote from royUK

I've removed the link, do not post these type of links again.

Thanks any way I am so great full for your help

Sorry for that.

**Kenneth Hobson** · May 14th 2021

For what it is worth, here is #4 link's code but with early binding.

Code

Sub Test_ReadPDFMetaData()
  ReadPDFMetaData "d:\myfiles\pdf\A10_1.pdf"
End Sub

'Acrobat metadata late binding example, http://vbcity.com/forums/t/170532.aspx
'Early binding method:
Sub ReadPDFMetaData(ByVal sFile As String)
  'Dim oApp As Object 'late binding
  'Dim odoc As Object 'late binding
  'Tools > References > Acrobat
  Dim oApp As AcroApp 'early binding
  Dim odoc As AcroPDDoc 'early binding
  Dim d As Long
  
  Dim strFileName As String, strNumPages As Long, strPageMode As String
  Dim strTitle As String, strSubject As String, strAuthor As String
  Dim strKeywords As String, strCreator As String, strProducer As String
  
  'Set oApp = CreateObject("AcroExch.App")  'Early or late binding
  'Set odoc = CreateObject("AcroExch.PDDoc")  'Early or late binding
  Set oApp = New AcroApp 'Early binding
  Set odoc = New AcroPDDoc 'Early binding
  
  With odoc
    .Open sFile
    strFileName = .GetFileName
    Debug.Print strFileName
    strNumPages = .GetNumPages
    Debug.Print strNumPages
    strPageMode = .GetPageMode
    Debug.Print strPageMode
    strTitle = .GetInfo("Title")
    Debug.Print strTitle
    strSubject = .GetInfo("Subject")
    Debug.Print strSubject
    strAuthor = .GetInfo("Author")
    Debug.Print strAuthor
    strKeywords = .GetInfo("Keywords")
    Debug.Print strKeywords
    strCreator = .GetInfo("Creator")
    Debug.Print strCreator
    strProducer = .GetInfo("Producer")
    Debug.Print strProducer
    .Close
  End With
    
  Set odoc = Nothing
  Set oApp = Nothing
End Sub

Display More

Grann · May 14th 2021

Greetings

All is working

I used excel with this code and the last code and all is good

I install VB6 and used the code there and now organizing code in form

Thank you sir

Grann · May 14th 2021

MetaData of pdf where shown in acrobat: File>properties>description

And all metadata that are stored there is accessible by previous codes

But there are some pdfs that have some thing called xmp is similar to xml
I can access the xmp info in acrobat: File>properties>description>Additional MetaData>Advanced

Some times the data is stored in xmp file and not inserted in File>properties>description, in this case codes return nothing

Have you reached this type of xmp before?

I can extract xmp file using acrobat and i found it similar to xml

this is an xmp file

<?xpacket begin="" id="W5M0MpCehiHzreSzNTczkc9d"?>

<x:xmpmeta xmlns:x="adobe:ns:meta/" x:xmptk="Adobe XMP Core 5.6-c017 91.164464, 2020/06/15-10:20:05 ">

<rdf:RDF xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#">

<rdf:Description rdf:about="doi:10.1016/j.progpolymsci.2010.01.006"

xmlns:dc="http://purl.org/dc/elements/1.1/"

xmlns:prism="http://prismstandard.org/namespaces/basic/2.0/"

xmlns:pdfaid="http://www.aiim.org/pdfa/ns/id/"

xmlns:xmp="http://ns.adobe.com/xap/1.0/"

xmlns:xmpRights="http://ns.adobe.com/xap/1.0/rights/"

xmlns:pdf="http://ns.adobe.com/pdf/1.3/"

xmlns:xmpMM="http://ns.adobe.com/xap/1.0/mm/"

xmlns:pdfx="http://ns.adobe.com/pdfx/1.3/">

<dc:format>application/pdf</dc:format>

<dc:identifier>10.1016/j.progpolymsci.2010.01.006</dc:identifier>

<dc:title>

<rdf:Alt>

<rdf:li xml:lang="x-default">Polymeric materials for bone and cartilage repair</rdf:li>

</rdf:Alt>

</dc:title>

<dc:creator>

<rdf:Seq>

<rdf:li>D. Puppi; F. Chiellini; A.M. Piras; E. Chiellini</rdf:li>

</rdf:Seq>

</dc:creator>

<dc:publisher>

<rdf:Bag>

<rdf:li>Elsevier Ltd</rdf:li>

</rdf:Bag>

</dc:publisher>

<dc:subject>

<rdf:Bag>

<rdf:li>Biodegradable polymers</rdf:li>

<rdf:li>Tissue engineering</rdf:li>

<rdf:li>Polymeric scaffolds</rdf:li>

<rdf:li>Bone</rdf:li>

<rdf:li>Cartilage</rdf:li>

</rdf:Bag>

</dc:subject>

<prism:aggregationType>journal</prism:aggregationType>

<prism:publicationName>Progress in Polymer Science</prism:publicationName>

<prism:issn>0079-6700</prism:issn>

<prism:volume>35</prism:volume>

<prism:number>4</prism:number>

<prism:coverDisplayDate>April 2010</prism:coverDisplayDate>

<prism:coverDate>2010-04</prism:coverDate>

<prism:pageRange>403-440</prism:pageRange>

<prism:startingPage>403</prism:startingPage>

<prism:endingPage>440</prism:endingPage>

<prism:doi>10.1016/j.progpolymsci.2010.01.006</prism:doi>

<prism:url>http://dx.doi.org/10.1016/j.progpolymsci.2010.01.006</prism:url>

<pdfaid:part>1</pdfaid:part>

<pdfaid:conformance>B</pdfaid:conformance>

<xmp:CreatorTool>Elsevier</xmp:CreatorTool>

<xmp:ModifyDate>2010-03-04T18:53:25+05:30</xmp:ModifyDate>

<xmpRights:Marked>True</xmpRights:Marked>

<pdf:Producer>Acrobat Distiller 8.1.0 (Windows)</pdf:Producer>

<pdf:Keywords>Biodegradable polymers; Tissue engineering; Polymeric scaffolds; Bone; Cartilage</pdf:Keywords>

<xmpMM:DocumentID>uuid:480fb66d-2c5d-432d-84db-b61fadfc99bf</xmpMM:DocumentID>

<xmpMM:InstanceID>uuid:f37f0421-2c40-4a64-88bc-b7974bc6200f</xmpMM:InstanceID>

<pdfx:AuthoritativeDomainↂ005B1ↂ005D>elsevier.com</pdfx:AuthoritativeDomainↂ005B1ↂ005D>

<pdfx:AuthoritativeDomainↂ005B2ↂ005D>sciencedirect.com</pdfx:AuthoritativeDomainↂ005B2ↂ005D>

<pdfx:AuthoritativeDomain>

<rdf:Bag>

<rdf:li>elsevier.com</rdf:li>

<rdf:li>sciencedirect.com</rdf:li>

</rdf:Bag>

</pdfx:AuthoritativeDomain>

</rdf:Description>

</rdf:RDF>

</x:xmpmeta>

<?xpacket end="w"?>

Grann · May 14th 2021

I know reading data could be easy if the xmp file is separated from pdf file, but in this case i can only view the data from acrobat.

i miss the code that reach the hidden xmp MetaData

What do you think?

Grann · May 14th 2021

in This link https://www.example-code.com/vb6/xmp_bag_seq_alt.asp

they are using Chilkat ActiveX but for a jpg image

i install the Chilkat

I tried the code for pdf, but an error says:Unrecognized file type

Code

Code

Dim xmp As New ChilkatXmp

' Load a JPG or TIF image file.
' Sample JPG's with XMP metadata may be found at:
' http://www.chilkatsoft.com/testData/xmp/sample1.jpg
' http://www.chilkatsoft.com/testData/xmp/sample2.jpg
' http://www.chilkatsoft.com/testData/xmp/sample3.jpg
Dim success As Long
success = xmp.LoadAppFile("e:\nn\puppi2010.pdf") ' it was sample1.jpg and i changed it to my pdf
If (success <> 1) Then    Debug.Print xmp.LastErrorText    Exit Sub
End If

Debug.Print "Num embedded XMP docs: " & xmp.NumEmbedded

' Assuming there is at least one, get the 1st.
' (There is typically never more than one, but theoretically it's possible.)
Dim xml As ChilkatXml
Set xml = xmp.GetEmbedded(0)
If (xmp.LastMethodSuccess = 1) Then
    ' Create an object to hold one or more string values.    Dim sa As New CkStringArray    success = sa.Append("value_1")    success = sa.Append("value_2")    ' ...
    success = xmp.AddArray(xml,"bag","Iptc4xmpCore:Chilkat",sa)    If (success <> 1) Then        Debug.Print xmp.LastErrorText    Else        ' Save the JPG.        success = xmp.SaveAppFile("modified.jpg")        If (success <> 1) Then            Debug.Print xmp.LastErrorText        End If
    End If

Else    Debug.Print xmp.LastErrorText
End If

Display More

immediate result as:

ChilkatLog:

LoadAppFile:

DllDate: Feb 8 2021

ChilkatVersion: 9.5.0.86

UnlockPrefix: NONE

Architecture: Little Endian; 32-bit

Language: ActiveX

VerboseLogging: 0

path: e:\nn\puppi2010.pdf

Auto-unlocking for trial mode...

unlockCode: Auto unlock for 30-day trial

regKeyUnlock:

product: ChilkatBundle

hcCurDate: Fri, 14 May 2021 15:08:23 +0300

hcExpire: 5/2021

Component successfully unlocked using trial key

--regKeyUnlock

xmpLoadFile:

Unrecognized file type

filename: e:\nn\puppi2010.pdf

--xmpLoadFile

Failed.

--LoadAppFile

--ChilkatLog

**Kenneth Hobson** · May 14th 2021

I used ChilKat many years ago for XML parsing.

I will look at some SDK's tonight to see if there is a COM method to get xmp metadata.

One does not see much about that for VBA in a web search. It seems like it may be complex. Look in: Acrobat JS API Reference

Something here might be of use. https://www.adobe.com/devnet/xmp.html

Grann · May 14th 2021

Thanks

Access denied

Will open the link when I am home, it needs VBN

**Kenneth Hobson** · May 15th 2021

As I thought, it looks too involved for VBA for me. One would need to set Search.docXMP=True. I don't know how to set the search object or how to query to get what you want. The API shows the javascript code to work the 3 Copyright metadata fields.

This thread has gotten so long, I forgot if I discussed exiftool. It does not work with all applications but PDF has the most features in it. I have used Shell() to execute the exiftool.exe with command line switches. If that interests you, see: exiftool.org

Here is an example to strip "all" metada from a PDF file using Acrobat and exiftool.

Code

'https://exiftool.org/forum/index.php?topic=3943.15

'This code is VBA code designed to accept filenames from the user (via a pop-up
'File-Dialog where user can select multiple files at the one time. The code then
'processes each file, removing the metadata in the file then saving the file back
'out into either the original file of (optionally) a new file with extension
'".nometa.pdf"
'
'Note! At the moment this code is deigned to work with PDF files and would require
'      changes to be made in the code to accomodate non-PDF files.
'
'Note!:
'This module must have the following references included
'within the VBA Editor environment
'  Visual Basic for Applications
'  Microsoft Access 11.o Object Library  (or equivalent/later)
'  Microsoft Office 14.0 Object Library  (or equivalent/later)
'  Adobe Acrobat 5.0 Type Library        (or equivalent/later)
'Include these by clicking on "Tools/References" within the editor

Public Sub RemoveMetaFromFiles()
Dim AcroPDDoc As Acrobat.CAcroPDDoc
Dim bOverwriteOriginal As Boolean
Dim cExt As String
Dim cFile As String
Dim cFiles As String
Dim cMsg As String
Dim cRoot As String
Dim fs As Object
Dim nEndSize As Long
Dim nFile As Integer
Dim nFiles As Long
Dim nStartSize As Long
Dim oDlg As FileDialog
Dim oFile As Object

'Change the following line to have value "True" if you DO
'want to overwrite the original file rather than writing to a new
'file with extension ".nometa.pdf"
bOverwriteOriginal = False

Set AcroPDDoc = CreateObject("AcroExch.PDDoc")

Set oDlg = Application.FileDialog(msoFileDialogFilePicker)
oDlg.InitialFileName = "*.pdf"
oDlg.AllowMultiSelect = True
oDlg.ButtonName = "Select"
oDlg.Title = "Select files from which MetaData is to be extracted:"
oDlg.Filters.Add "PDF Files", "*.pdf", 1
oDlg.Filters.Add "All Files", "*.*", 2
oDlg.InitialView = msoFileDialogViewDetails
Set fs = CreateObject("Scripting.FileSystemObject")
oDlg.InitialFileName = GetSetting(Application.Name, "Setup", "InitialFileName", "*.pdf")
If oDlg.Show = -1 Then
  cMsg = "MetaData removed:" + vbCrLf + vbCrLf
  nFiles = oDlg.SelectedItems.Count
  For nFile = 1 To nFiles
    SaveSetting Application.Name, "Setup", "InitialFileName", oDlg.InitialFileName
    Application.StatusBar = "Removing Metadata: " + CStr(nFile) + "/" + cFiles + ":" + cFile
    cFile = oDlg.SelectedItems(nFile)
    If fs.FileExists(cFile) Then
      Set oFile = fs.getfile(cFile)
      nStartSize = nStartSize + oFile.Size
      cMsg = cMsg + Space(4) + Format(nStartSize, "###,###,###")
      cExt = LCase(Mid(cFile, InStrRev(cFile, ".")))
      cRoot = Replace(cFile, cExt, "")
     
      StripMeta cFile, cExt, bOverwriteOriginal
     
      nEndSize = nEndSize + oFile.Size
      Select Case cExt
        Case ".pdf"
          'Open the newly stripped PDF file
          If AcroPDDoc.Open(cRoot + ".nometa.pdf") Then
            'then  save it removing unreferenced objects
            AcroPDDoc.Save PDSaveCollectGarbage + PDSaveFull, cRoot + ".nometa.pdf"
            AcroPDDoc.Close
          End If
      End Select
      cMsg = cMsg + " => " + Format(nEndSize, "###,###,###") + ": " + cRoot + ".nometa.pdf" + vbCrLf
    End If
  Next
  Application.StatusBar = ""
  Set AcroPDDoc = Nothing
  MsgBox cMsg, vbInformation + vbOKOnly
End If
End Sub
'
'

Private Sub StripMeta(cFile As String, cExt As String, bOverwriteOriginal As Boolean)
   Dim cExitTool As String
   Dim cOutFile As String
   Dim cExifTool As String
   
   'Note!: Comment out/amend the following to point to the exifTool Utility on your system
   cExifTool = "D:\myfiles\exiftool\exiftool.exe"
   'cExifTool = "c:\Program Files\exifTool\exiftool.exe"
   
   Select Case cExt
     Case ".pdf"
       'The following lines remove the Metadata from the document dictionary only
       If bOverwriteOriginal Then
          Shell cExifTool + " -all= " + cFile, vbMinimizedFocus
       Else
          cOutFile = Replace(cFile, cExt, "") + ".nometa.pdf"
          Shell cExifTool + " -all= -o " + cOutFile + " " + cFile, vbMinimizedFocus
       End If
   End Select
End Sub

Display More

Grann · May 16th 2021

Hello sir and thanks again

I tried the code and it showed error in shell line, any way the code lead me to anthor tool "I textsharp"

And in this link https://stackoverflow.com/ques…ng-any-data-to-user-on-pd

they used c# I think

Code

public string ReadXmpMetadata(byte[] src) {
    PdfReader reader = new PdfReader(src);
    byte[] b = reader.Metadata;
    return Encoding.UTF8.GetString(b, 0, b.Length);
}

I am trying to install itextsharp but on the site when install it, the file is not dll or ocx or any type could be added to references

I searched for it as dll but after adding it the program not responding.

Will try with it if it reads all meta data maybe I can store it and search it to get info I need

Year

Pages rang

Volume

Issue

Title

Jornal

Author

Grann · May 16th 2021

sorry for late reply

And thanks for your help

Do you thing th itextsharp can do the job with that short code?

**Kenneth Hobson** · May 16th 2021

If you attach a pdf file, and tell me the metadata fieldnames, I can better help.

For the standard Acrobat GetInfo() fields, that is easy as you saw.

For the xmp metadata fields, even the itextsharp does not return individual fields. It looks like it is returning all xmp metadata as a string. I suspect that you would have to parse it out too. If you can generate the xmp file data as you did, parsing it might be doable. If that interest you, we can pursue that.

If I had the example pdf file with the known fieldnames with values to retrieve, the exiftool returns metadata but from the xmp, I am not sure. Here is an example output to a csv file. Row 2 does not align here in the pasted text but I think you get it.

MicrosoftÂ® ExcelÂ® 2010

SourceFile

ExifToolVersion

FileName

Directory

FileSize

FileModifyDate

FileAccessDate

FileCreateDate

FilePermissions

FileType

FileTypeExtension

MIMEType

PDFVersion

Linearized

PageCount

Language

TaggedPDF

Author

CreateDate

ModifyDate

Producer

Creator

ken.pdf

12.17

.

2018:08:02 09:04:49-05:00

2021:05:15 11:31:26-05:00

PDF

application/pdf

1.5

2

Yes

2018:08:02 09:04:49-05:00

MicrosoftÂ® ExcelÂ® 2010

The command to generate in a CMD prompt would be something like:

Code

exiftool -csv ken.pdf > outken.csv

I can only guess at why your Shell() command failed since you did not post code. For the string in Shell() for exiftool, one would use the full drive:\path\filename.ext. Those would be double quote encapsulated. Normally, I do Win+R, CMD, Enter, and then, D:, cd myfiles\exiftool. My ken.pdf was in that folder. I copied my exiftool(-k).exe to exiftool.exe so that I could use the command line switches. If you click the -k file in file explorer, the help file will show in a CMD window.

Here is -all option for the same file in exiftool. As you can see, it may well get all that you need.

Quote

D:\MyFiles\exiftool>exiftool -all ken.pdf

ExifTool Version Number : 12.17

File Name : ken.pdf

Directory : .

File Size : 97 KiB

File Modification Date/Time : 2018:08:02 09:04:49-05:00

File Access Date/Time : 2021:05:16 09:47:07-05:00

File Creation Date/Time : 2021:05:15 11:31:26-05:00

File Permissions : rw-rw-rw-

File Type : PDF

File Type Extension : pdf

MIME Type : application/pdf

PDF Version : 1.5

Linearized : No

Page Count : 2

Language : en-US

Tagged PDF : Yes

Author : Kenneth Ray Hobson

Create Date : 2018:08:02 09:04:49-05:00

Modify Date : 2018:08:02 09:04:49-05:00

Producer : Microsoft┬« Excel┬« 2010

Creator : Microsoft┬« Excel┬« 2010

Display More

For the most part, I think that the itextsharp.dll is for those without Acrobat. I had a book for itextsharp but when I loaned it out, it was never returned. I had the University of Oklahoma do a research project for me to build a pdf using that DLL. I posted a tutorial for how to use it using vb.net. Using itextsharp might be more appropriate for a c# or vb.net forum. I have not used that in VBA. My tutorial is applicable for vb.net users. The EXE that I created from it can be used by most programming languages. The link is at a WordPerfect forum. My old itextsharp vb.net tutorial is at: https://www.wpuniverse.com/vb/…s-Parameters-5-iTextSharp

Grann · May 16th 2021

Hello sir

Got it .. i will sort my work and my goal with pdf examples for each case

I saw how getinfo and other ways work very easy and helpfull , but i was shocked when i found some pdf MetaData cant be reached with previous methods, where i saw xmp data, and yes it differs in the way it arranged form pdf to another (what is called <year> in some pdfs it is mentioned in other way in other pdfs)

i saw how to search xml files and i found it close to xmp, this way i can search keywords (year, author, title ...) if i got it, that i what i was thinking

I will work on my next reply to explain my thoughts and needs in a good way, not to waste your time sir.

I will also attach the error i got with exiftool,

Finally

i know i am not good at all in this, but i aim to do this idea and trying hard to understand the way it works

I am lucky i met you, thanks

**Kenneth Hobson** · May 16th 2021

Here is an example using exiftool that I worked up for you.

Code

Sub CSVExiftool()
  Dim pathExiftool As String, s As String, wsh As Object, testFile As String
  Dim sOut As String, a, c, d, i As Integer
  
  'Path to a file to get its properties/metadata
  testFile = "d:\myfiles\exiftool\ken.pdf"
  
  'https://exiftool.org/
  'Install the tool and set path below. Copy exiftool(-k).exe to exiftool.exe.
  pathExiftool = "D:\MyFiles\exiftool\exiftool.exe"
  
  'Set the string to execute exiftool with command line switches
  's= """" & pathExiftool & """" & " -all " & """" & testFile & """"
  s = """" & pathExiftool & """" & " -csv " & """" & testFile & """"
  
  'Exit if pathExiftool does not exist.
  If Dir(pathExiftool) = "" Then
    MsgBox pathExiftool, vbExclamation, "Path Does Not Exist - Macro Ending"
    Exit Sub
  End If
  
  'Exit if testFile does not exist.
  If Dir(testFile) = "" Then
    MsgBox testFile, vbExclamation, "Path Does Not Exist - Macro Ending"
    Exit Sub
  End If
    
  Set wsh = CreateObject("WScript.Shell")
  sOut = wsh.Exec(s).StdOut.ReadAll
  Set wsh = Nothing
  
  'Split output into an array
  a = Split(sOut, vbLf) '2 rows
  c = Split(a(0), ",")  'fields
  d = Split(a(1), ",") 'values
  ReDim a(1 To 2, 1 To UBound(c) + 1)
  For i = 1 To UBound(c) + 1
    a(1, i) = c(i - 1)
    a(2, i) = d(i - 1)
  Next i
  
  'Put array into range
  Range("A1").Resize(2, UBound(c) + 1) = a
  ActiveSheet.UsedRange.EntireColumn.AutoFit
End Sub

Display More

Grann · May 18th 2021

Dont't know what to say sir

Wish you all the best

I run the code and it returns the values into the excel sheet

There are some fields with the value "saved"

I will check if it refers to the values that are in pdf description

Tosay will attach pictures for results, once electricity is on.

Sorry we only have 6 hours of electricity in the day.

Grann · May 18th 2021

you managed 3 ways to get info and I appreciate your time and work

Thanks for all what you have done.

Now I will manage between the different methods to get all what I need

Grann · May 18th 2021

pic number 1 shows the field "Title" with value "saved"

in the xmp it has a value but under (container) as you see in pic 5 there next to title (alt container)

Grann · May 18th 2021

Dont know why it reads some values and others given the value "saved"

In another pdf file

the value of title is shown in another field

pic 6 is in xmp

pic 7 as in sheet

Copy pdf metadata (title, year, author...etc) into excel cells

Participate now!

Share

Tags