Microsoft Excel COM UDF library for AutoIt v3

[ScriptUSER]

Does anybody know if there is documentation and examples on this forum

on Microsoft Excel COM UDF library for AutoIt v3

Edited on this forum by Locodarwin ???

thanks ....

[Locodarwin]

Howdy do!

My apologies. I have every intention of providing examples and better documentation for that function collection. I've just started a new contract, and right now I am swamped to the gills with work.

For now, you can check out the function headers for each function in the ExcelCOM_UDF.au3 file. They provide function syntax and will help you understand how they are to be used.

Here's a basic example of how to create a workbook, enter some values into its first sheet, read a value, save the book, and quit:

#include <ExcelCOM_UDF.au3>  ; Include the collection

; Open new book, make it visible
Local $oExcel = _ExcelBookNew(1)

; Write a message to the first cell of the first sheet
_ExcelWriteCell($oExcel, "I'm going to fill some cells up with random data", "A1")

; A loop to fill cells up with random data
For $xx = 1 to 10
    For $yy = 3 to 15
        _ExcelWriteCell($oExcel, Random(22, 55), $yy, $xx)
    Next
Next

; Now we'll read a cell and MsgBox the result
Local $sReadCell = _ExcelReadCell($oExcel, "C5")
MsgBox(0, "Cell C5", $sReadCell)

; Now we save it into the temp directory; overwrite existing file if necessary
_ExcelBookSaveAs($oExcel, @TempDir & "\temp.xls", "xls", 0, 1)

; And finally we close out
_ExcelBookClose($oExcel)

From this example you can infer the basic principles of use. That should get you going.

-S

[ScriptUSER]

  Locodarwin said:

Howdy do!

My apologies. I have every intention of providing examples and better documentation for that function collection. I've just started a new contract, and right now I am swamped to the gills with work.

For now, you can check out the function headers for each function in the ExcelCOM_UDF.au3 file. They provide function syntax and will help you understand how they are to be used.

Here's a basic example of how to create a workbook, enter some values into its first sheet, read a value, save the book, and quit:

#include <ExcelCOM_UDF.au3>  ; Include the collection

; Open new book, make it visible
Local $oExcel = _ExcelBookNew(1)

; Write a message to the first cell of the first sheet
_ExcelWriteCell($oExcel, "I'm going to fill some cells up with random data", "A1")

; A loop to fill cells up with random data
For $xx = 1 to 10
    For $yy = 3 to 15
        _ExcelWriteCell($oExcel, Random(22, 55), $yy, $xx)
    Next
Next

; Now we'll read a cell and MsgBox the result
Local $sReadCell = _ExcelReadCell($oExcel, "C5")
MsgBox(0, "Cell C5", $sReadCell)

; Now we save it into the temp directory; overwrite existing file if necessary
_ExcelBookSaveAs($oExcel, @TempDir & "\temp.xls", "xls", 0, 1)

; And finally we close out
_ExcelBookClose($oExcel)

From this example you can infer the basic principles of use. That should get you going.

-S

Thank you ! Locodarwin !

I got going but the following functions are not working for me .... If you make some example on this functions ,the rest

will be easy i guess .

for ex. on excel hyperlink i wrote on cell adress "A20" ... I GOT TH LINK ON CELL A1.

These are the functions which are making trouble :

Just an example by substituting the variables will be enough and easy for you !

;_ExcelWriteFormula($oExcel, $iRow, $iColumn, $sFormula)
;_ExcelWriteArray($oExcel, $iStartRow, $iStartColumn, $aArray, $iDirection = 0, $iIndexBase = 0)
;_ExcelCopy($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd)

;_ExcelColumnDelete($oExcel, $iColumn, $iNumCols)
;_ExcelNumberFormatR1C1($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, $sFormat)
;_ExcelPictureInsert($oExcel, $sFilePath, $iLeft, $iTop, $iWidth, $iHeight, $fLinkToFile = False, $fSaveWithDoc = False)
;_ExcelPictureAdjust($oPicture, $iHorizontal, $iVertical, $iRotation = 0)
;_ExcelPictureScale($oPicture, $nScaleWidth = 1, $nScaleHeight = 1, $iScaleOrigWidth = True, $iScaleOrigHeight = True, $iScaleFrom = 0)
;_ExcelHyperlinkInsert($oExcel, $sLinkText, $sAddress, $sRangeOrRow, $iColumn = 1)
;_ExcelSortExtended($oExcel, $sKey, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1,
;_ExcelSort($oExcel, $sKey, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1

thanks !