Sign in to follow this  
Followers 0
dickjones007

read from excel database into array

15 posts in this topic

#1 ·  Posted (edited)

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 by dickjones007

Share this post


Link to post
Share on other sites



Have you tried the Excel UDF that comes with AutoIt?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

_ExcelCopy isn't a part of Excel.au3.

Where did you find _ExcelCopy?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

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 by dickjones007

Share this post


Link to post
Share on other sites

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.

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

1 person likes this

Share this post


Link to post
Share on other sites

thank you water and GMK

i will try it tomorrow at work :)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#15 ·  Posted (edited)

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 by GMK

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