read from excel database into array

[dickjones007]

Im trying to do what the title says.

In details what im trying to get is this:

i need input dialog for scanned barcode

when i scan products barcode i would like autoit to find that product in excel database

fetch data from 5 columns in that row

then display found info into array (if double code is found) or in a msgbox and copy data to clipboard for further pasting

can i get some help please?

Edited July 21, 2013 by dickjones007

[water]

Have you tried the Excel UDF that comes with AutoIt?

[dickjones007]

i have tried examples for _ExcelReadSheetToArray and other excelread functions and dont get result in array table

[water]

Can you please post the code you tried so far using _ExcelReadSheetToArray?

[dickjones007]

ok so far i lowered my ambitions

ill deal with array later

so basically now all i need to do is read and copy from, lets say, A3:D3 but i cant get _ExcelCopy to work

i always get undefined function for it

#include <Excel.au3>
Local $oExcel = _ExcelBookOpen(@ScriptDir & 'stanjeskladista.xls', 0, 0)
_ExcelCopy($oExcel, "A3:D3")

[water]

_ExcelCopy isn't a part of Excel.au3.

Where did you find _ExcelCopy?

[dickjones007]

here:

'?do=embed' frameborder='0' data-embedContent>>

tried using excel.au3 from that page but doesnt work

so now im trying with _ExcelReadCell

Edited July 22, 2013 by dickjones007

[dickjones007]

now i have this:

#include <Excel.au3>
Local $oExcel = _ExcelBookOpen(@ScriptDir & 'stanjeskladista.xls')
_ExcelReadCell($oExcel,"A3:D3")
$sCellValue = _ExcelReadCell($oExcel, "A3:D3")
    MsgBox(0, "", "Value is: " & @CRLF & $sCellValue)

and i always get Value is: 0

when i add trace lines

@@ Trace(3) :    Local $oExcel = _ExcelBookOpen(@ScriptDir & 'stanjeskladista.xls')
>Error code: 1

@@ Trace(5) :    _ExcelReadCell($oExcel,"A3:D3")
>Error code: 1

@@ Trace(7) :    $sCellValue = _ExcelReadCell($oExcel, "A3:D3")
>Error code: 1

@@ Trace(9) :        MsgBox(0, "", "Value is: " & @CRLF & $sCellValue)

 

BTW i have LibreOffice. is that problem?

Edited July 22, 2013 by dickjones007

[water]

Sure. The Excel UDF only works with - surprise - Microsoft Excel.

If you want to read a Excel workbook with LibreOffice you need the >Open Office Calc UDF.

[GMK]

If you're interested, I have updated many of those functions

[dickjones007]

thank you water and GMK

i will try it tomorrow at work

[dickjones007]

darn it few hrs of trying didnt get me anywhere

can anyone help me with writting the script.

i was trying to read cells from A3 to D10 and getting that info into array table.

file is xls and i have libreoffice (openoffice) installed.

[water]

What have you tried so far?
Can you post your code?

[dickjones007]

I was trying this code and it fails

#Include <OOoCOM_UDF.au3>
#Include <File.au3>

local $FFile = @ScriptDir & "\test.xls"

local $temp_file_name = StringRegExp ($FFile,'.*\\(.*?\.[A-z]{3})',1)
local $Our_file = $temp_file_name[0], $value

$oCurCom = _OOoCalc_Attach($Our_file)
if @error or Not IsObj($oCurCom) then _
   $oCurCom = _OOoCalc_Open($FFile)

$value = _OOoCalc_ReadCell ($oCurCom, 0, 2, 1)
MsgBox(1, "AutoIt", $value)
Exit

it is from 03_CalcGetCellData.au3 file from somewhere on this forum.

i get C:\Program Files\AutoIt3\Include\OOoCOM_UDF.au3 (104) : ==> The requested action with this object has failed.:

[GMK]

Using >my UDF, you can try this:

#include <OOoCalc.au3>

$FFile = @ScriptDir & "\test.xls"

$temp_file_name = StringRegExp($FFile, '.*\\(.*?\.[A-z]{3})', 1)
$Our_file = $temp_file_name[0]

$oCurCom = _OOoCalcBookAttach($Our_file)
If @error Or Not IsObj($oCurCom) Then _
        $oCurCom = _OOoCalcBookOpen($FFile)

$vValue = _OOoCalcReadCell($oCurCom, 0, 2) ; Reads cell C1
MsgBox(1, "AutoIt", $vValue)

Edited July 24, 2013 by GMK