Jump to content

ExcelWriteCell loop


kor
 Share

Recommended Posts

New thread to ask a specific question. I have the following worksheet

Posted Image

Using this code

#include <excel.au3>
#include <array.au3>
 
$file = @ScriptDir & "\test.xls"
$oExcel = _ExcelBookOpen($file, 0) ; open excel in the background
 
$data = _ExcelReadSheetToArray($oExcel, 2) ; ignoring header row
_ArrayDelete($data, 0) ; removes the first row of array to get rid of count
_ArrayDisplay($data, "Array using Default Parameters")
 
For $i = 0 to UBound($data)-1
    $firstname = $data[$i][1]
    $lastname = $data[$i][2]
    $location = $data[$i][3]
    $title = $data[$i][4]
    $classification = $data[$i][5]
    $action = $data[$i][6]
    
    If $action = "transfer" Then
        _test()
    Else
        ; do nothing
    EndIf
Next

Func _test()
    _ExcelWriteCell($oExcel, "success", 0, 8)
EndFunc

_ExcelBookClose($oExcel) ; close excel

What I want to do, is write a value of "success" to column H in the excel sheet. But I want to do it only on the rows that the condition "transfer" is met. Thus if my code *DID* work, the result would look like this. How do I get there?

Posted Image

Link to comment
Share on other sites

Never worked with the excel object or messed around with it.

But if it's bringing it into an array, you can manipulate the array and using _ExcelWriteSheetFromArray you can rewrite the whole array back to the spreadsheet.

#include <excel.au3>
#include <array.au3>

$file = @ScriptDir & "\test.xls"
$oExcel = _ExcelBookOpen($file, 0) ; open excel in the background

$bModified = False
$data = _ExcelReadSheetToArray($oExcel, 1,1)

_ArrayDisplay($data, "Array using Default Parameters")

ReDim $data[UBound($data)][UBound($data, 2) + 1]
$data[0][1] += 1

For $i = 0 to UBound($data) - 1
    If StringInStr($data[$i][6], "transfer") Then
        $bModified = True
        $data[$i][7] = "Success!"
    EndIf
Next

_ArrayDisplay($data, "Final Array")

If $bModified Then _ExcelWriteSheetFromArray($oExcel, $data)

_ExcelBookClose($oExcel) ; close excel
Link to comment
Share on other sites

I'm not wanting to read the entire sheet into an array and re-write it after every loop. That is too inefficient. Besides, thats what the ExcellWriteCell is for! Anyone have any ideas how to increment the row # after every loop so the "success" ends up on the correct line?

Link to comment
Share on other sites

"success" ends up on the correct line?

Try this:

#include <excel.au3>
#include <array.au3>

$file = @ScriptDir& "\test.xls"
$oExcel = _ExcelBookOpen($file, 0) ; open excel in the background

$data = _ExcelReadSheetToArray($oExcel, 2) ; ignoring header row
_ArrayDelete($data, 0) ; removes the first row of array to get rid of count
_ArrayDisplay($data, "Array using Default Parameters")

For $i = 0 to UBound($data)-1
    $firstname = $data[$i][1]
    $lastname = $data[$i][2]
    $location = $data[$i][3]
    $title = $data[$i][4]
    $classification = $data[$i][5]
    $action = $data[$i][6]

    If $action = "transfer" Then
        _test()
    Else
        ; do nothing
    EndIf
Next

Func _test()
    _ExcelWriteCell($oExcel, "success", $i + 2, 8);<< =====  Here?
EndFunc

_ExcelBookClose($oExcel) ; close excel
Link to comment
Share on other sites

I'm not wanting to read the entire sheet into an array and re-write it after every loop. That is too inefficient. Besides, thats what the ExcellWriteCell is for! Anyone have any ideas how to increment the row # after every loop so the "success" ends up on the correct line?

You can try by not doing this

_ArrayDelete($data, 0) ; removes the first row of array to get rid of count

That is the information you need - it's telling you how many columns/rows. Also with the example I gave you you're not rewriting the whole array after every loop. It gets written once if there is a change. Did you even try it?

You could loop back through the array from the example I gave you and instead of writing the whole thing, only write back the cells that have success in $data[$i][7]. This wouldn't be hard.

Link to comment
Share on other sites

I did try your example, however nothing is written to the array or to the excel sheet from your code, so I'm not sure where I need to look. Plus, I have to remove the row 0 or else the loop will try and process information from that row. (It will try and create a user with a first name of "firstname". The script must ignore that first row.

Link to comment
Share on other sites

It should work. I'm not sure how in depth you're trying to get here.

If you wanted to do it per-line

#include <excel.au3>
#include <array.au3>

$file = @ScriptDir & "\test.xls"
$oExcel = _ExcelBookOpen($file, 0) ; open excel in the background

$data = _ExcelReadSheetToArray($oExcel, 2,1)
_ArrayDisplay($data, "Array displaying spread sheet")

For $i = 1 To UBound($data) - 1
    If StringInStr($data[$i][6], "transfer") Then
        ConsoleWrite($data[$i][1] & " is a transfer" & @CRLF)
        _ExcelWriteCell($oExcel, "Success!", $i + 1, 7)
    EndIf
Next

_ExcelBookClose($oExcel) ; close excel

And you can keep the header in the array if you want, if you noticed there is an option for _ExcelReadSheetToArray for which row / cols to start on.

#include <excel.au3>
#include <array.au3>

$file = @ScriptDir & "\test.xls"
$oExcel = _ExcelBookOpen($file, 0) ; open excel in the background

$data = _ExcelReadSheetToArray($oExcel, 1,1)
_ArrayDisplay($data, "Array displaying spread sheet")

For $i = 1 To UBound($data) - 1
    If StringInStr($data[$i][6], "transfer") Then
        ConsoleWrite($data[$i][1] & " is a transfer" & @CRLF)
        _ExcelWriteCell($oExcel, "Success!", $i, $data[0][1] + 1)
    EndIf
Next

_ExcelBookClose($oExcel) ; close excel

Notice now we can use the array count without the addition of the removed row. ($i + 1)

If you want to make it dynamic instead of hardcoding the '7' in the _ExcelWriteCell section you could use $data[0][1] column count field to increment. Which is how you should be thinking (Dynamic) if you want to re-use the script. The 2nd example shows that.

Both work fine, script ran with excel document in the scriptdir and, of course, not having the excel file already open :x

More edit : I think your biggest hangup is you're trying to use your own functions where custom functions are not needed.

#include <Excel.au3>

$file = @ScriptDir & "\test.xls"
$oExcel = _ExcelBookOpen($file, 0) ; open excel in the background

$data = _ExcelReadSheetToArray($oExcel, 1,1)

For $i = 1 To UBound($data) - 1
    If StringInStr($data[$i][6], "transfer") Then _ExcelWriteCell($oExcel, "Success!", $i, $data[0][1] + 1)
Next

_ExcelBookClose($oExcel) ; close excel

Cleaned up.

Edited by ZacUSNYR
Link to comment
Share on other sites

Your code works, but I'm uneasy about the fact that if I put a msgbox in the loop it gives me the header and the count which I must ignore. while Excel might be ignoring it, the loop isn't

example:

#include <Excel.au3>
#include <array.au3>

$file = @ScriptDir & "\test.xls"
$oExcel = _ExcelBookOpen($file, 0) ; open excel in the background

$data = _ExcelReadSheetToArray($oExcel, 1,1)
_ArrayDisplay($data, "Array using Default Parameters")

For $i = 0 to UBound($data)-1
    $varfirstname = $data[$i][1]
    $varlastname = $data[$i][2]
    $varlocation = $data[$i][3]
    $vartitle = $data[$i][4]
    $varclass = $data[$i][5]
    $varaction = $data[$i][6]

    msgbox(0, '', "first= " & $varfirstname & @CRLF & "last= " & $varlastname & @CRLF & "location= " & _ 
    $varlocation & @CRLF & "title= " & $vartitle & @CRLF & "class= " & $varclass & @CRLF & "action= " & $varaction)
Next

;For $i = 1 To UBound($data) - 1
;    If StringInStr($data[$i][6], "transfer") Then _ExcelWriteCell($oExcel, "Success!", $i, $data[0][1] + 1)
;Next

_ExcelBookClose($oExcel) ; close excel

EDIT:

nevermind. I figured out if I make my loop have an $i = 2, then it will ignore the first 2 rows. Exactly what I need thanks.

Edited by kor
Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...