Jump to content
Sign in to follow this  
lafafmentvotre

Obtain cells value in excel

Recommended Posts

lafafmentvotre

Hi everybody

First sorry for my bad english.

Second, i need a script to read an excel file and read full cells and put these in a array.

I use this :

;###################################################################
;AutoIT Variables
#include <Array.au3>
#include <Excel.au3>
;###################################################################
;Script Variables
Local $List

;~ Func _GetOU($ID)
$FileName=@ScriptDir&"\Site_OU.xls"
$oExcel = ObjGet($FileName)
$aArray1 = _ExcelReadArray($oExcel, 2, 1, 10, 1) ;Direction is Vertical
$aArray2=_ArrayDisplay($aArray1, "Vertical")

The problem is to have only the full cells.

If i wrote value in cells after range A7, i haven't value in array.

And if my cells value end in A20 in excel, i haven't value.

I need this values to increment a GUICtrlCreateCombo.

Thanks for help and really sorry for details and english

Edited by lafafmentvotre

Share this post


Link to post
Share on other sites
KenNichols

Hi everybody

First sorry for my bad english.

Second, i need a script to read an excel file and read full cells and put these in a array.

I use this :

;###################################################################
;AutoIT Variables
#include <Array.au3>
#include <Excel.au3>
;###################################################################
;Script Variables
Local $List

;~ Func _GetOU($ID)
$FileName=@ScriptDir&"\Site_OU.xls"
$oExcel = ObjGet($FileName)
$aArray1 = _ExcelReadArray($oExcel, 2, 1, 10, 1) ;Direction is Vertical
$aArray2=_ArrayDisplay($aArray1, "Vertical")

The problem is to have only the full cells.

If i wrote value in cells after range A7, i haven't value in array.

And if my cells value end in A20 in excel, i haven't value.

Thanks for help and really sorry for details and english

Take a look.

#include <Excel.au3>

Local $oExcel = _ExcelBookNew();Create new book, make it visible

For $i = 1 To 5;Loop
    _ExcelWriteCell($oExcel, $i, $i, 1);Write to the Cell
Next

For $i = 1 To 5;Loop
    $sCellValue = _ExcelReadCell($oExcel, $i, 1)
    If $sCellValue <> "" Then MsgBox(0, "", "The Cell Value is: " & @CRLF & $sCellValue, 2)
Next
Edited by KenNichols

[topic="21048"]New to AutoIt? Check out AutoIt 1-2-3![/topic] Need to make a GUI? You NEED KODA FormDesigner!

Share this post


Link to post
Share on other sites
lafafmentvotre

Thanks for response but i saw this script in help.

My problem is i need to change loop when i write a new value. It's not dynamics.

Share this post


Link to post
Share on other sites
PsaltyDS

Thanks for response but i saw this script in help.

My problem is i need to change loop when i write a new value. It's not dynamics.

I don't understand your problem description. You can get all used cells in one array like this:
#include <Array.au3>
#include <Excel.au3>

Local $oExcel = _ExcelBookNew();Create new book, make it visible

For $i = 1 To 5
    _ExcelWriteCell($oExcel, $i, $i, 1);Write to the Cell
Next
$avSheet = _ExcelReadSheetToArray($oExcel)
_ArrayDisplay($avSheet, "$avSheet")

Now it's just a problem of how you want to access the array.

:D


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
lafafmentvotre

Hello

It's very difficult to explain.

1. In my excel file, i have, for example, these values :

Posted Image

2. I need to read these values (only used cells in column "A" without title "SITE") and grab it in autoit.

3. I need to use these values to update a GUICtrlCreateCombo

My problems are :

1. How to do this ?

2. if i add values in column "A" (like A8, A9, A10, Etc.), how can i grab only all used cells values without change script (dynamics) ?

Thanks for help

Share this post


Link to post
Share on other sites
lafafmentvotre

Hello

It's ok for me.

A friend give me solution :

#include <Excel.au3>
#include <Array.au3>
#include <GUIConstants.au3>

$sFilePath1 = @ScriptDir & "\Site_OU.xls" ;This file should already exist
$oExcel = _ExcelBookOpen($sFilePath1,0)

$aArray2 = _ExcelReadArray($oExcel, 2, 1, 3000, 1) 

_ExcelBookClose($oExcel)

#Region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Form1", 633, 454, 193, 115)
$Combo = GUICtrlCreateCombo("", 88, 120, 353, 25)
$list_combo = ""
For $y=1 to UBound($aArray2) -1
    If $aArray2[($y)] <> "" Then
        $list_combo = $list_combo & "|"
        $list_combo = $list_combo & $aArray2[($y)]
    EndIf   
Next
GUICtrlSetData($Combo, $list_combo) ; add other item snd set a new default
$Button1 = GUICtrlCreateButton("Quitter", 104, 240, 177, 41, 0)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

While 1 
Dim $u_msg 
$u_msg = GUIGetMsg(1) 
    If $u_msg[1] = $Form1 Then 
        Select 
            case  $u_msg[0] = $Button1 Or $u_msg[0] = $GUI_EVENT_CLOSE 
                ExitLoop
        EndSelect 
    EndIf 
Wend

This topic can be archived

Thanks for all

Edited by lafafmentvotre

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  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.