0

I want to extract data from pdf files to Excel sheets (then eventually organize the data from there), due to the sensitivity as well as the deployment of this project I am unable to use third party software (so no Adobe, etc.). How do I do this only in Excel?

Sticky keys is not viable as it breaks when a user accidentally hits a key mid scan (assuming it operates solely from within Excel/Office).

Sticky keys code.

Dim myPath As String, myExt As String
Dim ws As Worksheet
Dim openPDF As Object
'Dim pasteData As MSForms.DataObject
Dim fCell As Range

'Set pasteData = New MSForms.DataObject
Set ws = Sheets("DATA")
If ws.Cells(ws.Rows.Count, "A").End(xlUp).Row > 1 Then Range("A3:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row).ClearContents

myExt = "\*.pdf"
'When Scan Receipts Button Pressed Scan the selected folder/s for receipts
For Each fCell In Range(ws.Cells(1, 1), ws.Cells(1, ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column))
    myPath = Dir(fCell.Value & myExt)
    Do While myPath <> ""
        myPath = fCell.Value & "\" & myPath
        Set openPDF = CreateObject("Shell.Application")
        openPDF.Open (myPath)
        Application.Wait Now + TimeValue("00:00:2")
        SendKeys "^a"
        Application.Wait Now + TimeValue("00:00:2")
        SendKeys "^c"
        'Application.Wait Now + TimeValue("00:00:2")
        ws.Select
        ActiveSheet.Paste
        'pasteData.GetFromClipboard

        'ws.Cells(3, 1) = pasteData.GetText
        Exit Sub

        myPath = Dir
    Loop

Next fCell
Community
  • 1
  • 1
Lopeker
  • 33
  • 8
  • 1. There is no question in this "Question". 2. This is not an appropriate question for this forum. It is too broad. We are here to help overcome specific problems in existing code. We are not here to teach you where to start. Please [edit] the post to include the code you have tried and where it failed, please see [mcve] and [ask] – Scott Craner Jun 08 '22 at 16:14
  • All the code I find is relating to the topic using another program to copy pdf data, it sounds like you are saying you do not know of a way to use vba to copy pdf data to excel without external programs, thanks for your input – Lopeker Jun 08 '22 at 16:16
  • I was just explaining why your [last question](https://stackoverflow.com/questions/72547330/looking-to-copy-pdf-data-to-excel-with-vba-and-no-external-programs), now deleted, was closed and down voted. Also why this one will most likely be closed also. – Scott Craner Jun 08 '22 at 16:21
  • So how does one get help from the community regarding a rare topic such as this one? I could post my google results but they lead to nothing – Lopeker Jun 08 '22 at 16:21
  • 1
    1. I honestly do not think this is possible solely with vba and no sendkeys as it would require dll's that do not natively exist in vba to interact with the pdf. 2. Think of it this way, we are all volunteers here and this is not our job. With such a broad question you are asking these volunteers to spend a lot of time to do this research for you. So your only hope is that someone has done this before. But again I just do not think this is possible without having adobe installed, otherwise I am sure you would have stumbled onto it with your own research. – Scott Craner Jun 08 '22 at 16:32
  • 1
    I am just hoping someone has done this before, based on the api's available it is likely someone has tried. I feel like there is some office internal automation for power query I just haven't found yet. I'm not asking anyone to do research for me, if they don't know they don't know. – Lopeker Jun 08 '22 at 16:49
  • I truly hope you get an answer. Good Luck. – Scott Craner Jun 08 '22 at 16:54
  • A crude pdf reader built into vba sounds like a perfect solution to repeatedly get pdf data into an excel file format to then search through and populate a database. my main issue is needing the code to work from within vba so the only requirement needed on the workers computers is to have ms office. (otherwise I would call from acrobat reader or something) finding vba code on this topic has been surprisingly difficult – Lopeker Jun 11 '22 at 20:51

1 Answers1

0

There is a pdf connector in Excel via PowerQuery. You can wrap it in a loop to go through all *.pdf in a folder as well (also within PowerQuery)

https://learn.microsoft.com/en-us/power-query/connectors/pdf

Zach Young
  • 10,137
  • 4
  • 32
  • 53
Anonymous
  • 369
  • 3
  • 5
  • Thank you, this seems like the right path. I am having trouble finding any instructions anywhere about how to do this in the vba code still, do you have any suggestions where I might look to learn more about that? – Lopeker Jun 11 '22 at 20:46
  • Same as with the other comments before, I am not aware that you can open pdf with VBA without calling another app. It would be easier to do it all in PowerQuery. But if you “insisting”, you may want to “wrap” the PowerQuery code for opening pdf in your VBA. – Anonymous Jun 11 '22 at 23:03
  • I just found this on the topic of “integrating” PowerQuery in VBA: https://stackoverflow.com/questions/51386600/how-to-automate-a-power-query-in-vba – Anonymous Jun 11 '22 at 23:05