VBA to Find and Copy text from PDF to create Table in Excel

I’m trying to automate the very repetative and consistant process of finding the Month, Title, and Cost from a PDF file and pasting it into a large table in Excel. The goal is to find the text “Month: ” on each page of the 200ish page PDF, copy the 6 characters following it, and paste it into each cell of a colum in an Excel Table (the table is already created), and then do the same with the 4 characters folowing “Title: “, and then any characters  after “Totals: ” and before “.00” (which could be anywhere from 1 to 8 characters, all numeric). To create a table 3x200ish which I can then sort and find the data much more efficiently. I’m fairily new with Macros, and I’m having trouble finding where to start my search for the code I need. Most code I’ve found involves opening a number of pdfs and searching for true or false results. I only have one very large PDF that’s already open and will always have the same document name.

I would imagine in lamens terms it would look something like the below, but I don’t know the exact VBA language to use.

Activate “PDF_Title.pdf” | Find “Month: ” | Select (6 characters following – this seems like it might be a more complicated part of the code) | Copy Selection | Activate “Excel_Title.xlsx” | Activate (Next Blank cell in Column A – I think I know how to do this bit) | Paste from Clipboard | Loop until End of Document

Any assistance would be a huge help!

By: Natasha Gossman

Leave a Reply

Your email address will not be published. Required fields are marked *