Tricky Excel Question

[judgedk]

Hi,

Is it possible to somehow search a excel sheet for at specific number in a row then go 3 cells out and copy that number to at destination sheet..

pls let me demostrate what i mean.

I have a code that extract some data to a excel sheet..

when i run this in store A i get this result, pls see pic 1.jpg

when i run this in store B i get this result, pls see pic 2.jpg

At store A there is a department number 059, that number isen't in Store B

so when i copy from store A's sheet i get one result and another when i copy from store B's sheet

like this

$C1 = _ExcelReadCell($1Excel, "H13:I13")
_ExcelWriteCell($0Excel, $C1, "B20:C20")
$C1 = _ExcelReadCell($1Excel, "J13:K13")
_ExcelWriteCell($0Excel, $C1, "E20:F20")
$C1 = _ExcelReadCell($1Excel, "L13:M13")
_ExcelWriteCell($0Excel, $C1, "H20:I20")
$C1 = _ExcelReadCell($1Excel, "O13:P13")

Hope this make any sense..

[Envoi]

Hi,

Is it possible to somehow search a excel sheet for at specific number in a row then go 3 cells out and copy that number to at destination sheet..

Here is a quick example:

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

;Attach to your Excel books.
$oExcel1 = _ExcelBookAttach("c:\Book1.xls")
$oExcel2 = _ExcelBookAttach("c:\Book2.xls")

;Read the first column of each sheet into an array.
;Notice I'm only reading the first 10 rows.
$array1 = _ExcelReadArray($oExcel1, 1, 1, 10, 1)
$array2 = _ExcelReadArray($oExcel2, 1, 1, 10, 1)

;See if array2 contains each value in array1.
For $i = 0 to Ubound($array2 - 1)
    ;If there wasn't a match...
    If _ArraySearch($array2, $array1[$i]) = -1 Then
        ;Make a new row to insert the data into.
        _ExcelRowInsert($oExcel2, 1, 1)
        ;Write the value from column 1 of book1 into book2.
        _ExcelWriteCell($oExcel2, $array1[$i], 1, 1)
        ;Read the value from column 3 of book1
        $value = _ExcelReadCell($oExcel1, $i + 1, 3)
        ;Write that into column3 of book2
        _ExcelWriteCell($oExcel2, $value, 1, 3)
    EndIf
Next

This is very rough, but will hopefully get you going.

BTW: I think this would be a breeze in VBA. Any reason you're forcing this in AutoIt?

[judgedk]

Here is a quick example:

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

;Attach to your Excel books.
$oExcel1 = _ExcelBookAttach("c:\Book1.xls")
$oExcel2 = _ExcelBookAttach("c:\Book2.xls")

;Read the first column of each sheet into an array.
;Notice I'm only reading the first 10 rows.
$array1 = _ExcelReadArray($oExcel1, 1, 1, 10, 1)
$array2 = _ExcelReadArray($oExcel2, 1, 1, 10, 1)

;See if array2 contains each value in array1.
For $i = 0 to Ubound($array2 - 1)
    ;If there wasn't a match...
    If _ArraySearch($array2, $array1[$i]) = -1 Then
        ;Make a new row to insert the data into.
        _ExcelRowInsert($oExcel2, 1, 1)
        ;Write the value from column 1 of book1 into book2.
        _ExcelWriteCell($oExcel2, $array1[$i], 1, 1)
        ;Read the value from column 3 of book1
        $value = _ExcelReadCell($oExcel1, $i + 1, 3)
        ;Write that into column3 of book2
        _ExcelWriteCell($oExcel2, $value, 1, 3)
    EndIf
Next

This is very rough, but will hopefully get you going.

BTW: I think this would be a breeze in VBA. Any reason you're forcing this in AutoIt?

Cause i don't know anything in Visual Basic.. if its much easier to do in VBA i'll have a look at it..

Thanks for the reply.

[Envoi]

Cause i don't know anything in Visual Basic.. if its much easier to do in VBA i'll have a look at it..

Thanks for the reply.

That's a perfectly good reason!