# Tricky Excel Question

## Recommended Posts

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

_ExcelWriteCell(\$0Excel, \$C1, "B20:C20")
_ExcelWriteCell(\$0Excel, \$C1, "E20:F20")
_ExcelWriteCell(\$0Excel, \$C1, "H20:I20")

Hope this make any sense..

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

\$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 on other sites

Here is a quick example:

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

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

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

That's a perfectly good reason!

## Create an account

Register a new account