Using Excel UDF to read/write values

[ViciousXUSMC]

I just tossed this together, I have only used the Excel UDF one time previously and Water pretty much walked me through it back when I was very new and had no idea what to do.

This seems to be working, but I would like somebody to double check and see if there are obvious errors or any techniqes I should have used to make the code cleaner (especially how to read from one cell and apply a result from that cell and write it to another)

#Include <Excel.au3>
#Include <Array.au3>

$vXSession = _Excel_Open()
$vWorkbook = _Excel_BookOpen($vXSession, @ScriptDir & "\Unit Latency.xlsx")

$test = _Excel_RangeRead($vWorkbook, Default)

;_ArrayDisplay($test)

For $i = 1 to UBound($test) -1
_Excel_RangeWrite($vWorkbook, Default, AvgPing($test[$i][0]), "B" & $i+1)
Next

_Excel_Close($vXSession)


Func AvgPing($vHost)
$vP1 = Ping($vHost)
$vP2 = Ping($vHost)
$vP3 = Ping($vHost)
$vAvg = ($vP1 + $vP2 + $vP3)/3
Return $vAvg
EndFunc

An example of my excel sheet is simply 2 columns one with a hostname/ip and the other for the average ping result (attached)

[water]

Looks good to me.
If speed is an issue you could store the results in a one-dimensional table and at the end write the whole table in one go to cell B2.

[ViciousXUSMC]

Speed is good, I'll have to look up the flags to maybe have this all happen in the background and just get a MsgBox or something to let me know it is done rather than have the spreadsheet open in my face.

To expand this and make it more useful, the next thing to do would be find how to shift every column other than column A to the right one spot this way each time I run the script I have my old results for comparison.  That would probably be done with excel magic more so than autoit code.

 

Edit: I bet _Excel_RangeInsert can do it easy enough  going to test now. 

Edited May 11, 2015 by ViciousXUSMC

[water]

_Excel_RangeInsert is what you are looking for.

[ViciousXUSMC]

Here is where I ended up.

Only issue is if I run this on a workbook with ony Column A it creates 1 dimensional array and causes a error,  so I have to put something in column B to get a 2 dimensional array.

I thought of checking for something and if nothing exsist then place something, then do the read and then delete it after but that seems dirty so for now I'll just leave it like this and know to add something for that first run.

#Include <Excel.au3>
#Include <Array.au3>

$vXSession = _Excel_Open("False")
$vWorkbook = _Excel_BookOpen($vXSession, @ScriptDir & "\Unit Latency.xlsx")

$test = _Excel_RangeRead($vWorkbook, Default)
_Excel_RangeInsert($vWorkbook.ActiveSheet, "B:B")
_Excel_RangeWrite($vWorkbook, $vWorkBook.ActiveSheet, "Latency " & @Hour & ":" & @Min, "B1")

;_ArrayDisplay($test)

For $i = 1 to UBound($test) -1
_Excel_RangeWrite($vWorkbook, Default, AvgPing($test[$i][0]), "B" & $i+1)
Next

_Excel_Close($vXSession)


Func AvgPing($vHost)
$vP1 = Ping($vHost)
$vP2 = Ping($vHost)
$vP3 = Ping($vHost)
$vAvg = ($vP1 + $vP2 + $vP3)/3
Return $vAvg
EndFunc

MsgBox(0, "", "Unit Latency Test is Complete and Saved to your Excel Workbook")

 

[water]

You could check $vWorkbook.ActiveSheet.UsedRange.Columns.Count. If <  2 use _Excel_RangeWrite to set B1 to space.

The following _Excel_RangeRead then will always return a 2D array.