Sign in to follow this  
Followers 0
Bucktail

Query Excel

20 posts in this topic

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?

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

and once it's in csv format, you could import it into sqlite and do a proper job with a proper database!!

William

Share this post


Link to post
Share on other sites

Thanks for the tip. Any ideas on how to start writing this script?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#6 ·  Posted (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 by MrMitchell

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#8 ·  Posted (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 by MrMitchell

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 :unsure:

My example data:

Posted Image

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)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

;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

Share this post


Link to post
Share on other sites

@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

Share this post


Link to post
Share on other sites

@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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

#20 ·  Posted (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 by Bucktail

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0