Sign in to follow this  
Followers 0
judgedk

Tricky Excel Question

4 posts in this topic

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

post-30523-1234973877_thumb.jpg

post-30523-1234973887_thumb.jpg

Share this post


Link to post
Share on other sites



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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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! :)

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