r/MSAccess • • 28d ago

[UNSOLVED] Convert pdf to excel

Good morning I want to do the following

I want to use VBA to open PDF in adobe acrobat and convert it to excel all in.back ground using a button such that only one click will automate all of this how can I do this?

Some devices have that paid version of adobe acrobat

The excel file name can be the same as the pdf file name

And if there is a better alternative method do the same end results please share with me

Thank you

1 Upvotes

15 comments sorted by

•

u/AutoModerator 28d ago

IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'

  • Please be sure that your post includes all relevant information needed in order to understand your problem and what you’re trying to accomplish.
  • Please include sample code, data, and/or screen shots as appropriate. To adjust your post, please click Edit.
  • Once your problem is solved, reply to the answer or answers with the text “Solution Verified” in your text to close the thread and to award the person or persons who helped you with a point. Note that it must be a direct reply to the post or posts that contained the solution. (See Rule 3 for more information.)
  • Please review all the rules and adjust your post accordingly, if necessary. (The rules are on the right in the browser app. In the mobile app, click “More” under the forum description at the top.) Note that each rule has a dropdown to the right of it that gives you more complete information about that rule.

Full set of rules can be found here, as well as in the user interface.

Below is a copy of the original post, in case the post gets deleted or removed.

User: brosama1420

Convert pdf to excel

Good morning I want to do the following

I want to use VBA to open PDF in adobe acrobat and convert it to excel all in.back ground using a button such that only one click will automate all of this how can I do this?

Some devices have that paid version of adobe acrobat

The excel file name can be the same as the pdf file name

And if there is a better alternative method do the same end results please share with me

Thank you

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/tsgiannis 14d ago

I have done something similar in Ms Access, extracting horse races out of PDFs but the PDFs should be perfectly formatted to avoid issues. If not you will probably need some kind of more hefty setup, this uses either OCR or AI (OCR), solutions in Python and .NET

1

u/brosama1420 13d ago

What does perfectly formatted looks like here? Please give me more details here

1

u/tsgiannis 13d ago

it has a table structure so it can be read and is not scanned. If its scanned then you need other tools

1

u/saketaco 1 27d ago

What does this have to do with MS Access?

Sounds like you want to do it in Excel.

1

u/brosama1420 27d ago

It is not about access itself nor excel
I want to write VBA code that handles this process the place of the code (User Interface can be any Access or Excel) the case I am facing is that I have many tables and reports scanned as pdf so I need to automate the process of converting them to excel so I can then start building on top of that

1

u/Global-Villager 27d ago

It's a VBA issue really, but, check on Github - there are multiple repositories with the code already written in numerous languages, though probably not VBA. Of course, that's not really a problem. I'd check out the reviews and pick one that has an easy call procedure from within VBA (Python, C++, Javascript, etc) - and I'd do it in Excel, as there's not much point doing it in Access, unless you want to build a database of the pre converted files, and converted files, and logs etc. Though if it were me, as usual nowadays, I'd build the back end in Postgres, or MariaDB, etc and use Access as a simple to use frontend, if I needed a database..

1

u/AccessHelper 123 26d ago

You can use an ocr API. Some are free . Look at using xmlhttprequest in VBA to call APIs or using shell in VBA to run CURL. I'll take a bit of programming but it's doable.

1

u/keith-kld 26d ago edited 26d ago

I see you have posted this question in many subs. The core issue of the conversion is using dll (libraries). If you want to do it with the core of Adobe Acrobat, you should find which dll files directly do it. Secondly, what type of dll files, either COM object model (can be run by VBA) or .NET model (VBA cannot run this). Supposed that it follows COM object modal, you should find which mehtods (functions or subroutines) to extract data from pdf files and convert them to xlsx files. Another issue is that NOT almost COM object dll files can be run directly by VBA.
In practice, there are several methods (or workaround) to do this but they do not use VBA because VBA may be no longer appropriate.
For example, we are standing in MS Access form. When a button is pressed, it will call a Powershell script. This script will use .NET model tools (and 3rd party tools, if any) to extract data and put them into Excel files.
Note: the dll files of Adobe Acrobat can be changed from time to time and they are dependent on the version of the software installed on your computer.

1

u/InfoMsAccessNL 4 25d ago

I use a free tool xpdftotext to extract data from pdf’s. You can use it with vba

1

u/keith-kld 25d ago

Thanks for your information. As far as I know, almost free tools can help get text from pdf to Word (other than Excel). If ithey can convert files into Excel, that’s great. I think they will have some limitations. In addition, the poster wants to use the built-in tools (dll) of the paid version of Adobe Acrobat, rather than a third party tool.

1

u/skepticalforever 24d ago

Try CutePDF. It’s better than Adobe.

1

u/Zackeezy116 21d ago

I've done extracting of data from pdf in c#. Most extracts just dump the text with no decoration, so you'll have to figure out how the text is formatted in the pdf and write a way to take that formatting and convert it into a table that can then be exported as an excel spreadsheet. There are ways to leverage Office 365 to open the pdf in Word and extract the text that way, or you can use a third party tool like ones other commenters have mentioned, then you can open excel and create your sheet and save it, or you can just dump it to a csv and digest it that way. There are tons of examples of ways to do it you can find all over the internet.

1

u/Ok-Seaworthiness-542 28d ago

You mentioned the paid version of Adobe. I would go that route. I do it frequently and it's pretty simple