judgedk Posted February 18, 2009 Share Posted February 18, 2009 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.. Link to comment Share on other sites More sharing options...
Envoi Posted February 18, 2009 Share Posted February 18, 2009 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? Link to comment Share on other sites More sharing options...
judgedk Posted February 18, 2009 Author Share Posted February 18, 2009 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. Link to comment Share on other sites More sharing options...
Envoi Posted February 18, 2009 Share Posted February 18, 2009 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! Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now