Free AutoIt Automating PDF extract & Excel Macro

AutoIt v3

is a freeware BASIC-like scripting language designed for automating the Windows GUI and general scripting. It uses a combination of simulated keystrokes, mouse movement and window/control manipulation in order to automate tasks in a way not possible or reliable with other languages (e.g. VBScript and SendKeys). AutoIt is also very small, self-contained and will run on all versions of Windows out-of-the-box with no annoying “runtimes” required!

AutoIt was initially designed for PC “roll out” situations to reliably automate and configure thousands of PCs. Over time it has become a powerful language that supports complex expressions, user functions, loops and everything else that veteran scripters would expect. from here. Download here.

The download and open is not straightforward. The way you use the programme is that you use the SciTE editor to run it. So after downloading the main programme you need to download the editor and start that up. Watch this video:

I found the TutsTeach tutorials very informative and worth following, especially the later ones. I also followed and did the early tutorials and found them good at practicing the programme:

Process. Part 1. 3D PDF export to CSV

3D PDF extract Parameters to CSV file. This is a 2nd attempt at the process but with AutoIT instead of UiPath.  I have this running.

The code is in this file, to a point. I’ve been playing with other parts of it to see if I an extend it, which I may do later.

The issue with the 3DPDF Model Tree part of the programme does not have any listed items in the menu bar, you have to mouse click on them, also they are grouped within a box so Mouse Click is only way to get at them (so far for me), as well as the sub menu.

So the basic process works. I may come back to it and compile it later, for the time being I have the export file I want.

A few issues arose, for 3D PDF’s the Adobe Reader blocks opening them unless you “Trust” the file. So I’d need to add a line in to manage that too.

I Maximized  programme on screen  & also set AutoIT Window Information to “Window” so that it would work on different screen sizes (yet to test though).

Part 2. Excel & Macro

The 2nd part of the process is to open up Excel Macro Workbook which opens on the “Information” sheet, move to Macro sheet,  Activate Macro to Delete all the data in the sheet, Activate Macro to Import CSV file ans lets the user select the file, note there is a sleep of 9 seconds to allow this to happen, Activate Macro to Filter only Room Data,  Activate Macro to copy Room Data to another sheet, Activate Macro to Export Sheet to new Excel file with user choosing new file name and save, then close the Macro Workbook down without saving. Currently, the new file is still open.

#include <Array.au3>
#include <Excel.au3>

Local $oExcel = _Excel_Open()
If @error Then
MsgBox(0, “Error”, “Error creating Excel object”)
_Excel_Close($oExcel)
Exit
EndIf
Local $oWorkbook = _Excel_BookOpen($oExcel, “C:\Users\drake\Downloads\AutoIT\Files\ImportCSVfromPDF.xlsm”, Default, Default)
If @error Then
MsgBox(0, “Error”, “Error opening the workbook”)
_Excel_Close($oExcel)
Exit
EndIf

Sleep(1000)
$oExcel.Sheets (“MacroSheet” ).Select
Sleep(100)
WinActivate(“ImportCSVfromPDF.xlsm – Excel”)
$oExcel.run(“DeleteInfo”)
Sleep(1000)
$oExcel.run(“GetCSVList”)
Sleep(9000)
$oExcel.run(“Room”)
Sleep(500)
$oExcel.run(“RoomsCopyTransposeAllVariables”)
Sleep(5000)
$oExcel.run(“ExportRangetoExcel”)
; ROOM5
;Sleep(9000)
_Excel_BookClose($oWorkbook, False)

The macro’s themselves are quite robust. I’ve been using this workbook for a few years. The Excel coding in AutoIT was definitely easier than the PDF coding above.

Combining the process

I could combine both of these processes into one file, but if the first one falls over (PDF process crashes)  its hard to get to the 2nd (Excel process).  A nifty solution to this is to have 2 different executable files and have a main programme with a GUI interface, as per the 11 serries videos below (first one shown):

That way you have an interface and can choose which part of the process you want to run.

Resources

In AutoIt there are UDF’s (User Defined Functions) and a library of them can be found here. These are “add-In Libraries” that can be called withing a file with the #include <Excel.au3>, which, like Python, is a handy way to make the programme more general.

From my preliminary viewing there is a bias towards IE and I’ve had to fossik for a Firefox UDF.  I haven’t tried this out yet.

There seems to be a lot of videos on YouTube and also forums discussing the programme, some going back quite a few years, so the programme has been around and used well in that time. Its good to see.

Some Handy things to know

Getting Help on a function with Help file

I also found this video useful on how to use the help file:

Commenting out code:

As you are always running bits of code, sometimes you need comments telling what the code is doing, sometimes you want to comment out a lot of code.

“;”  is single line commenting.

For big blocks you can also use : #cs  at front (comment start) and #ce at end (comment end)

Select several lines of code and press Hotkeys Ctrl + Q.

Testing code:

F5 for testing the code. Easy.

If your code locks up then you need to use Ctrl + Break, but my Surface Book  laptop doesn’t have the “Break” key so I have to go to , in the SciTE editor Tools and in the pull down choose ” Stop Execute”. Not as efficient. Posts suggest creating a HotKey with AutoHotKey to get around this.

Directory where you can put UDF’s:

Note, in posts they suggest you put them elsewhere as if you update the programme it overwrites the directory, but they are located in :

C:\Program Files (x86)\AutoIt3\Include

End Thoughts

A bit more coding than UiPath, but:

1. Its free. So no worries about limitations of a Community Edition (CE).
2. Lots of resources .
3. Compiles to executable .exe  files, so very transportable. Can be activated  easily by others (unlike UiPath) .

So, I like it a lot. So far, its been within the range of my searching online and programming skills. These are baby steps.

In 2 days I’m happy with my headway and have some functioning (crude) code to show for it. So I have a result, one day of learning, one day of coding. More than I can say for UiPath.

This programme is more my Forte. Its free and easy to share, its robust and its extensive, and its transportable. I’m looking forward to doing lots more with it.