Import data from pdf to excel6/3/2023 Excel and Word play well together because they are both Microsoft programs. The code is a lot easier to work with when you are trying to extract data from a. I know this is an old issue but I just had to do this for a project at work, and I am very surprised that nobody has thought of this solution yet: Keep in mind what you get from this could be full of all kinds of non-printing characters (line feeds, newlines, etc) that could even end up in the middle of what look like contiguous blocks of text, so you may need additional code to clean it up before you can use it. It's going through the PDF one page at a time, highlighting all of the text on the page, then dropping it (one text element at a time) into a string. What this does is essentially the same thing you are trying to do - only using Adobe's own library. StrText = strText & objSelection.GetText(tCount) Set objSelection = objPage.CreatePageHilite(objHighlight)įor tCount = 0 To objSelection.GetNumText - 1 ObjHighlight.Add 0, 10000 ' Adjust this up if it's not getting all the text on the page Set objPage = objPDDoc.AcquirePage(pageNum) Make sure to add the Library to your references too (On my machine it is the Adobe Acrobat 10.0 Type Library, but not sure if that is the newest version)Įven with the Adobe library it is not trivial (you'll need to add your own error-trapping etc): Function getTextFromPDF(ByVal strFilename As String) As Stringįor pageNum = 0 To objPDDoc.GetNumPages() - 1 You can open the PDF file and extract its contents using the Adobe library (which I believe you can download from Adobe as part of the SDK, but it comes with certain versions of Acrobat as well) 'Application.Wait Now + TimeValue("00:00:2") Set openPDF = CreateObject("Shell.Application")Īpplication.Wait Now + TimeValue("00:00:2") 'When Scan Receipts Button Pressed Scan the selected folder/s for receiptsįor Each fCell In Range(ws.Cells(1, 1), ws.Cells(1, ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column)) 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 Why is my paste not working? If I paste after the macro has stopped running it pastes as normal. I get an error when I try to paste the data from the PDF document. I am currently doing this through SendKeys and it is not working. The PDFs show and text can be manually copied and pasted into the Excel document. I am trying to extract the data from a PDF document into a worksheet.
0 Comments
Leave a Reply. |