Bucktail Posted April 13, 2011 Share Posted April 13, 2011 Hoping someone can help here and point me in the right direction. I have some experience with Autoit be at a very limited level. The script I would like to write is beyond my current knowledge. Hope someone can show me how to write or at least point me to a previous post that may possibly answer my request. I need Autoit to query an Excel document and return some specs. Below is a more in-depth steps I'm looking for. I would start with an Excel document that has part numbers and specs from one maybe two cells. Want Autoit to open a Input Box asking for a Part Number The user enters a part number and Autoit will then go to the excel document and look for this part number. If the requested part number is valid, Autoit will return the specs for this part number. "This part number uses this spec XXXXXXXX" If the part number is not there Autoit will return indicating that this is not a valid part number. If I cancel the script I would like Autoit to then close the Excel document and it self. Also can Autoit read the excel document without opening it or does excel need to be run? Link to comment Share on other sites More sharing options...
Xenobiologist Posted April 13, 2011 Share Posted April 13, 2011 Should be no problem. Besides, I would convert the excel file to a csv file. This way, you will not need excel to check the file. Scripts & functions Organize Includes Let Scite organize the include files Yahtzee The game "Yahtzee" (Kniffel, DiceLion) LoginWrapper Secure scripts by adding a query (authentication) _RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...) Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc. MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times Link to comment Share on other sites More sharing options...
saywell Posted April 13, 2011 Share Posted April 13, 2011 and once it's in csv format, you could import it into sqlite and do a proper job with a proper database!! William Link to comment Share on other sites More sharing options...
Bucktail Posted April 13, 2011 Author Share Posted April 13, 2011 Thanks for the tip. Any ideas on how to start writing this script? Link to comment Share on other sites More sharing options...
dara Posted April 13, 2011 Share Posted April 13, 2011 Thanks for the tip. Any ideas on how to start writing this script?I don't know how to hide excel but you can hide OpenOffice Calc or LibreOffice Calc when working on spreadsheets.ooCalc must still be installed (they are free anyway). Link to comment Share on other sites More sharing options...
MrMitchell Posted April 13, 2011 Share Posted April 13, 2011 (edited) Thanks for the tip. Any ideas on how to start writing this script?If you go the Excel route then you need the Excel UDF (#include <Excel.au3>) and refer to the Excel functions in the help file. For example you'd need _ExcelBookOpen() and _ExcelReadSheetToArray() or _ExcelReadCell(). This isn't the recommended method.Where does the data from the Excel spreadsheet come from? Perhaps you can query that data source directly?Edit: If you're just using Excel to open the original file which is in fact just a CSV file, highly recommend what Xeno suggested, and it would be faster. Edited April 13, 2011 by MrMitchell Link to comment Share on other sites More sharing options...
Bucktail Posted April 13, 2011 Author Share Posted April 13, 2011 The Excel document is a list of part numbers and their specs that I created. So it could be in xls or csv. Doesn't matter to me. I can't imagine that this would have to be a very long script. I just have no idea what this script would be like. I was hoping for some examples from the experts here to learn from. Link to comment Share on other sites More sharing options...
MrMitchell Posted April 13, 2011 Share Posted April 13, 2011 (edited) I can't imagine that this would have to be a very long script. I just have no idea what this script would be like. I was hoping for some examples from the experts here to learn from._ExcelReadCell() documentation in the help file has an example of how to create a new excel spreadsheet then use a loop to read through the cells. Check it out and post a question about anything that doesn't make sense... I'd post an example closer to what you're trying to do but I have to leave now, sorry.Since the data originates from you, you should use a real database to store/modify/retrieve the data as suggested earlier. Of course if you're pressed for time and just want to get something running, the Excel functions should work just fine. Either way you go you'll get plenty of help, just probably won't get someone to write the whole thing. Edited April 13, 2011 by MrMitchell Link to comment Share on other sites More sharing options...
ptrex Posted April 14, 2011 Share Posted April 14, 2011 @AllMaybe this can help ?Rgds,ptrex Contributions :Firewall Log Analyzer for XP - Creating COM objects without a need of DLL's - UPnP support in AU3Crystal Reports Viewer - PDFCreator in AutoIT - Duplicate File FinderSQLite3 Database functionality - USB Monitoring - Reading Excel using SQLRun Au3 as a Windows Service - File Monitor - Embedded Flash PlayerDynamic Functions - Control Panel Applets - Digital Signing Code - Excel Grid In AutoIT - Constants for Special Folders in WindowsRead data from Any Windows Edit Control - SOAP and Web Services in AutoIT - Barcode Printing Using PS - AU3 on LightTD WebserverMS LogParser SQL Engine in AutoIT - ImageMagick Image Processing - Converter @ Dec - Hex - Bin -Email Address Encoder - MSI Editor - SNMP - MIB ProtocolFinancial Functions UDF - Set ACL Permissions - Syntax HighLighter for AU3ADOR.RecordSet approach - Real OCR - HTTP Disk - PDF Reader Personal Worldclock - MS Indexing Engine - Printing ControlsGuiListView - Navigation (break the 4000 Limit barrier) - Registration Free COM DLL Distribution - Update - WinRM SMART Analysis - COM Object Browser - Excel PivotTable Object - VLC Media Player - Windows LogOnOff Gui -Extract Data from Outlook to Word & Excel - Analyze Event ID 4226 - DotNet Compiler Wrapper - Powershell_COM - New Link to comment Share on other sites More sharing options...
Xenobiologist Posted April 14, 2011 Share Posted April 14, 2011 Just post a example csv file and what you need from it. Should be 5 min work to get the script done. Read csv, putting data into an array, get the needed data. Scripts & functions Organize Includes Let Scite organize the include files Yahtzee The game "Yahtzee" (Kniffel, DiceLion) LoginWrapper Secure scripts by adding a query (authentication) _RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...) Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc. MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times Link to comment Share on other sites More sharing options...
MrMitchell Posted April 14, 2011 Share Posted April 14, 2011 Ok an example of opening excel given the file name, read the table/sheet into an array, then loop thru the array to find the part number you're looking for then use that row number to get specs from the same row in the next column. This is very quick and dirty and needs a lot of cleaning up but you get the idea... hopefully My example data:And code:#include <Excel.au3> #include <Array.au3> $sSourceFile = "C:\temp\test.xls" $oExcel = _ExcelBookOpen($sSourceFile) $partToFind = "123abc" ConsoleWrite ($partToFind & ": " & _FindPart($partToFind) & @CRLF) $partToFind = "something I don't have" ConsoleWrite ($partToFind & ": " & _FindPart($partToFind) & @CRLF) Func _FindPart($part) Local $return = "Not Found!" $aSheet = _ExcelReadSheetToArray($oExcel, 2, 1, 0, 2) ;Read sheet starting from row 2, column 1, read all rows, read only 2 columns ;~ _ArrayDisplay($aSheet) ;Uncomment to display the array just to get a visual on the last function For $i = 1 To $aSheet[0][0] ;[0][0] is number of rows, [0][1] is number of columns ;~ ConsoleWrite($aSheet[$i][1] & ", " & $aSheet[$i][2] & @CRLF) ;Uncomment to list everything as an example If StringCompare($aSheet[$i][1], $partToFind) = 0 Then $return = $aSheet[$i][2] Next Return $return EndFunc _ExcelBookClose($oExcel, 0) Link to comment Share on other sites More sharing options...
Bucktail Posted April 14, 2011 Author Share Posted April 14, 2011 In response to Xenobiologist. The CSV file consists of two columns of numbers. Column A is the part number to search and Column B is the spec that I would like returned. Does that help? Thanks for the help. Link to comment Share on other sites More sharing options...
Xenobiologist Posted April 14, 2011 Share Posted April 14, 2011 Hi, not that hard. Here is a very short and simple solution. ;Your file --> ;~ Number;Specification ;~ 1;Test ;~ 2;Hugo ;~ 3;Mega ;~ 4;Yes ;~ 5;NO #include<Array.au3> Global $content = FileRead(@ScriptDir & '\lockup.csv') Global $re = StringRegExp($content, '(\w+);|\w+', 3) _ArrayDisplay($re) $nr = InputBox('Title', 'Number:') $result = _ArraySearch($re, $nr) If @error Then MsgBox(16, 'error', 'Not found Nr: ' & $nr) Else MsgBox(64, 'Specification is : ', $re[$result + 1]) EndIf Mega Scripts & functions Organize Includes Let Scite organize the include files Yahtzee The game "Yahtzee" (Kniffel, DiceLion) LoginWrapper Secure scripts by adding a query (authentication) _RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...) Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc. MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times Link to comment Share on other sites More sharing options...
Bucktail Posted April 14, 2011 Author Share Posted April 14, 2011 I will take a look at the script you put together and see if I can understand how to get it to work for me. I should have posted this example earlier. I am looking for some thing that would work similar to this script I made by copying a script I found and changed to work for me. I just don't know what commands would be needed to make a script to search column A of the csv file for the requested part number and return the information that is in column B in the cell next to the part number I am searching. ; Checks for PDF file and then opens the requested PDF file. $path = "\\CorpData\Data\drawingpdfs\" $ext = ".pdf" ; Loops until the user gives a valid PDF file or cancels $bLoop = 1 While $bLoop = 1 $file = InputBox("PDF File To Open", "Please enter in the PDF file number you want to open and click OK","","",250,175) If @error = 1 Then Exit Else $filepath = ($path & $file & $ext) If FileExists($filepath) Then ShellExecute( $filepath, "" , "" , "" , @SW_MAXIMIZE ) $bLoop = 0 ; Exit the loop - ExitLoop would have been an alternative too :) Else MsgBox(0, " NOT VALID", "Not a Valid Number!") EndIf EndIf WEnd Link to comment Share on other sites More sharing options...
Xenobiologist Posted April 14, 2011 Share Posted April 14, 2011 Okay, if it is just a loopup you need to get done then I would go for an ini file. Should be very easy to create for you [PDF-FILES] 1=bla.pdf 2=hugo.pdf 3=mix.pdf 4=andsoon.pdf Now you can use the INI-Functions of Autoit, which should make your job easier than before. expandcollapse popup;Your file --> ;~ [PDF-FILES] ;~ 1=bla.pdf ;~ 2=hugo.pdf ;~ 3=mix.pdf ;~ 4=andsoon.pdf #include <ButtonConstants.au3> #include <EditConstants.au3> #include <GUIConstantsEx.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #Region ### START Koda GUI section ### Form= $Form1 = GUICreate("Form1", 282, 447, 192, 124) $Group1 = GUICtrlCreateGroup("Group1", 8, 0, 265, 441) $Edit1 = GUICtrlCreateEdit("", 24, 80, 233, 353) GUICtrlSetData(-1, "Edit1") $Input1 = GUICtrlCreateInput("Input1", 80, 16, 177, 21) $Label1 = GUICtrlCreateLabel("PDF-NR", 24, 16, 44, 17) $Button1 = GUICtrlCreateButton("Open PDF", 16, 48, 235, 25, $WS_GROUP) GUICtrlCreateGroup("", -99, -99, 1, 1) GUISetState(@SW_SHOW) #EndRegion ### END Koda GUI section ### Global $path = @ScriptDir & '\', $file = 'lockup.ini' GUICtrlSetData($Edit1, FileRead($path & $file)) While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $Button1 ;~ If FileExists($path & IniRead($path & $file, 'PDF-FILES', GUICtrlRead($Input1), '')) Then ConsoleWrite('!' & $path & IniRead($path & $file, 'PDF-FILES', GUICtrlRead($Input1), '') & @CRLF) ;~ ShellExecute($path & IniRead($path & $file, 'PDF-FILES', GUICtrlRead($Input1), "", "", "", @SW_MAXIMIZE) ;~ EndIf EndSwitch WEnd Scripts & functions Organize Includes Let Scite organize the include files Yahtzee The game "Yahtzee" (Kniffel, DiceLion) LoginWrapper Secure scripts by adding a query (authentication) _RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...) Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc. MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times Link to comment Share on other sites More sharing options...
Bucktail Posted April 14, 2011 Author Share Posted April 14, 2011 @Xenobiologist I tried the last example you posted and it prompts for a PDF what I posted as an example is something I used for my PDF file search script. This one needs to search a csv file. I'm having some success with the first one you posted. I just need to find some time to understand it some more. I'll let you know if I need some help. Thanks Link to comment Share on other sites More sharing options...
Bucktail Posted April 14, 2011 Author Share Posted April 14, 2011 @Xenobiologist The first script you wrote is sort of working. I just need to see if it can be cleaned up and looped. Right now what you have written returns only the first number. for example. If I search for 12347 I only get "16" When what I want returned is what was in the cell "16 - 0.25" Here is a sample of my data in my csv file. 12345 22 - 0.12 12346 22 - 0.25 12347 16 - 0.25 12348 16 - 0.25 12349 20 - 0.31 Also Is there a way to NOT display the Array? Link to comment Share on other sites More sharing options...
Bucktail Posted April 14, 2011 Author Share Posted April 14, 2011 OK here is what I was able to put together with the help of the script from Xenobiologist and combining it with what I had used in a past script. For some reason when I click on the cancel button it does not exit out completely and I don't understand why. I also need it to loop back to the InputBox if a bogus number is given. Still would like to know if there a way to NOT display the Array? My plan is to use this out on the shop floor and the array popup with confuse the user. Anybody with some answers for me? ;Your file --> ;~ Number;Specification ;~ 1;Test ;~ 2;Hugo ;~ 3;Mega ;~ 4;Yes ;~ 5;NO #include<Array.au3> Global $content = FileRead('C:\WireStrip\wirepns.csv') Global $re = StringRegExp($content, '(\w+);|\w+', 3) _ArrayDisplay($re) ; Loops until the user gives a valid file or cancels $bLoop = 1 While $bLoop = 1 $nr = InputBox("Wire Spec To Find", "Please enter the file number spec you want to find and click OK","","",250,175) $result = _ArraySearch($re, $nr) If @Error = 1 Then ; Exit if Cancel is chosen. Exit Else If @error Then MsgBox(16, 'ERROR', 'No Spec Found for ' & $nr) $bLoop = 0 ; Exit the loop - ExitLoop would have been an alternative too :) Else MsgBox(0, 'Spec Is : ', $re[$result + 1]) Exit EndIf EndIf WEnd Link to comment Share on other sites More sharing options...
MrMitchell Posted April 15, 2011 Share Posted April 15, 2011 Still would like to know if there a way to NOT display the Array? My plan is to use this out on the shop floor and the array popup with confuse the user.Anybody with some answers for me?Try removing from your code:_ArrayDisplay($re) Link to comment Share on other sites More sharing options...
Bucktail Posted April 15, 2011 Author Share Posted April 15, 2011 (edited) OK in case anyone is wondering I was able to figure it out. Here is what I came up with. Thanks to those that helped. I was able to learn something. #include<Array.au3> Global $content = FileRead('C:\WireStrip\wirepns.csv') Global $re = StringRegExp($content, '(\w+);|\w+', 3) ;_ArrayDisplay($re) ; Loops until the user gives a valid file or cancels $bLoop = 1 While $bLoop = 1 $nr = InputBox("Wire Spec To Find", "Please enter the file number you want to find and click OK","","",200,175) If @Error = 1 Then ; Exits if Cancel is chosen. Exit Else $result = _ArraySearch($re, $nr) If @error Then MsgBox(16, 'ERROR', 'No Spec Found for ' & $nr) Else MsgBox(0, 'Spec Is : ', $nr & " = " &$re[$result + 1] & " - "&$re[$result + 2] & $re[$result + 3]) ; this line returns 3 results. $bLoop = 0 ; Exit the loop - ExitLoop would have been an alternative too :) EndIf EndIf WEnd Edited April 15, 2011 by Bucktail Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now