VBA Expert: Reading Scanned PDF's 

Hi Daniel. I haven't used Tesseract with .tiff files. Also, Tesseract doesn't return anything for check boxes when read from a JPG file. But when I google "Tesseract tif" then I immediately get some possibly useful hits. I suggest you try to make it work at the command prompt first based on those articles. Once you've covered that you can move it to the script.

Hi Chris. The video is made on a Windows machine. So Unar and Tesseract definitely work on there. Also, MS Excel 2010 is fine.

Hi Bart, Thanks for your reply. I managed to find the Windows versions of Unar and Tesseract, and have downloaded your example spreadsheet. I had the same problem with the Shell command as Andre. I therefore substituted Andre's code (i.e. Function PDF_To_Txt down to ==================Put all the txt files into 1 & rename======================= .... End Function. I changed the file directory locations for my computer. However I get a runtime error '53' File not found message, and the 'Call Shell(spdfEx & " " & """" & sPDF & """ """ & sTXT & """", vbNormalFocus)' line of code immediately below ==============Convert PDF to TXT====================== is highlighted. I've pasted the end of the code below. Am I missing something obvious? I'd be grateful if you could take a look. Thanks again. Chris Function PDF_To_Txt(sPDF As String) 'Tools -> Reference -> Microsoft Scripting Runtime 'Process: ' - PDF to JPG/TIFF with Unar --> output is 1 picture per PDF page ' - Make sure the pictures are in the correct folder ' - JPG's to TXT's ' - All TXT's into 1 TXT ' - Collect everything in an Output folder 'To do: ' - If picture; don't call Unar, but straight to Teseract ' - Add .exe and dll's to Macro ' - Check whether files already exist before creating them Dim sPath As String Dim spdfEx As String Dim sTesseract As String Dim sUnar As String Dim sTXT As String Dim i As Integer Dim iSlashCounter As Integer Dim iPageCounter As Integer Dim sPDFname As String Dim sNewPath As String Dim FSO As Scripting.FileSystemObject Dim fsoFolder As Scripting.Folder Dim fsoFile As Scripting.File Dim fsoFile2 As Scripting.File Dim oTxt As Object Dim oTxtGet As Object Dim sAPI As String Set FSO = CreateObject("Scripting.FileSystemObject") iPageCounter = 1 If UCase(Right(sPDF, 3)) "PDF" Then Exit Function End If 'Get Path and PDF name from dir iSlashCounter = 0 For i = Len(sPDF) To 1 Step -1 If Mid(sPDF, i, 1) = "\" Then iSlashCounter = iSlashCounter + 1 If iSlashCounter = 2 Then sPath = Mid(sPDF, 1, i) Exit For ElseIf iSlashCounter = 1 Then sPDFname = Mid(sPDF, i + 1) sPDFname = Mid(sPDFname, 1, Len(sPDFname) - 4) End If End If Next i If FSO.FolderExists(sPath & "Output") = False Then MkDir sPath & "Output\" Sleep 500 End If sUnar = sPath & "unar.exe" 'PDF to JPG converter sTesseract = sPath & "Tesseract\tesseract.exe" 'JPG to TXT converter spdfEx = sPath & "pdfEx\pdfExtractor.exe" sTXT = sPath & sPDFname & ".txt" '==============Convert PDF to TXT====================== Call Shell(spdfEx & " " & """" & sPDF & """ """ & sTXT & """", vbNormalFocus) sAPI = FindWindow(vbNullString, spdfEx) i = 0 Do Until sAPI "0" Or i >= 50 'Catch the screen Sleep 50 sAPI = FindWindow(vbNullString, spdfEx) i = i + 1 Loop i = 0 Do Until sAPI = "0" Or i >= 50 'loop until the screen is away Sleep 500 sAPI = FindWindow(vbNullString, spdfEx) i = i + 1 Loop 'Check whether there is something in the file Set oTxtGet = FSO.OpenTextFile(sTXT, ForReading) If FileLen(sTXT)

Hi Chris, I noticed that the code I uploaded contained more than what I explained in the video. I first call an app to directly read the PDF to TXT. This one doesn't work for scans but goes a lot faster. So only if this fails I go to Unar & Tesseract. In any case, I'm sorry for the confusion. Regarding the error in the shell, I created this video to explain the shell function and what to pay attention to: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-YiHVMF5N9BY.html

I've found out Shell function does not work properly for part: Converting from PDF to JPG. Simply, when I run it, I dont get JPG file. I've tried Part 4 of program on JPG file (manually added JPG file with text into folder) and it works. Do you know how to fix Shell part for PDF to JPG?

Hi DeanDog. Try this link: drive.google.com/file/d/1-vCvJRWg6m6k1d23_fRC2NyQ0q4vsAdP/view?usp=sharing It's an updated version.

Hi Andre. Can you paste the code here that you are using? And maybe the error you are getting as well

I found the code that was on the original downloadable version was different to the code that was at the end of the video, maybe i'm wrong. I'm only interested in the Unar and Tesseract parts; when i execute the code (F8), it seems that its skipping the Unar part and is trying to convert the PDF directly to a text file rather than going through the whole process. Thanks in advance. Function PDF_To_Txt(sPDF As String) 'Tools -> Reference -> Microsoft Scripting Runtime 'Process: ' - PDF to JPG/TIFF with Unar --> output is 1 picture per PDF page ' - Make sure the pictures are in the correct folder ' - JPG's to TXT's ' - All TXT's into 1 TXT ' - Collect everything in an Output folder 'To do: ' - If picture; don't call Unar, but straight to Teseract ' - Add .exe and dll's to Macro ' - Check whether files already exist before creating them Dim sPath As String sPath = "C:\Users\Andre\Desktop\Tesseract-OCR\Tesseract\ReadPDFs\" Dim i As Integer Dim iSlashCounter As Integer Dim iPageCounter As Integer Dim sPDFname As String Dim fsoFolder As Scripting.Folder Dim fsoFile As Scripting.File Dim fsoFile2 As Scripting.File Dim oTxt As Object Dim oTxtGet As Object Dim sAPI As Long Dim FSO As Scripting.FileSystemObject Dim oFolder As Scripting.Folder Dim oFile As Scripting.File Dim sFolder As String Dim sUnar As String Dim sTesseract As String Dim sTxt As String Dim sNewPath As String Set FSO = CreateObject("Scripting.FileSystemObject") sFolder = "C:\Users\Andre\Desktop\Tesseract-OCR\Tesseract\ReadPDFs" Set oFolder = FSO.GetFolder(sFolder) sUnar = "C:\Users\Andre\Desktop\Tesseract-OCR\Tesseract/" & "unar.exe" sTesseract = "C:\Users\Andre\Desktop\Tesseract-OCR\Tesseract" & "/Tesseract.exe" iPageCounter = 1 For Each oFile In oFolder.Files sTxt = sFolder & oFile.Name & "convert" sNewPath = sFolder & "\" & oFile.Name & "pdf" '==============Convert PDF to TXT====================== Call Shell(sUnar & " " & """" & oFile.Name & """", vbNormalFocus) 'Run Unar: PDF to JPG sAPI = FindWindow(vbNullString, sUnar) i = 0 Do Until sAPI "0" Or i >= 50 'Catch the screen Sleep 50 sAPI = FindWindow(vbNullString, sUnar) i = i + 1 Loop i = 0 Do Until sAPI = "0" Or i >= 50 'loop until the screen is away Sleep 500 sAPI = FindWindow(vbNullString, sUnar) i = i + 1 Loop 'Check whether a folder is made by unar. If not; make one and copy the jpg to it If FSO.FolderExists(sNewPath) = False Then MkDir sNewPath Do Until FSO.FolderExists(sNewPath) = True Sleep 100 Loop Dim SourceFile, DestinationFile As String SourceFile = sPath & oFile.Name 'Define source file name. DestinationFile = sNewPath & oFile.Name 'Define target file name. 'Copy the jpg to the newly created folder Set oFolder = FSO.GetFolder(sPath) If Mid(oFile.Name, 1, 4) = "Page" Then FileCopy SourceFile, DestinationFile Sleep 500 Kill sPath & oFile.Name Sleep 500 Exit For End If End If Call Shell(Chr(34) & sTesseract & Chr(34) & " " & Chr(34) & sNewPath & oFile.Name & Chr(34) & " " & Chr(34) & sTxt & Chr(34), vbNormalFocus) sAPI = FindWindow(vbNullString, sTesseract) i = 0 Do Until sAPI "0" Or i >= 50 'Catch the screen Sleep 50 sAPI = FindWindow(vbNullString, sTesseract) i = i + 1 Loop i = 0 Do Until sAPI = "0" Or i >= 50 'Loop until the screen is away Sleep 500 sAPI = FindWindow(vbNullString, sTesseract) i = i + 1 Loop Next oFile '==================Put all the txt files into 1 & rename======================= Set oFolder = FSO.GetFolder(sNewPath) sTxt = sNewPath & sPDFname & ".txt" Set oTxt = FSO.CreateTextFile(sTxt, True) 'Create new txt file Do Until FSO.FileExists(sTxt) = True Sleep 100 Loop For Each oTxtGet In oFolder.Files 'Loop through other files and copy text If CStr(oTxtGet) sTxt Then Set oTxtGet = FSO.OpenTextFile(oTxtGet, ForReading) Sleep 1500 On Error Resume Next oTxt.WriteLine (oTxtGet.ReadAll) On Error GoTo 0 oTxt.WriteLine (" _-_-_-_-_-_-_-_-_- " & "Page " & iPageCounter & " _-_-_-_-_-_-_-_-_- ") oTxtGet.Close iPageCounter = iPageCounter + 1 End If Next oTxtGet oTxt.Close 'Copy txt file to an output folder On Error Resume Next MkDir sPath & "Output\" On Error GoTo 0 FileCopy sTxt, sPath & "Output\" & sPDFname & ".txt" On Error Resume Next Kill sNewPath & "*" 'delete all files in folder Do Until Err.Number = 0 On Error GoTo 0 On Error Resume Next Kill sNewPath & "*" 'delete all files in folder Sleep 200 Loop On Error GoTo 0 End Function

Hi Andre, I don't recommend walking through the code with F8 because the code is trying to find windows and handle them. You might interrupt this by always going back to the VBE. Also, the code you show starts with running Unar. So that seems OK.... I do have a version in which I first try to read the PDF with a different console app (works in case of eg Word files saved as PDF). If this fails then I go for Unar and Tesseract. In any case, I don't think that I have this in the uploaded version on Dropbox.

Hi Eric. Here is an updated version: drive.google.com/file/d/1-vCvJRWg6m6k1d23_fRC2NyQ0q4vsAdP/view?usp=sharing