Posts by Kenneth Hobson

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

    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:

    1. 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.

    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 Using itextsharp might be more appropriate for a c# or forum. I have not used that in VBA. My tutorial is applicable for 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 tutorial is at:…s-Parameters-5-iTextSharp

    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:

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

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

    Here is the GetDetailsOf version will ALL names and property values. Just modify the INPUTS commented block in first sub and run it. The inputs do not have to be just pdf files. As before, run with activesheet being empty/blank.

    For a beginner you seem to grasp more than many.

    Unfortunately, my computer at work no longer has Acrobat. I will get an old Acrobat version 6 and see if it would work for metadata. I wish Adobe had not gone the way of leasing software.

    For the interim, I did some preparatory work for a GetDetailsOF approach. It does not apply to your needs but many concepts could be used in your project.

    Normally, when I use GetDetailsOf method, I only need a few properties/metadata. With the needs of your project in mind, I fleshed most of this out tonight. Put it into a Module, change pPDF value line in first sub and run it with a blank worksheet active. Cell B2 and to the right will have 322 columns filled. Some are blank because it has no property name for that index position.

    If you examine the code, you will see comments with some good tips. Debug.Print lines were left in as they are good for testing but also show how some things can be shown. I most always code like this.

    The ArrayList method offers some nice features that we can use later in the file iterative loop. e.g. We may just use say 5 property field values. We would get the names, and with IndexOf, we can get just the value from another ArrayList with the values in the same index order. Most people, just hard code the index in. 99.9% of the time, that would be sufficient. Howsoever, every once in a while, Microsoft does an upgrade that could impact the code.

    This conceptual approach also offers a more dynamic potential. e.g. Data Validation list with all property names in B2. When one is picked for the file in A2, then C2 will be updated with that property value. That way, 322 columns are not needed but readily found.

    Anyway, if you want to give it a run, change the pPDF value and run...

    You can iterate i=0 to 100 or such. I say 100 because the stack in Immediate window is limited. Then do 101 to 200. The number of properties varies by file type. 350 should get most all. If coded right, one can put "" as the file base name and it returns the property name. I have put all of that into a worksheet so loop count is not limited. I would use Debug.Print i, s. The "s" is the property value found. The "i" reminds you which index number the value came from.

    GetDetailsOf method is nice but of limited use to you it sounds like.

    File property information can also be found using a Shell() to an application like exiftool. It does not get all file types but for those that it does, it can read and sometimes write those properties back. PDF is one that it handles well. Most use it for media file types. Its command line switches can be short but powerful. It is a good tool to consider depending on the project.

    When you run that code, Debug.Print puts the results in the VBE, Immediate Window. Select View in VBE to view that window if not active.

    Post back if you need more help. Attaching a short example file with the column headings would help us help you a bit easier. Click the Attachments link at end of reply box.

    It takes a bit more work but I like to put all results into an array and then write to the worksheet once. Writing to sheet one cell at a time is very slow.

    The file probably came from an outside source.

    1. Open File Explorer (Win+E)

    2. Right click the CHM file

    3. View Properties.

    4. Bottom Right should be an UnBlock checkbox. Check it.

    5. Apply, OK.

    Welcome to the forum!

    You installed Adobe Pro? If so, you should have AcroExch object in VBE > Tools > References. I would post code that I found but I don't have Pro version installed on this computer. I like to test code before I post it.

    Are these metadata values the same as File property values as seen in File Explorer? Is so, you can use the GetDetailsOf() method. e.g.

    For the files stored locally, it is easy to get your path in File Explorer.

    This is probably of limited or no help at all. I have 3 OneDrive entries in the registry. Likely you would want the OneDriveCommercial. If the goal is to find the local path for any logged in user, one can get that from the Registry HCU\Environment. Of course that can be automated in VBA.

    Maybe something like this but you may want to add the filter by date. I was not sure what you wanted with that. I removed some things so I could work up this example.

    I can not duplicate your problem. I tried ActiveX and Forms command buttons and shape controls.