Jump to content
ViciousXUSMC

Using Excel UDF to read/write values

Recommended Posts

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)

Ping result.PNG

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 by ViciousXUSMC

Share this post


Link to post
Share on other sites
water

_Excel_RangeInsert is what you are looking for.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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")

 

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

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

×