Видео

Love you too

Remind me again tomorrow. Thanks.

Hi. You asked me to remind you. Please share the code. Thank you

​@@ganeshs1360 Hi Ganesh, I've stored the code here: github.com/Alpsure/automation-windowsUIwrite/tree/main I haven't been able to test it lately. In the next week or 2 I'll go through these old projects and check whether they still work.

Thank you for uploading. I will check this sometime next week

How did it go?

You might have a point.

Thanks! Any other topic that would be useful for you?

Thanks. Let me know if there are any other topics you want a tutorial about. I'm returning.

What's "pol"?

Yes. Make me an offer I can't refuse!

Good question... I'm going through these projects in the next week or 2.

If a popup appears in an ms office window then the vba running in a ms office program like excel will pause until the popup is handled. So you need to run the handling script outside. Eg in a vbs.

@@kalkytron do you know how to do it with C#?

​@@bredmond812 not right away...but I can take a look sometime next week.

@@kalkytron no problem. If I can wait two years for the first response, I can wait some more

@@bredmond812 That's the spirit! Anyway, I've got the C# solution working. I'll upload it to GitHub shortly.

VBA Will pause if a popup appears in a MS Office app. That's why you need to start a separate script (eg in vbs) BEFORE the popup appears such that the launched script can handle the popup. Let me know if I'm still unclear.

I'm not fully getting the question. Please elaborate

Sir, I'll investigate this issue in the coming days.

Sir, I've made slight changes to the code. This version was tested today: github.com/Alpsure/automation-windowsUIwrite/blob/main/write_to_app_from_VBA.vba

Hi, I'm going through these old projects. In the next week or 2 you'll find the links to the code in the updated video description.

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. You can find the original code here: github.com/Alpsure/automation-windowsUIwrite I haven't tested it yet of late but will do so in the next week or two.

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

Still interested?

You could use this solution to handle to popup: github.com/Alpsure/automation-windowsHandling

@kalkytron thanks

Hey. I got a working example in python. Let me know if you would like one in VBA as well!

Sorry we were having network problems and that appears to have been the cause of the problem.

But late...but are you still facing issues?

Hi Bert. It's honestly a pain in the bottom to find good documentation with clear examples on this topic. I'm reviewing these old projects and will update the description with useful documentation if I find some.

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

Make me an offer I can't refuse

Welcome!

Hi Sviatoslav. Is the problem that VBA doesn't go to the next line of code until the extra popup disappeared?

Hello Bart, thank you for reply. Yes, correct. Can you advice some workaround?

Hi Sviatoslav. I've encountered this issue as well in the past. I am not sure where the script is but here is what I did: First, create a vbs script to push the button which loops until the button is found. Next, call the script just before the line where your VBA script stops. If this isn't clear enough then I'll try to explain it in more detail at a later point.

Hi Bart, thank you for reply. I found video on youtube explaining the idea you described - ruclips.net/video/G-2khNFYQl8/видео.html . But I'm just wondering is there way loop for intial window "save as" as well and do it in one vbs script.

Hi Alejandro More. The declaration of the API's is slightly different for the 64bit system than the 32bit system. You need to add "PtrSafe" after "Declare".

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) <= 2 Then '(FileLen returns 2 in case of an empty file) oTxtGet.Close Kill sTXT Else oTxtGet.Close FileCopy sTXT, sPath & "Output\" & sPDFname & ".txt." Kill sTXT Exit Function End If '=================Convert PDF to picture=================

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: ruclips.net/video/YiHVMF5N9BY/видео.html

I will!....with a slight delay, But 100 times the experience and knowledge!

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 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.