Sign in to follow this  
Followers 0
Bowmore

Fast Array Write To Excel

15 posts in this topic

#1 ·  Posted (edited)

I have some scripts that use Excel to present the result of data validation tests and I was becoming a little frustrated at the length of time it was taking to write the data to excel, using _ExcelWriteSheetFromArray() function in the Excel.au3 UDF. So I thought I'd take a look and see if I could improve on it's performance. This was the result. This new function is 200 to 300 times faster than _ExcelWriteSheetFromArray() for large arrays (20000 plus elements) and in the region of 100 times faster for small arrays.

I have also included the option to write only part of the array to the spreadsheet as sometimes I do not need all of the array.

_ExcelSheetWriteFromArray

; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelSheetWriteFromArray
; Description ...: Writes a 2D array, or part of, to a specified location in the active worksheet
; Syntax.........: _ExcelSheetWriteFromArray($oExcel, ByRef $aArray, $iExcelStartRow = Default, $iExcelStartCol = Default, $iArrayStartRow = Default, $iArrayStartCol = Default, $iArrayEndRow = Default, $iArrayEndCol = Default)
; Parameters ....: $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                $aArray - The array ByRef to write data from (array is not modified)
;                $iExcelStartRow - The spreadsheet row to start writing the array to, default is 1
;                $iExcelStartCol - The spreadsheet column to start writing the array to, default is 1
;                $iArrayStartRow - Start row index of array to start writing data from, default is 0
;                $iArrayStartCol - Start column index of array to start writing data from, default is 0
;                $iArrayEndRow - End row index of array to stop writing data from, default is Ubound($aArray)
;                $iArrayEndCol - End column index of array to stop writing data from, default is Ubound($aArray,2)
; Return values .: Success   - Returns 1
;                Failure - Returns 0 and sets @error on errors:
;                |@error=1   - Excel object does not exist
;                |@error=2   - Not a 2D array
;                |@error=3   - End row greater than start row
;                |@error=4   - End col greater than start col
; Author ........: Bowmore
; Modified.......:
; Remarks .......: Only 2D arrays are currently handeled. Maxim cell content length is 255 characters
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _ExcelSheetWriteFromArray($oExcel, ByRef $aArray, $iExcelStartRow = Default, $iExcelStartCol = Default, $iArrayStartRow = Default, $iArrayStartCol = Default, $iArrayEndRow = Default, $iArrayEndCol = Default)
Local $iExcelEndRow = 1
Local $iExcelEndCol = 1

If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
If UBound($aArray, 0) <> 2 Then Return SetError(2, 0, 0)
If $iExcelStartRow = Default Then $iExcelStartRow = 1
If $iExcelStartCol = Default Then $iExcelStartCol = 1
If $iArrayStartRow = Default Then $iArrayStartRow = 0
If $iArrayStartCol = Default Then $iArrayStartCol = 0
If $iArrayEndRow = Default Then $iArrayEndRow = UBound($aArray) - 1
If $iArrayEndCol = Default Then $iArrayEndCol = UBound($aArray, 2) - 1
If $iExcelStartRow < 1 Then $iExcelStartRow = 1
If $iExcelStartCol < 1 Then $iExcelStartCol = 1
If $iArrayStartRow < 0 Then $iArrayStartRow = 0
If $iArrayStartCol < 0 Then $iArrayStartCol = 0
If $iArrayEndRow < $iArrayStartRow Then Return SetError(3, 1, 0)
If $iArrayEndCol < $iArrayStartCol Then Return SetError(4, 1, 0)
$iExcelEndRow = $iExcelStartRow + $iArrayEndRow - $iArrayStartRow
$iExcelEndCol = $iExcelStartCol + $iArrayEndCol - $iArrayStartCol

; Check if only part of the array is to written to the speadsheet
If $iArrayStartRow <> 0 Or $iArrayStartCol <> 0 Or $iArrayEndRow <> UBound($aArray) - 1 Or $iArrayEndCol = UBound($aArray, 2) - 1 Then
;Copy specified array range to a temporary array
Local $aTemp[$iArrayEndRow - $iArrayStartRow + 1][$iArrayEndCol - $iArrayStartCol + 1]
Local $iRow = 0
Local $iCol = 0
For $i = $iArrayStartRow To $iArrayEndRow
$iCol = 0
For $j = $iArrayStartCol To $iArrayEndCol
$aTemp[$iRow][$iCol] = $aArray[$i][$j]
$iCol += 1
Next
$iRow += 1
Next
With $oExcel.ActiveSheet
.Range(.Cells($iExcelStartRow, $iExcelStartCol), .Cells($iExcelEndRow, $iExcelEndCol)).Select
.Range(.Cells($iExcelStartRow, $iExcelStartCol), .Cells($iExcelEndRow, $iExcelEndCol)).value = $oExcel.Application.WorksheetFunction.Transpose($aTemp)
EndWith
Else
With $oExcel.ActiveSheet
.Range(.Cells($iExcelStartRow, $iExcelStartCol), .Cells($iExcelEndRow, $iExcelEndCol)).Select
.Range(.Cells($iExcelStartRow, $iExcelStartCol), .Cells($iExcelEndRow, $iExcelEndCol)).value = $oExcel.Application.WorksheetFunction.Transpose($aArray)
EndWith
EndIf

EndFunc ;==>_ExcelSheetWriteFromArray

Simple Speed Test _ExcelSheetWriteFromArray

#include <excel.au3>

Test_Main()

Func Test_Main()
Local $sXlsFile1 = @ScriptDir & "Test1.xls"
Local $sXlsFile2 = @ScriptDir & "Test2.xls"
Local $oExcel = 0
Local $Tim = 0
Local $aData[5000][10]

For $i = 0 To 4999
$aData[$i][0] = "Y"
$aData[$i][1] = "DATA_TEST_ROW_0" & StringFormat("%03i", $i)
$aData[$i][2] = "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Vestibulum ultrices est varius elit consequat sollicitudin mollis libero ultrices. Quisque lobortis lorem quis nunc vestibulum tincidunt. Duis eget urna sapien, nec egestas purus. Fusce massa nunc."
$aData[$i][3] = $i * 10
$aData[$i][4] = $i * 100
$aData[$i][5] = $aData[$i][3] / $aData[$i][4]
$aData[$i][6] = "Pass"
$aData[$i][7] = @YEAR & "-" & @MON & "-" & @MDAY & "T" & @HOUR & ":" & @MIN
$aData[$i][8] = "FAIL"
$aData[$i][9] = "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Vestibulum ultrices est varius elit consequat sollicitudin mollis libero ultrices. Quisque lobortis lorem quis nunc vestibulum tincidunt. Duis eget urna sapien, nec egestas purus. Fusce massa nunc."
Next

; Speed Test of new function
$oExcel = _ExcelBookNew(0)
$Tim = TimerInit()
_ExcelSheetWriteFromArray($oExcel, $aData)
ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : TimerDiff($Tim) = ' & TimerDiff($Tim) & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console
_ExcelBookSaveAs($oExcel, $sXlsFile1)
_ExcelBookClose($oExcel, 0, 0)

; Speed Test of old function
$oExcel = _ExcelBookNew(0)
$Tim = TimerInit()
_ExcelWriteSheetFromArray($oExcel, $aData)
ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : TimerDiff($Tim) = ' & TimerDiff($Tim) & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console
_ExcelBookSaveAs($oExcel, $sXlsFile2)
_ExcelBookClose($oExcel, 0, 0)

EndFunc ;==>Test_Main


; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelSheetWriteFromArray
; Description ...: Writes a 2D array, or part of, to a specified location in the active worksheet
; Syntax.........: _ExcelSheetWriteFromArray($oExcel, ByRef $aArray, $iExcelStartRow = Default, $iExcelStartCol = Default, $iArrayStartRow = Default, $iArrayStartCol = Default, $iArrayEndRow = Default, $iArrayEndCol = Default)
; Parameters ....: $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                $aArray - The array ByRef to write data from (array is not modified)
;                $iExcelStartRow - The spreadsheet row to start writing the array to, default is 1
;                $iExcelStartCol - The spreadsheet column to start writing the array to, default is 1
;                $iArrayStartRow - Start row index of array to start writing data from, default is 0
;                $iArrayStartCol - Start column index of array to start writing data from, default is 0
;                $iArrayEndRow - End row index of array to stop writing data from, default is Ubound($aArray)
;                $iArrayEndCol - End column index of array to stop writing data from, default is Ubound($aArray,2)
; Return values .: Success   - Returns 1
;                Failure - Returns 0 and sets @error on errors:
;                |@error=1   - Excel object does not exist
;                |@error=2   - Not a 2D array
;                |@error=3   - End row greater than start row
;                |@error=4   - End col greater than start col
; Author ........: Bowmore
; Modified.......:
; Remarks .......: Only 2D arrays are currently handeled. Maxim cell content length is 255 characters
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _ExcelSheetWriteFromArray($oExcel, ByRef $aArray, $iExcelStartRow = Default, $iExcelStartCol = Default, $iArrayStartRow = Default, $iArrayStartCol = Default, $iArrayEndRow = Default, $iArrayEndCol = Default)
Local $iExcelEndRow = 1
Local $iExcelEndCol = 1

If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
If UBound($aArray, 0) <> 2 Then Return SetError(2, 0, 0)
If $iExcelStartRow = Default Then $iExcelStartRow = 1
If $iExcelStartCol = Default Then $iExcelStartCol = 1
If $iArrayStartRow = Default Then $iArrayStartRow = 0
If $iArrayStartCol = Default Then $iArrayStartCol = 0
If $iArrayEndRow = Default Then $iArrayEndRow = UBound($aArray) - 1
If $iArrayEndCol = Default Then $iArrayEndCol = UBound($aArray, 2) - 1
If $iExcelStartRow < 1 Then $iExcelStartRow = 1
If $iExcelStartCol < 1 Then $iExcelStartCol = 1
If $iArrayStartRow < 0 Then $iArrayStartRow = 0
If $iArrayStartCol < 0 Then $iArrayStartCol = 0
If $iArrayEndRow < $iArrayStartRow Then Return SetError(3, 1, 0)
If $iArrayEndCol < $iArrayStartCol Then Return SetError(4, 1, 0)
$iExcelEndRow = $iExcelStartRow + $iArrayEndRow - $iArrayStartRow
$iExcelEndCol = $iExcelStartCol + $iArrayEndCol - $iArrayStartCol

; Check if only part of the array is to written to the speadsheet
If $iArrayStartRow <> 0 Or $iArrayStartCol <> 0 Or $iArrayEndRow <> UBound($aArray) - 1 Or $iArrayEndCol = UBound($aArray, 2) - 1 Then
;Copy specified array range to a temporary array
Local $aTemp[$iArrayEndRow - $iArrayStartRow + 1][$iArrayEndCol - $iArrayStartCol + 1]
Local $iRow = 0
Local $iCol = 0
For $i = $iArrayStartRow To $iArrayEndRow
$iCol = 0
For $j = $iArrayStartCol To $iArrayEndCol
$aTemp[$iRow][$iCol] = $aArray[$i][$j]
$iCol += 1
Next
$iRow += 1
Next
With $oExcel.ActiveSheet
.Range(.Cells($iExcelStartRow, $iExcelStartCol), .Cells($iExcelEndRow, $iExcelEndCol)).Select
.Range(.Cells($iExcelStartRow, $iExcelStartCol), .Cells($iExcelEndRow, $iExcelEndCol)).value = $oExcel.Application.WorksheetFunction.Transpose($aTemp)
EndWith
Else
With $oExcel.ActiveSheet
.Range(.Cells($iExcelStartRow, $iExcelStartCol), .Cells($iExcelEndRow, $iExcelEndCol)).Select
.Range(.Cells($iExcelStartRow, $iExcelStartCol), .Cells($iExcelEndRow, $iExcelEndCol)).value = $oExcel.Application.WorksheetFunction.Transpose($aArray)
EndWith
EndIf

EndFunc ;==>_ExcelSheetWriteFromArray
Edited by Bowmore
1 person likes this

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to build bigger and better idiots. So far, the universe is winning."- Rick Cook

Share this post


Link to post
Share on other sites



There is a ticket in BugTracker for a new version of _ExcelReadSheetFromArray() that also eliminates the nested loops that individually access Excel cells in favor of using the Range.Cells method. It shows a similar speed increase.

I would think if you reverted back to the production function name, switched both the $iRowBase ($iArrayStartRow) and $iColBase ($iArrayStartCol) parameters back to defaulting to 1, instead of 0, and maintained the existing error return values (with some of your own additions), that you'd have a backward-compatible function (with 2 new parms) that could replace the one in the production UDF?

Share this post


Link to post
Share on other sites

Thanks for the suggestions. Although I wrote it to meet my own specific needs I would make sense to change the version I posted hear to make it compatible with the existing _ExcelWriteSheetFromArray(). I'll change it in the next couple of days when I have half an hour to make the changes and test it properly.


"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to build bigger and better idiots. So far, the universe is winning."- Rick Cook

Share this post


Link to post
Share on other sites

Hi,

Your Fast Array Write To Excel Script rocks, but only for 65536 rows, if I put one more 65537 I get an error:

: ==> The requested action with this object has failed.:

.Range(.Cells($iExcelStartRow, $iExcelStartCol), .Cells($iExcelEndRow, $iExcelEndCol)).value = $oExcel.Application.WorksheetFunction.Transpose($aTemp)

.Range(.Cells($iExcelStartRow, $iExcelStartCol), .Cells($iExcelEndRow, $iExcelEndCol)).value = $oExcel.Application.WorksheetFunction.Transpose($aTemp)^ ERROR

can that be fixed?

Share this post


Link to post
Share on other sites

I'm about to rewrite the Excel UDF that comes with AutoIt. It's an early Alpha version but your problem has been solved by GMX's (it is named _Excel_RangeRead).

If you can test, please post the results in the Excel Rewrite thread.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Actually, I need to make changes to the _Excel_RangeWrite function as well. Will post it in the Excel Rewrite thread as soon as I've completed it.

Share this post


Link to post
Share on other sites

I agree with GMK, I have tested _ExcelSheetWriteFromArray with Excel Rewrite.au3 and 65536 issue is still present.

Waiting for changes to the _Excel_RangeWrite function...

Share this post


Link to post
Share on other sites

There appears to be a limit of 65536 cells when using the .transpose method at least up to Excel 2007, might even go beyond that, but I'm not sure.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

I find this post on mrexcel.com forum: http://www.mrexcel.com/forum/excel-questions/555073-limitation-range-size-transpose-function.html#post2742571

(The Transpose function just doesn't work with >65536, just as SpecialCells or AdvancedFilter etc.methods sometimes don't work with ranges above a certain size. You'd have to ask the Microsoft coders why they haven't updated these to allow for the larger sheet size of Excel 2007 and subsequent versions.)

I really don't know is this comment form mrexcel.com forum true or false, but I find one not so pretty but working solution for example in our topic

#include <excel.au3>

Test_Main()

Func Test_Main()
Local $oExcel = 0
Local $Tim = 0
Local $aData[65536][1]
Local $aData1[65536][1]

For $i = 0 To 65535
$aData[$i][0] = "DATA_TEST_ROW_0" & StringFormat("%03i", $i+1)
Next

For $j = 0 To 65535
$aData1[$j][0] = "DATA_TEST_ROW_0" & StringFormat("%03i", $j+65537)
Next

; Speed Test of new function
$oExcel = _ExcelBookNew()
$Tim = TimerInit()
_ExcelSheetWriteFromArray($oExcel, $aData)
_ExcelSheetWriteFromArray($oExcel, $aData1, 65537, 1)
ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : TimerDiff($Tim) = ' & TimerDiff($Tim) & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console

EndFunc ;==>Test_Main


; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelSheetWriteFromArray
; Description ...: Writes a 2D array, or part of, to a specified location in the active worksheet
; Syntax.........: _ExcelSheetWriteFromArray($oExcel, ByRef $aArray, $iExcelStartRow = Default, $iExcelStartCol = Default, $iArrayStartRow = Default, $iArrayStartCol = Default, $iArrayEndRow = Default, $iArrayEndCol = Default)
; Parameters ....: $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;            $aArray - The array ByRef to write data from (array is not modified)
;            $iExcelStartRow - The spreadsheet row to start writing the array to, default is 1
;            $iExcelStartCol - The spreadsheet column to start writing the array to, default is 1
;            $iArrayStartRow - Start row index of array to start writing data from, default is 0
;            $iArrayStartCol - Start column index of array to start writing data from, default is 0
;            $iArrayEndRow - End row index of array to stop writing data from, default is Ubound($aArray)
;            $iArrayEndCol - End column index of array to stop writing data from, default is Ubound($aArray,2)
; Return values .: Success - Returns 1
;            Failure - Returns 0 and sets @error on errors:
;            |@error=1 - Excel object does not exist
;            |@error=2 - Not a 2D array
;            |@error=3 - End row greater than start row
;            |@error=4 - End col greater than start col
; Author ........: Bowmore
; Modified.......:
; Remarks .......: Only 2D arrays are currently handeled. Maxim cell content length is 255 characters
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _ExcelSheetWriteFromArray($oExcel, ByRef $aArray, $iExcelStartRow = Default, $iExcelStartCol = Default, $iArrayStartRow = Default, $iArrayStartCol = Default, $iArrayEndRow = Default, $iArrayEndCol = Default)
Local $iExcelEndRow = 1
Local $iExcelEndCol = 1

If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
If UBound($aArray, 0) <> 2 Then Return SetError(2, 0, 0)
If $iExcelStartRow = Default Then $iExcelStartRow = 1
If $iExcelStartCol = Default Then $iExcelStartCol = 1
If $iArrayStartRow = Default Then $iArrayStartRow = 0
If $iArrayStartCol = Default Then $iArrayStartCol = 0
If $iArrayEndRow = Default Then $iArrayEndRow = UBound($aArray) - 1
If $iArrayEndCol = Default Then $iArrayEndCol = UBound($aArray, 2) - 1
If $iExcelStartRow < 1 Then $iExcelStartRow = 1
If $iExcelStartCol < 1 Then $iExcelStartCol = 1
If $iArrayStartRow < 0 Then $iArrayStartRow = 0
If $iArrayStartCol < 0 Then $iArrayStartCol = 0
If $iArrayEndRow < $iArrayStartRow Then Return SetError(3, 1, 0)
If $iArrayEndCol < $iArrayStartCol Then Return SetError(4, 1, 0)
$iExcelEndRow = $iExcelStartRow + $iArrayEndRow - $iArrayStartRow
$iExcelEndCol = $iExcelStartCol + $iArrayEndCol - $iArrayStartCol

; Check if only part of the array is to written to the speadsheet
If $iArrayStartRow <> 0 Or $iArrayStartCol <> 0 Or $iArrayEndRow <> UBound($aArray) - 1 Or $iArrayEndCol = UBound($aArray, 2) - 1 Then
;Copy specified array range to a temporary array
Local $aTemp[$iArrayEndRow - $iArrayStartRow + 1][$iArrayEndCol - $iArrayStartCol + 1]
Local $iRow = 0
Local $iCol = 0
For $i = $iArrayStartRow To $iArrayEndRow
$iCol = 0
For $j = $iArrayStartCol To $iArrayEndCol
$aTemp[$iRow][$iCol] = $aArray[$i][$j]
$iCol += 1
Next
$iRow += 1
Next
With $oExcel.ActiveSheet
.Range(.Cells($iExcelStartRow, $iExcelStartCol), .Cells($iExcelEndRow, $iExcelEndCol)).Select
.Range(.Cells($iExcelStartRow, $iExcelStartCol), .Cells($iExcelEndRow, $iExcelEndCol)).value = $oExcel.Application.WorksheetFunction.Transpose($aTemp)
EndWith
Else
With $oExcel.ActiveSheet
.Range(.Cells($iExcelStartRow, $iExcelStartCol), .Cells($iExcelEndRow, $iExcelEndCol)).Select
.Range(.Cells($iExcelStartRow, $iExcelStartCol), .Cells($iExcelEndRow, $iExcelEndCol)).value = $oExcel.Application.WorksheetFunction.Transpose($aArray)
EndWith
EndIf

EndFunc ;==>_ExcelSheetWriteFromArray

Share this post


Link to post
Share on other sites

A better way would be to do something like this:

#include <excel.au3>

Test_Main()


; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelSheetWriteFromArray
; Description ...: Writes a 2D array, or part of, to a specified location in the active worksheet
; Syntax.........: _ExcelSheetWriteFromArray($oExcel, ByRef $aArray, $iExcelStartRow = Default, $iExcelStartCol = Default, $iArrayStartRow = Default, $iArrayStartCol = Default, $iArrayEndRow = Default, $iArrayEndCol = Default)
; Parameters ....: $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;            $aArray - The array ByRef to write data from (array is not modified)
;            $iExcelStartRow - The spreadsheet row to start writing the array to, default is 1
;            $iExcelStartCol - The spreadsheet column to start writing the array to, default is 1
;            $iArrayStartRow - Start row index of array to start writing data from, default is 0
;            $iArrayStartCol - Start column index of array to start writing data from, default is 0
;            $iArrayEndRow - End row index of array to stop writing data from, default is Ubound($aArray)
;            $iArrayEndCol - End column index of array to stop writing data from, default is Ubound($aArray,2)
; Return values .: Success - Returns 1
;            Failure - Returns 0 and sets @error on errors:
;            |@error=1 - Excel object does not exist
;            |@error=2 - Not a 2D array
;            |@error=3 - End row greater than start row
;            |@error=4 - End col greater than start col
; Author ........: Bowmore
; Modified.......:
; Remarks .......: Only 2D arrays are currently handeled. Maxim cell content length is 255 characters
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _ExcelSheetWriteFromArray($oExcel, ByRef $aArray, $iExcelStartRow = Default, $iExcelStartCol = Default, $iArrayStartRow = Default, $iArrayStartCol = Default, $iArrayEndRow = Default, $iArrayEndCol = Default)
    Local $iExcelEndRow = 1
    Local $iExcelEndCol = 1

    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If UBound($aArray, 0) <> 2 Then Return SetError(2, 0, 0)
    If $iExcelStartRow = Default Then $iExcelStartRow = 1
    If $iExcelStartCol = Default Then $iExcelStartCol = 1
    If $iArrayStartRow = Default Then $iArrayStartRow = 0
    If $iArrayStartCol = Default Then $iArrayStartCol = 0
    If $iArrayEndRow = Default Then $iArrayEndRow = UBound($aArray) - 1
    If $iArrayEndCol = Default Then $iArrayEndCol = UBound($aArray, 2) - 1
    If $iExcelStartRow < 1 Then $iExcelStartRow = 1
    If $iExcelStartCol < 1 Then $iExcelStartCol = 1
    If $iArrayStartRow < 0 Then $iArrayStartRow = 0
    If $iArrayStartCol < 0 Then $iArrayStartCol = 0
    If $iArrayEndRow < $iArrayStartRow Then Return SetError(3, 1, 0)
    If $iArrayEndCol < $iArrayStartCol Then Return SetError(4, 1, 0)
    If $iArrayEndRow > UBound($aArray) - 1 Then $iArrayEndRow = UBound($aArray) - 1 ; <<<<<<<<<<<<<<< added this line, because there's no check to see if the $iArrayEndRow is larger than the end of the array
    $iExcelEndRow = $iExcelStartRow + $iArrayEndRow - $iArrayStartRow
    $iExcelEndCol = $iExcelStartCol + $iArrayEndCol - $iArrayStartCol

    ; Check if only part of the array is to written to the speadsheet
    If $iArrayStartRow <> 0 Or $iArrayStartCol <> 0 Or $iArrayEndRow <> UBound($aArray) - 1 Or $iArrayEndCol = UBound($aArray, 2) - 1 Then
        ;Copy specified array range to a temporary array
        Local $aTemp[$iArrayEndRow - $iArrayStartRow + 1][$iArrayEndCol - $iArrayStartCol + 1]
        Local $iRow = 0
        Local $iCol = 0
        For $i = $iArrayStartRow To $iArrayEndRow
            $iCol = 0
            For $j = $iArrayStartCol To $iArrayEndCol
                $aTemp[$iRow][$iCol] = $aArray[$i][$j]
                $iCol += 1
            Next
            $iRow += 1
        Next
        With $oExcel.ActiveSheet
            .Range(.Cells($iExcelStartRow, $iExcelStartCol), .Cells($iExcelEndRow, $iExcelEndCol)).Select
            .Range(.Cells($iExcelStartRow, $iExcelStartCol), .Cells($iExcelEndRow, $iExcelEndCol)).value = $oExcel.Application.WorksheetFunction.Transpose($aTemp)
        EndWith
    Else
        With $oExcel.ActiveSheet
            .Range(.Cells($iExcelStartRow, $iExcelStartCol), .Cells($iExcelEndRow, $iExcelEndCol)).Select
            .Range(.Cells($iExcelStartRow, $iExcelStartCol), .Cells($iExcelEndRow, $iExcelEndCol)).value = $oExcel.Application.WorksheetFunction.Transpose($aArray)
        EndWith
    EndIf

EndFunc   ;==>_ExcelSheetWriteFromArray

Func Test_Main()
    Local $oExcel = 0
    Local $Tim = 0
    Local $aData[131072][1]

    For $i = 0 To 131071
        $aData[$i][0] = "DATA_TEST_ROW_0" & StringFormat("%03i", $i + 1)
    Next
    ; Speed Test of new function
    $oExcel = _ExcelBookNew()
    $Tim = TimerInit()
    ; This only writes 65535 elements of the array at a time, if your array is larger than that, then it will split it up
    ; for you.
    For $loop = 0 To UBound($aData) - 1 Step 65536 
        _ExcelSheetWriteFromArray($oExcel, $aData, $loop + 1, 0, $loop, 0, $Loop + 65535)
    Next
    ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : TimerDiff($Tim) = ' & TimerDiff($Tim) & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console

EndFunc   ;==>Test_Main

This rewrites the test script that tempman posted above and also corrects a bug in the _ExcelSheetWriteFromArray function if you send it an end row of the array that is beyond the UBound dimension of the array.

This will write 65535 rows to the spreadsheet until you've hit the end of the array, and the array can be of any size either larger or smaller than 65535 rows.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites
updated.

Share this post


Link to post
Share on other sites

#12 ·  Posted (edited)

Thanks GMK!

I hope to release a new alpha version with your changes quite soon.

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

I posted a revised version of _Excel_RangeWrite for you to test.

Would like to hear from Excel 2003 testers as I only can test with Excel 2010.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Hi

Is there a script for Fast Array Write from Excel? I see that it takes a lot of time for this as well - when using big Excel files.

Thank you!

Share this post


Link to post
Share on other sites

Welcome to AutoIt and the forum,

the rewrite of the Excel UDF I'm currently working on has a _Excel_RangeRead function too.

Please check the for the latest download. But be aware that it is still an early Alpha version!

If you search the forum you will find some other fast _ExcelReadSheetToArray functions too.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

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
Sign in to follow this  
Followers 0

  • Similar Content

    • willichan
      By willichan
      Here is another one from my archives that filled a specific need.
       
      Here is the back story if you are interested.
       
      Keep in mind that I wrote this script over 3 years ago, so it may not compile or run directly without some minor tweaks.  It also requires the use of GraphViz to build the graph.
      #cs ---------------------------------------------------------------------------- Project Name: ExcelLinksMapper Description: Analyse an Excel file's links and map them out. Creation Date: 9/26/2014 AutoIt Version: Author: willichan Requires: Graphviz (http://graphviz.org/) #ce ---------------------------------------------------------------------------- Opt("MustDeclareVars", 1) ;0=no, 1=require pre-declare Opt("TrayAutoPause", 0) ;0=no pause, 1=Pause Opt("TrayMenuMode", 0) ;0=append, 1=no default menu, 2=no automatic check, 4=menuitemID not return Opt("TrayIconHide", 0) ;0=show, 1=hide tray icon Global Const $MyName=StringLeft(@ScriptName, StringInStr(@ScriptName,".", 0, -1)-1) ;get just the name portion of the script/exe name Global Const $MyMutex=$MyName & "-82243BEBC30533A3" ;name the mutex for this app Global $SQLloaded = False Global $sDbName = @ScriptDir & "\db2gv.db" ConsoleWrite($sDbName & @crlf) If _MutexExists($MyMutex) Then Exit #include <SQLite.au3> #include <SQLite.dll.au3> #include <file.au3> #include <array.au3> #include <excel.au3> _ConfigInitialize() _Main() Func _ConfigInitialize() OnAutoItExitRegister("_ConfigDestroy") ;initializers here Global $sSQliteDll = _SQLite_Startup() If @error Then MsgBox(0, "SQLite Error", "could not load the DLL") Global $sSQLiteDB = _SQLite_Open($sDbName) If $sSQLiteDB = 0 Then MsgBox(0, "SQLite Error", "could not open the database") $SQLloaded =True __CreateTables() EndFunc ;==>_ConfigInitialize Func _ConfigDestroy() ;destructors here If $SQLloaded Then _SQLite_Close() _SQLite_Shutdown() EndIf EndFunc ;==>_ConfigDestroy Func _MutexExists($sOccurenceName) Local $ERROR_ALREADY_EXISTS = 183, $handle, $lastError $sOccurenceName = StringReplace($sOccurenceName, "\", "") $handle = DllCall("kernel32.dll", "int", "CreateMutex", "int", 0, "long", 1, "str", $sOccurenceName) $lastError = DllCall("kernel32.dll", "int", "GetLastError") Return $lastError[0] = $ERROR_ALREADY_EXISTS EndFunc ;==>_MutexExists Func __CreateTables() _SQLite_Exec($sSQLiteDB, "DROP TABLE IF EXISTS nodes;") _SQLite_Exec($sSQLiteDB, "CREATE TABLE IF NOT EXISTS nodes( name TEXT PRIMARY KEY, fileexists INTEGER);") _SQLite_Exec($sSQLiteDB, "DROP TABLE IF EXISTS links;") _SQLite_Exec($sSQLiteDB, "CREATE TABLE IF NOT EXISTS links( id INTEGER PRIMARY KEY, name1 TEXT, name2 TEXT, weight INTEGER);") EndFunc Func _Main() Local $sInfile, $vResult, $iErrLoop $sInfile = FileOpenDialog("Source File", @WorkingDir, "Excel files (*.xl*)", 1 + 2) If Not FileExists($sInfile) Then MsgBox(0, "Excel Links Mapper Error", "Unable to locate source file") Exit EndIf $vResult = $SQLITE_IOERR $iErrLoop = 5 While $vResult = $SQLITE_IOERR $vResult = _SQLite_Exec($sSQLiteDB, "INSERT OR IGNORE INTO nodes ('name', 'fileexists') VALUES (" & _SQLite_FastEscape($sInfile) & ", 1);") If Not $vResult = $SQLITE_OK Then Sleep(100) $iErrLoop -= 1 If $iErrLoop = 0 Then ConsoleWrite($iErrLoop & " tries" & @CRLF & $sInfile & @CRLF) $vResult = $SQLITE_OK EndIf WEnd _GetExcelLinks($sInfile) Global $hOutfile = FileOpen(@ScriptDir & "\" & $MyName & ".gv", 2) If $hOutfile = -1 Then MsgBox(0, $MyName & " ERROR", "Unable to upen file for output") Exit EndIf _WriteHeader() _WriteNodes() _WriteLinks() _WriteFooter() FileClose($hOutfile) _GenerateGraph() ShellExecute(@ScriptDir & '\ExcelLinksMapper.png') EndFunc ;==>_Main Func _GetExcelLinks($strFileName) Local $hQuery, $aCount, $iErrLoop, $vResult ConsoleWrite($strFileName & @CRLF) Local $iLoop, $iExists Local $aLinks Local Const $xlExcelLinks = 1 Local $oExcel = _Excel_Open() Local $ret = _Excel_BookOpen_NoUpdate($oExcel, $strFileName, True, True) Local $err = @error If $err Then If Not IsObj($oExcel) Then ConsoleWrite($ret & " - " & $err & @CRLF) Exit EndIf EndIf $aLinks = $oExcel.ActiveWorkbook.LinkSources($xlExcelLinks) _Excel_BookClose($oExcel, False) _Excel_Close($oExcel, False, True) If IsArray($aLinks) Then If UBound($aLinks) > 0 Then For $iLoop = 0 To UBound($aLinks) - 1 If $aLinks[$iLoop] <> $strFileName Then $iExists = FileExists($aLinks[$iLoop]) ConsoleWrite("DEBUG - Calling WriteNode()") __WriteNode($aLinks[$iLoop], $iExists) ConsoleWrite("DEBUG - Calling WriteLink()") __WriteLink($strFileName, $aLinks[$iLoop]) If $iExists And ($aLinks[$iLoop] <> $strFileName) Then _GetExcelLinks($aLinks[$iLoop]) EndIf Next EndIf EndIf EndFunc ;==>_GetExcelLinks Func __WriteNode($sName, $iExists) Local $iErrLoop = 5 ;Number of attempts to make Local $vResult Do ConsoleWrite("DEBUG - WriteNode()" & @CRLF & " _SQLite_Exec(INSERT OR IGNORE INTO nodes ('name', 'fileexists') VALUES (" & _SQLite_FastEscape($sName) & ", " & $iExists & ");) - create node entry" & @CRLF) $vResult = _SQLite_Exec($sSQLiteDB, "INSERT OR IGNORE INTO nodes ('name', 'fileexists') VALUES (" & _SQLite_FastEscape($sName) & ", " & $iExists & ");") If Not $vResult = $SQLITE_OK Then Sleep(100) $iErrLoop -= 1 If $iErrLoop = 0 Then $vResult = $SQLITE_OK ;Used up all our attempts, so simulate a success Until $vResult = $SQLITE_OK EndFunc ;==>__WriteNode Func __WriteLink($sName1, $sName2) Local $iErrLoop = 5 ;Number of attempts to make Local $vResult, $hQuery, $vCount ConsoleWrite("DEBUG - WriteNode()" & @CRLF & " _SQLite_Query(SELECT weight FROM links WHERE 'name1'=" & _SQLite_FastEscape($sName1) & " AND 'name2'=" & _SQLite_FastEscape($sName2) & ";) - lookup link entry" & @CRLF) _SQLite_Query($sSQLiteDB, "SELECT weight FROM links WHERE 'name1'=" & _SQLite_FastEscape($sName1) & " AND 'name2'=" & _SQLite_FastEscape($sName2) & ";", $hQuery) ConsoleWrite("DEBUG - _SQLite_FetchData()" & @CRLF) _SQLite_FetchData($hQuery, $vCount) If UBound($vCount) > 1 Then _ArrayDisplay($vCount) If $SQLITE_OK And UBound($vCount) > 1 Then $vCount = $vCount[1] + 1 Else $vCount = 1 EndIf Do If $vCount = 1 Then ConsoleWrite("DEBUG - _SQLite_Exec() - create link entry" & @CRLF) $vResult = _SQLite_Exec($sSQLiteDB, "INSERT INTO links ('name1', 'name2', 'weight') VALUES (" & _SQLite_FastEscape($sName1) & ", " & _SQLite_FastEscape($sName2) & ", " & $vCount & ");") Else ConsoleWrite("DEBUG - _SQLite_Exec() - update link entry" & @CRLF) $vResult = _SQLite_Exec($sSQLiteDB, "UPDATE links SET 'weight'=" & $vCount & " WHERE 'name1'=" & _SQLite_FastEscape($sName1) & " AND 'name2=" & _SQLite_FastEscape($sName2) & ";") EndIf If Not $vResult = $SQLITE_OK Then Sleep(100) $iErrLoop -= 1 If $iErrLoop = 0 Then $vResult = $SQLITE_OK ;Used up all our attempts, so simulate a success Until $vResult = $SQLITE_OK EndFunc ;==>__WriteLink ; #FUNCTION# ==================================================================================================================== ; Author ........: SEO <locodarwin at yahoo dot com> ; Modified.......: litlmike, water, GMK, willichan ; =============================================================================================================================== Func _Excel_BookOpen_NoUpdate($oExcel, $sFilePath, $bReadOnly = Default, $bVisible = Default, $sPassword = Default, $sWritePassword = Default) If Not IsObj($oExcel) Or ObjName($oExcel, 1) <> "_Application" Then Return SetError(1, @error, 0) If Not FileExists($sFilePath) Then Return SetError(2, 0, 0) If $bReadOnly = Default Then $bReadOnly = False If $bVisible = Default Then $bVisible = True ;; changing the second parameter on the following line to a 0 tells Excel not to update any links. Local $oWorkbook = $oExcel.Workbooks.Open($sFilePath, 0, $bReadOnly, Default, $sPassword, $sWritePassword) If @error Then Return SetError(3, @error, 0) $oExcel.Windows($oWorkbook.Name).Visible = $bVisible ; If a read-write workbook was opened read-only then return an error If $bReadOnly = False And $oWorkbook.Readonly = True Then Return SetError(4, 0, $oWorkbook) Return $oWorkbook EndFunc ;==>_Excel_BookOpen_NoUpdate Func _GenerateGraph() RunWait(@ScriptDir & '\GraphViz238\bin\dot.exe -Tpng "' & @ScriptDir & '\' & $MyName & '.gv" -o "' & @ScriptDir & '\ExcelLinksMapper.png"') EndFunc ;==>_GenerateGraph Func _WriteHeader() __OutLine(0, "digraph main {") EndFunc ;==>_WriteHeader Func _WriteNodes() Local $aResult, $iRows, $iColumns, $iRval Local $iLoop, $sStyle __OutLine(1, "// Nodes") $iRval = _SQLite_GetTable($sSQLiteDB, "SELECT name, fileexists FROM nodes;", $aResult, $iRows, $iColumns) If @error Then ConsoleWrite("_WriteNodes()" & @CRLF & "_SQLite_GetTable") If $iRval = $SQLITE_OK Then For $iLoop = 1 To $iRows If $aResult[($iLoop * 2) + 2] > 0 Then $sStyle = "normal" Else $sStyle = "missing" EndIf __OutNode($aResult[1 + (2 * $iLoop)], $sStyle) ;; Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg() & @CRLF & "in _WriteNotes() calling _SQLite_GetTable()") Exit EndIf EndFunc ;==>_WriteNodes Func __OutNode($sName, $sStyle = Default) If $sStyle = Default Then $sStyle = "Normal" Switch StringLower($sStyle) Case "missing" __OutLine(1, __MakeName($sName) & ' [label="' & StringReplace($sName, "\", "\\") & '",color=red,fontcolor=red,shape=octagon];') Case Else ;"normal", Default __OutLine(1, __MakeName($sName) & ' [label="' & StringReplace($sName, "\", "\\") & '",color=black,fontcolor=black,shape=box];') EndSwitch EndFunc ;==>__OutNode Func _WriteLinks() Local $aResult, $iRows, $iColumns, $iRval Local $iLoop, $sStyle, $aCount __OutLine(1, "// Links") $iRval = _SQLite_GetTable($sSQLiteDB, "SELECT name1, name2 FROM links ORDER BY name1 ASC, name2 ASC;", $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then For $iLoop = 1 To $iRows __OutLink($aResult[1 + (2 * $iLoop)], $aResult[2 + (2 * $iLoop)]) Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) Exit EndIf EndFunc ;==>_WriteLinks Func __OutLink($sName1, $sName2, $iWeight = 1) __OutLine(1, __MakeName($sName1) & ' -> ' & __MakeName($sName2) & ';') EndFunc ;==>__OutLink Func _WriteFooter() __OutLine(0, "}") EndFunc ;==>_WriteFooter Func __OutLine($iTabs, $sText) Local $iLoop If $iTabs > 0 Then For $iLoop = 1 To $iTabs FileWrite($hOutfile, " ") ;ConsoleWrite(" ") Next EndIf FileWriteLine($hOutfile, $sText) EndFunc ;==>__OutLine Func __MakeName($sText) Local $sNewName = StringReplace($sText, "\", " ") $sNewName = StringReplace($sNewName, "/", " ") $sNewName = StringReplace($sNewName, "'", " ") $sNewName = StringReplace($sNewName, '"', " ") $sNewName = StringReplace($sNewName, ':', " ") $sNewName = StringReplace($sNewName, '.', " ") $sNewName = StringReplace($sNewName, '-', " ") $sNewName = StringReplace($sNewName, '$', " ") $sNewName = StringStripWS($sNewName, 8) Return StringLower($sNewName) EndFunc ;==>__MakeName  
    • Dimmae
      By Dimmae
      Hello,
      at first: i'm new here, so please forgive me my mistakes, and show them to me, just that i can learn to do better in the future.
      Now to my Problem: i have an excel sheet, where i just need some columns for further actions, but i have no idea how to add single columns to a new array.
      I found the following code(the one i just added as a file) from 'water' in this forum, but i wont get how i could add multiple columns into a new array.
      The biggest problem in my situation is that i dont know the count of the rows i need for the array, i just got a fix number of rows, which is 4.
       
      Hope you can help me, and sry again for this 'unlucky illustration'.
       
      btw: how can i add code shown as code here, instead of posting it as a attached file?.
       
       
       
      autoit-select-column.au3
      defects.xlsx
    • LoneWolf_2106
      By LoneWolf_2106
      Hi,
      i have an error:
      ==> The requested action with this object has failed.: $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count $iRowCount = .Range(^ ERROR  
      My code is:
      $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) Sleep(1000) $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count Sleep(1000) _Excel_RangeWrite($oWorkbook_1, $oWorkbook_1.ActiveSheet,$aFileList[$i][2] , "AB3:AB"&$iRowCount) I have added some sleep because the application was crashing more often before, so i thought to slow down the code execution.
      But i didn't solve the issue.
      Has anyone an idea of what the problem might be?
      Thanks in advance.
    • water
      By water
      Extensive library to control and manipulate Microsoft Excel charts.
      Written by GreenCan and water.
      Theads: General Help & Support - Example Scripts
      BTW: If you like this UDF please click the "I like this" button. This tells me where to next put my development effort

      KNOWN BUGS (last changed: 2017-07-21)
      None. The COM error handling related bugs have been fixed.