{"id":4811,"date":"2019-01-11T17:22:27","date_gmt":"2019-01-11T17:22:27","guid":{"rendered":"https:\/\/max-drake.cc\/?p=4811"},"modified":"2019-02-14T11:04:05","modified_gmt":"2019-02-14T11:04:05","slug":"free-autoit-automating-pdf-extract-excel-macro","status":"publish","type":"post","link":"https:\/\/max-drake.cc\/?p=4811","title":{"rendered":"Free AutoIt Automating PDF extract &#038; Excel Macro"},"content":{"rendered":"<h2><em>AutoIt v3<\/em><\/h2>\n<p><em> 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 \u201cruntimes\u201d required!<\/em><\/p>\n<p><em>AutoIt was initially designed for PC \u201croll out\u201d 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. <a href=\"https:\/\/www.autoitscript.com\/site\/autoit\/\" target=\"_blank\" rel=\"noopener\"><strong>from here<\/strong><\/a>. Download <a href=\"https:\/\/www.autoitscript.com\/site\/autoit\/downloads\/\" target=\"_blank\" rel=\"noopener\"><strong>here<\/strong><\/a>.<br \/>\n<\/em><\/p>\n<p>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:<br \/>\n<iframe data-src=\"https:\/\/www.youtube.com\/embed\/ho-PvySWM0g\" width=\"560\" height=\"315\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" class=\"lazyload\" data-load-mode=\"1\"><\/iframe><\/p>\n<p>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:<br \/>\n<iframe data-src=\"https:\/\/www.youtube.com\/embed\/uz3bnrUKhW8\" width=\"560\" height=\"315\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" class=\"lazyload\" data-load-mode=\"1\"><\/iframe><\/p>\n<h2>Process. Part 1. 3D PDF export to CSV<\/h2>\n<p>3D PDF extract Parameters to CSV file. This is a 2nd attempt at the process but with AutoIT instead of UiPath.\u00a0 I have this running.<\/p>\n<p>The code is in this file, to a point. I&#8217;ve been playing with other parts of it to see if I an extend it, which I may do later.<\/p>\n<p>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.<\/p>\n<p><img decoding=\"async\" class=\"wp-image-4813 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im41-300x184.jpg\" alt=\"\" width=\"1681\" height=\"1031\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im41-300x184.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im41-768x471.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im41-1024x628.jpg 1024w\" data-sizes=\"(max-width: 1681px) 100vw, 1681px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1681px; --smush-placeholder-aspect-ratio: 1681\/1031;\" \/><\/p>\n<p><img decoding=\"async\" class=\"wp-image-4814 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im40-300x200.jpg\" alt=\"\" width=\"1788\" height=\"1192\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im40-300x200.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im40-768x512.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im40-1024x682.jpg 1024w\" data-sizes=\"(max-width: 1788px) 100vw, 1788px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1788px; --smush-placeholder-aspect-ratio: 1788\/1192;\" \/><\/p>\n<p><img decoding=\"async\" class=\"wp-image-4815 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im42-300x222.jpg\" alt=\"\" width=\"1655\" height=\"1225\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im42-300x222.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im42-768x567.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im42-1024x757.jpg 1024w\" data-sizes=\"(max-width: 1655px) 100vw, 1655px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1655px; --smush-placeholder-aspect-ratio: 1655\/1225;\" \/><\/p>\n<p>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.<\/p>\n<p>A few issues arose, for 3D PDF&#8217;s the Adobe Reader blocks opening them unless you &#8220;Trust&#8221; the file. So I&#8217;d need to add a line in to manage that too.<\/p>\n<p>I Maximized\u00a0 programme on screen\u00a0 &amp; also set AutoIT Window Information to &#8220;Window&#8221; so that it would work on different screen sizes (yet to test though).<\/p>\n<p><img decoding=\"async\" class=\"wp-image-4816 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im43-300x260.jpg\" alt=\"\" width=\"1057\" height=\"916\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im43-300x260.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im43-768x667.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im43.jpg 871w\" data-sizes=\"(max-width: 1057px) 100vw, 1057px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1057px; --smush-placeholder-aspect-ratio: 1057\/916;\" \/><\/p>\n<h2>Part 2. Excel &amp; Macro<\/h2>\n<p>The 2nd part of the process is to<strong> open up Excel Macro Workbook<\/strong> which opens on the &#8220;Information&#8221; sheet, <strong>move to Macro sheet<\/strong>,\u00a0 <strong>Activate Macro<\/strong> <strong>to Delete<\/strong> all the data in the sheet, <strong>Activate Macro to Import CSV file<\/strong> ans lets the user select the file, note there is a sleep of 9 seconds to allow this to happen, <strong>Activate Macro to Filter only Room Data<\/strong>,\u00a0 <strong>Activate Macro to copy Room Data to another sheet<\/strong>, <strong>Activate Macro to Export Sheet to new Excel file <\/strong>with user choosing new file name and save, then close the Macro Workbook down without saving. Currently, the new file is still open.<\/p>\n<p><em>#include &lt;Array.au3&gt;<\/em><br \/>\n<em>#include &lt;Excel.au3&gt;<\/em><\/p>\n<p><em>Local $oExcel = _Excel_Open()<\/em><br \/>\n<em>If @error Then<\/em><br \/>\n<em>MsgBox(0, &#8220;Error&#8221;, &#8220;Error creating Excel object&#8221;)<\/em><br \/>\n<em>_Excel_Close($oExcel)<\/em><br \/>\n<em>Exit<\/em><br \/>\n<em>EndIf<\/em><br \/>\n<em>Local $oWorkbook = _Excel_BookOpen($oExcel, &#8220;C:\\Users\\drake\\Downloads\\AutoIT\\Files\\ImportCSVfromPDF.xlsm&#8221;, Default, Default)<\/em><br \/>\n<em>If @error Then<\/em><br \/>\n<em>MsgBox(0, &#8220;Error&#8221;, &#8220;Error opening the workbook&#8221;)<\/em><br \/>\n<em>_Excel_Close($oExcel)<\/em><br \/>\n<em>Exit<\/em><br \/>\n<em>EndIf<\/em><\/p>\n<p><em>Sleep(1000)<\/em><br \/>\n<em>$oExcel.Sheets (&#8220;MacroSheet&#8221; ).Select<\/em><br \/>\n<em>Sleep(100)<\/em><br \/>\n<em>WinActivate(&#8220;ImportCSVfromPDF.xlsm &#8211; Excel&#8221;)<\/em><br \/>\n<em>$oExcel.run(&#8220;DeleteInfo&#8221;)<\/em><br \/>\n<em>Sleep(1000)<\/em><br \/>\n<em>$oExcel.run(&#8220;GetCSVList&#8221;)<\/em><br \/>\n<em>Sleep(9000)<\/em><br \/>\n<em>$oExcel.run(&#8220;Room&#8221;)<\/em><br \/>\n<em>Sleep(500)<\/em><br \/>\n<em>$oExcel.run(&#8220;RoomsCopyTransposeAllVariables&#8221;)<\/em><br \/>\n<em>Sleep(5000)<\/em><br \/>\n<em>$oExcel.run(&#8220;ExportRangetoExcel&#8221;)<\/em><br \/>\n<em>; ROOM5<\/em><br \/>\n<em>;Sleep(9000)<\/em><br \/>\n<em>_Excel_BookClose($oWorkbook, False)<\/em><\/p>\n<p>The macro&#8217;s themselves are quite robust. I&#8217;ve been using this workbook for a few years. The Excel coding in AutoIT was definitely easier than the PDF coding above.<\/p>\n<p><img decoding=\"async\" class=\"wp-image-4819 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im44-300x281.jpg\" alt=\"\" width=\"1717\" height=\"1608\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1717px; --smush-placeholder-aspect-ratio: 1717\/1608;\" \/><\/p>\n<p><img decoding=\"async\" class=\"wp-image-4818 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im45-297x300.jpg\" alt=\"\" width=\"1653\" height=\"1670\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im45-297x300.jpg 297w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im45-768x775.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im45-1015x1024.jpg 1015w\" data-sizes=\"(max-width: 1653px) 100vw, 1653px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1653px; --smush-placeholder-aspect-ratio: 1653\/1670;\" \/><\/p>\n<p><img decoding=\"async\" class=\"wp-image-4817 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im46-300x133.jpg\" alt=\"\" width=\"1741\" height=\"772\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im46-300x133.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im46-768x340.jpg 768w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im46-1024x453.jpg 1024w\" data-sizes=\"(max-width: 1741px) 100vw, 1741px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1741px; --smush-placeholder-aspect-ratio: 1741\/772;\" \/><\/p>\n<h2>Combining the process<\/h2>\n<p>I could combine both of these processes into one file, but if the first one falls over (PDF process crashes)\u00a0 its hard to get to the 2nd (Excel process).\u00a0 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):<br \/>\n<iframe data-src=\"https:\/\/www.youtube.com\/embed\/KpwMYarYwR8\" width=\"560\" height=\"315\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" class=\"lazyload\" data-load-mode=\"1\"><\/iframe><\/p>\n<p>That way you have an interface and can choose which part of the process you want to run.<\/p>\n<h2>Resources<\/h2>\n<p>In AutoIt there are UDF&#8217;s (User Defined Functions) and a library of them can be found <a href=\"https:\/\/www.autoitscript.com\/wiki\/User_Defined_Functions#Other_Applications\" target=\"_blank\" rel=\"noopener\"><strong>here<\/strong><\/a>. These are &#8220;add-In Libraries&#8221; that can be called withing a file with the <em><strong>#include<\/strong> &lt;Excel.au3&gt;, <\/em>which, like Python, is a handy way to make the programme more general.<\/p>\n<p>From my preliminary viewing there is a bias towards IE and I&#8217;ve had to fossik for a Firefox UDF.\u00a0 I haven&#8217;t tried this out yet.<\/p>\n<p>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.<\/p>\n<h3>Some Handy things to know<\/h3>\n<h4>Getting Help on a function with Help file<\/h4>\n<p><img decoding=\"async\" class=\"wp-image-4820 aligncenter lazyload\" data-src=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im47-300x137.jpg\" alt=\"\" width=\"1739\" height=\"794\" data-srcset=\"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im47-300x137.jpg 300w, https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im47-768x350.jpg 768w\" data-sizes=\"(max-width: 1739px) 100vw, 1739px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1739px; --smush-placeholder-aspect-ratio: 1739\/794;\" \/><\/p>\n<p>I also found this video useful on how to use the help file:<br \/>\n<iframe data-src=\"https:\/\/www.youtube.com\/embed\/s4XzVyhBP3Q\" width=\"560\" height=\"315\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" class=\"lazyload\" data-load-mode=\"1\"><\/iframe><\/p>\n<h4>Commenting out code:<\/h4>\n<p>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.<\/p>\n<p>&#8220;<strong>;<\/strong>&#8221;\u00a0 is single line commenting.<\/p>\n<p>For big blocks you can also use : #cs\u00a0 at front (comment start) and <strong>#ce<\/strong> at end (<strong>c<\/strong>omment <strong>e<\/strong>nd)<\/p>\n<p>Select several lines of code and press Hotkeys <strong>Ctrl + Q<\/strong>.<\/p>\n<h4>Testing code:<\/h4>\n<p><strong>F5<\/strong> for testing the code. Easy.<\/p>\n<p>If your code locks up then you need to use <strong>Ctrl + Break<\/strong>, but my Surface Book\u00a0 laptop doesn&#8217;t have the &#8220;Break&#8221; key so I have to go to , in the SciTE editor Tools and in the pull down choose &#8221; Stop Execute&#8221;. Not as efficient. Posts suggest creating a HotKey with AutoHotKey to get around this.<\/p>\n<h4>Directory where you can put UDF&#8217;s:<\/h4>\n<p>Note, in posts they suggest you put them elsewhere as if you update the programme it overwrites the directory, but they are located in :<\/p>\n<p><strong>C:\\Program Files (x86)\\AutoIt3\\Include<\/strong><\/p>\n<h2>End Thoughts<\/h2>\n<p>A bit more coding than UiPath, but:<\/p>\n<ol>\n<li>Its free. So no worries about limitations of a Community Edition (CE).<\/li>\n<li>Lots of resources .<\/li>\n<li>Compiles to executable .exe\u00a0 files, so very transportable. Can be activated\u00a0 easily by others (unlike UiPath) .<\/li>\n<\/ol>\n<p>So, I like it a lot. So far, its been within the range of my searching online and programming skills. These are baby steps.<\/p>\n<p>In 2 days I&#8217;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.<\/p>\n<p>This programme is more my Forte. Its free and easy to share, its robust and its extensive, and its transportable. I&#8217;m looking forward to doing lots more with it.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":4815,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,204,32,42],"tags":[183,185,184,48,186],"class_list":["post-4811","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-3d-pdf-data-access","category-automation","category-power-bi","category-productivity","tag-autoit","tag-excel-macros","tag-exporting-from-pdf","tag-productivity","tag-windows"],"featured_image_src":"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im42.jpg","featured_image_src_square":"https:\/\/max-drake.cc\/wp-content\/uploads\/2019\/01\/im42.jpg","author_info":{"display_name":"Max Drake","author_link":"https:\/\/max-drake.cc\/?author=1"},"_links":{"self":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts\/4811","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4811"}],"version-history":[{"count":0,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/posts\/4811\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=\/wp\/v2\/media\/4815"}],"wp:attachment":[{"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4811"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4811"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/max-drake.cc\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4811"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}