Jump to content

Yet Another -- ExcelCOM UDF

Locodarwin

By Locodarwin
in AutoIt Example Scripts

 Share

Recommended Posts

  • Replies 379
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Tweaky Wayfarer

Tweaky

Posted
Posted (edited)

Hi,

the function _ExcelReadSheetToArray doesn`t work

$exceldatei = _ExcelBookOpen($exceldatei_tore_usw)
$avRET = _ExcelReadSheetToArray($exceldatei, 3, 1, 40, 50)

I get error code 2 and @extended 1

If I disable

If $iStartRow > $iLastRow Then Return SetError(2, 0, 0)
    If $iStartColumn > $iLastColumn Then Return SetError(2, 1, 0)
    If $iStartRow + $iRowCnt - 1 > $iLastRow Then Return SetError(3, 0, 0)
    If $iStartColumn + $iColCnt - 1 > $iLastColumn Then Return SetError(3, 1, 0)

in the excelcom_udf.au3 then it works fine.

Could you please correct it.

Thanks

Edited by Tweaky
Zest Seeker

Zest

Posted
Posted

I just found out why the _ExcelReadSheetToArray doesn't work ;) :

It searches for "R" and "C" to determine the size of the sheet, but at work I use the German version of Excel, which uses "Z" and "S" for row (Zeile) and column (Spalte).

At home I have a Dutch version of Excel, which also uses uses different letters.

This is exactly the reason why I think all these kind of names/letters/functions in Office should ALWAYS be in English, regardless of which Language version the program itself is in .... :)

Anyway, This could be solved e.g. by analyzing the String to determine the size of the sheet (e.g. R32C454) character by character for letters and numbers. This will provide the position of the 2 letters (in my example 1 and 4).

Then it's easy to split the string into the two numbers between both letters (position 2-3) and after the last letter (position 5-end). That way it would work independent of which letter is used in the localised version of Excel.

The check if the last sheet is blank also uses the "R1C1" ands should be changed.

Zest Seeker

Zest

Posted
Posted

Hello, I've changed te _ExcelReadSheetToArray function to make it work for all localized versions of Excel ... I hope ... ;)

Maybe the author of the ExcelCOM UDF can fix the function with this suggested code.

This code works for me, but it's likely there are people with more knowledge who can code it in a more efficient way. :)

Func _ExcelReadSheetToArray($oExcel, $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 0, $iColCnt = 0)
    Local $avRET[1][2] = [[0, 0]]; 2D return array
   
   ; Test inputs
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $iStartRow < 1 Then Return SetError(2, 0, 0)
    If $iStartColumn < 1 Then Return SetError(2, 1, 0)
    If $iRowCnt < 0 Then Return SetError(3, 0, 0)
    If $iColCnt < 0 Then Return SetError(3, 1, 0)

; ------------
; CHANGE START
; ------------
    
   ; Get size of current sheet as R1C1 string
   ;     Note: $xlCellTypeLastCell and $x1R1C1 are constants declared in ExcelCOM_UDF.au3
    Local $sLastCell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)
   
   ; Extract integer last row and col
; 1) Determine length of string containing last cell in R1C1 format
    local $len = StringLen($sLastCell)
; 2) Replace the non-numeric characters with "-", to make it language independent
    $NumString=StringRegExpReplace($sLastCell,"\D","-")
; Loop to split the string containing last cell in R1C1 format into number for last row and number for last column
; start at 2nd character, because the 1st is always a letter
    For $iLastCellCount = 2 to $len
        If StringMid($NumString,$iLastCellCount,1) = "-" Then
            Local $iLastRow = Number(StringMid($sLastCell, 2, $iLastCellCount -2))
            Local $iLastColumn = Number(StringMid($sLastCell, $iLastCellCount+1, $len-$iLastCellCount))
            ExitLoop
        EndIf
    Next

; Return 0's if the sheet is blank
    If $iLastRow = 1 And $iLastColumn = 1 And $oExcel.Activesheet.Cells($iLastRow, $iLastColumn).Value = "" Then Return $avRET

; ------------
; CHANGE END
; ------------

   ; Check input range is in bounds
    If $iStartRow > $iLastRow Then Return SetError(2, 0, 0)
    If $iStartColumn > $iLastColumn Then Return SetError(2, 1, 0)
    If $iStartRow + $iRowCnt - 1 > $iLastRow Then Return SetError(3, 0, 0)
    If $iStartColumn + $iColCnt - 1 > $iLastColumn Then Return SetError(3, 1, 0)
   
   ; Check for defaulted counts
    If $iRowCnt = 0 Then $iRowCnt = $iLastRow - $iStartRow + 1
    If $iColCnt = 0 Then $iColCnt = $iLastColumn - $iStartColumn + 1
   
   ; Size the return array
    ReDim $avRET[$iRowCnt + 1][$iColCnt + 1]
    $avRET[0][0] = $iRowCnt
    $avRET[0][1] = $iColCnt
   
   ; Read data to array
    For $r = 1 To $iRowCnt
        For $c = 1 To $iColCnt
            $avRET[$r][$c] = $oExcel.Activesheet.Cells($iStartRow + $r - 1, $iStartColumn + $c - 1).Value
        Next
    Next
   
   ;Return data
    Return $avRET
EndFunc  ;==>_ExcelReadSheetToArray
PsaltyDS Universalist

PsaltyDS

Posted
Posted (edited)

Hello, I've changed te _ExcelReadSheetToArray function to make it work for all localized versions of Excel ... I hope ... :D

Maybe the author of the ExcelCOM UDF can fix the function with this suggested code.

This code works for me, but it's likely there are people with more knowledge who can code it in a more efficient way. :)

Nice catch finding the problem of R/C being different in localized versions! ;)

When I can finally shake loose some time to work on it, I will check out your patch ,too. The only free time I've had has not been around the machines with MS Excel on them, but I'll jump on it as soon as possible.

My preferred solution would be to just query the COM interface for the numeric Row/Col values and avoid the text parsing altogether. But I'll have to look it up to see if that is available. If not, a good RegExp can just pull the two numerics out of the string, ignoring all alpha characters.

:(

Update: The RegExp solution turned out to be pretty trivial. I don't have Excel here to test it, as stated above, but this looks like it should work. Please test it with both english and non-english localized versions if you can:

;===============================================================================
;
; Description:      Create a 2D array from the rows/columns of the active worksheet.
; Syntax:           _ExcelReadSheetToArray($oExcel, [$iStartRow , $iStartColumn [, $iRowCnt, $iColCnt]])
; Parameter(s):     $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;               $iStartRow - Row number to start reading, defaults to 1 (first row)
;               $iStartColumn - Column number to start reading, defaults to 1 (first column)
;               $iRowCnt - Count of rows to read, defaults to 0 (all)
;               $iColCnt - Count of columns to read, defaults to 0 (all)
; Requirement(s):   Requires ExcelCOM_UDF.au3
; Return Value(s):  On Success - Returns a 2D array with the specified cell contents by [$row][$col]
;                   On Failure - Returns 0 and sets @error on errors:
;                  @error=1 - Specified object does not exist
;                  @error=2 - Start parameter out of range
;                     @extended=0 - Row out of range
;                     @extended=1 - Column out of range
;                  @error=3 - Count parameter out of range
;                     @extended=0 - Row count out of range
;                     @extended=1 - Column count out of range
; Author(s):        SEO <locodarwin at yahoo dot com> (original _ExcelReadArray() function)
; Modified:         PsaltyDS 01/04/08 - 2D version _ExcelReadSheetToArray()
;                   PsaltyDS 03/12/08 - Removed use of literal "R" and "C" becaused localized version use 
;                       different letters for "row" and "column"
; Note(s):          Returned array has row count in [0][0] and column count in [0][1].
;                   Except for the counts above, row 0 and col 0 of the returned array are empty, as actual
;                        cell data starts at [1][1] to match R1C1 numbers.
;                   By default the entire sheet is returned.
;                   If the sheet is empty [0][0] and [0][1] both = 0.
;
;===============================================================================
Func _ExcelReadSheetToArray($oExcel, $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 0, $iColCnt = 0)
    Local $avRET[1][2] = [[0, 0]] ; 2D return array

    ; Test inputs
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $iStartRow < 1 Then Return SetError(2, 0, 0)
    If $iStartColumn < 1 Then Return SetError(2, 1, 0)
    If $iRowCnt < 0 Then Return SetError(3, 0, 0)
    If $iColCnt < 0 Then Return SetError(3, 1, 0)

    ; Get size of current sheet as R1C1 string
    ;     Note: $xlCellTypeLastCell and $x1R1C1 are constants declared in ExcelCOM_UDF.au3
    Local $sLastCell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)

    ; Extract integer last row and col
    Local $avLastCell = StringRegExp($sLastCell, "\d+", 3)
    Local $iLastRow = Number($avLastCell[0])
    Local $iLastColumn = Number($avLastCell[1])

    ; Return 0's if the sheet is blank
    If $iLastRow = 1 And $iLastColumn = 1 And $oExcel.Activesheet.Cells($iLastRow, $iLastColumn).Value = "" Then Return $avRET

    ; Check input range is in bounds
    If $iStartRow > $iLastRow Then Return SetError(2, 0, 0)
    If $iStartColumn > $iLastColumn Then Return SetError(2, 1, 0)
    If $iStartRow + $iRowCnt - 1 > $iLastRow Then Return SetError(3, 0, 0)
    If $iStartColumn + $iColCnt - 1 > $iLastColumn Then Return SetError(3, 1, 0)

    ; Check for defaulted counts
    If $iRowCnt = 0 Then $iRowCnt = $iLastRow - $iStartRow + 1
    If $iColCnt = 0 Then $iColCnt = $iLastColumn - $iStartColumn + 1

    ; Size the return array
    ReDim $avRET[$iRowCnt + 1][$iColCnt + 1]
    $avRET[0][0] = $iRowCnt
    $avRET[0][1] = $iColCnt

    ; Read data to array
    For $r = 1 To $iRowCnt
        For $c = 1 To $iColCnt
            $avRET[$r][$c] = $oExcel.Activesheet.Cells($iStartRow + $r - 1, $iStartColumn + $c - 1).Value
        Next
    Next

    ;Return data
    Return $avRET
EndFunc   ;==>_ExcelReadSheetToArray

Thanks.

Edit: Added Number() to insure integer type returned for $iLastRow/$iLastColumn

Edited by PsaltyDS
Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Zest Seeker

Zest

Posted
Posted

Your version workswith the German version of Excel. I'll test it with the Dutch version as well, but it should work with that one too.

Your code is also based on regeular expression replace very simililar to mine, except more sophisticated :)

PsaltyDS Universalist

PsaltyDS

Posted
Posted

Your version workswith the German version of Excel. I'll test it with the Dutch version as well, but it should work with that one too.

Your code is also based on regeular expression replace very simililar to mine, except more sophisticated :)

Glad it worked. :D

We may need to look into applying this tweak to any other functions that pull R1C1 formatted cell values. I haven't scanned the rest of the UDF to see if that is done anywhere else yet.

;)

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Locodarwin Universalist

Locodarwin

Posted
  • Author
Posted

Sure, I'll address it. I can't help you in any way, shape, or form. Office 2007 does not support saving documents in that format.

http://www.cpafirmtechnology.com/office_20...file_compat.htm

Note the last section.

Of course, I'm not terribly surprised, since dBase IV came out in 1988.

-S

(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
Locodarwin Universalist

Locodarwin

Posted
  • Author
Posted

PsaltyDS,

Your code works great for me, as well, though I'm only able to test English versions. The regular expression looks sound, however. I'll add it to the next release of the UDF if you have no objection.

-S

(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
Zest Seeker

Zest

Posted
Posted (edited)

Hello again,

I found some time to test PsaltyDS' version with the Dutch Excel 2000, and as expected it also works with this version (as with the German Excel 2003 version).

So you have my vote for implementing this change in the next version of ExcelCOM UDF (imagine smiley with thumbs up here).

Edit: typo to stupid to even mention...

Edited by Zest
PsaltyDS Universalist

PsaltyDS

Posted
Posted

PsaltyDS,

Your code works great for me, as well, though I'm only able to test English versions. The regular expression looks sound, however. I'll add it to the next release of the UDF if you have no objection.

-S

Glad it fixed the problem, and of course I have no objection at all.

Thanks to Zest for the testing with other language settings.

Cheers!

:)

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
PsaltyDS Universalist

PsaltyDS

Posted
Posted

Ooh... found a new toy... :)

Downloaded the latest _XMLDOMWrapper.au3 by eltorro, and noticed a commented section for adding to the au3.user.calltips.api file (in C:\Program Files\AutoIt3\SciTE\api\ by default). I thought, "It can't be that easy..." but it is.

Paste this into your au3.user.calltips.api (and restart SciTE if it's already running) and you get call tip help with the ExcelCOM_UDF functions in SciTE:

;
; ========= From ExcelCOM_UDF.au3 =================
;
_ExcelBookNew ( $fVisible = 1 ) Creates new workbook and returns its object identifier (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelAttach ( $s_string, $s_mode = "FilePath" ) Attach to an existing instance of Microsoft Excel (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelBookOpen ( $sFilePath, $fVisible = 1, $fReadOnly = False, $sPassword = "", $sWritePassword = "" ) Opens an existing workbook and returns its object identifier (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelBookOpenTxt ( $sFilePath, $sDelimiter = ",", $iStartRow = 1, $iDataType = 1, $iTextQualifier = 1, $fConsecDelim = False, $fVisible = 1 ) Opens an existing text file, parses it into Excel, and returns its object identifier (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelBookSave ( $oExcel, $fAlerts = 0 ) Saves the active workbook of the Excel object (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelBookSaveAs ( $oExcel, $sFilePath, $sType = "xls", $fAlerts = 0, $fOverWrite = 0, $sPassword = "", $sWritePassword = "", $iAccessMode = 1, $iConflictResolution = 2 ) Saves the active workbook of the Excel object with a new filename and/or type (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelBookClose ( $oExcel, $fSave = 1, $fAlerts = 0 ) Closes the active workbook and removes the Excel object (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelShow ( $oExcel ) Makes the Excel document object visible (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelHide ( $oExcel ) Makes the Excel document object invisible (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelWriteCell ( $oExcel, $sValue, $sRangeOrRow, $iColumn = 1 ) Write information to a cell on the active worksheet of the Excel object (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelWriteFormula ( $oExcel, $sFormula, $sRangeOrRow, $iColumn = 1 ) Write a formula to a cell on the active worksheet of the Excel object (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelWriteArray ( $oExcel, $iStartRow, $iStartColumn, $aArray, $iDirection = 0, $iIndexBase = 0 ) Write an array to a row or column on the active worksheet of the Excel object (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelReadCell ( $oExcel, $sRangeOrRow, $iColumn = 1 ) Read information from the active worksheet of the Excel object (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelReadArray ( $oExcel, $iStartRow, $iStartColumn, $iNumCells, $iDirection = 0, $iIndexBase = 0 ) Create an array from a row or column of the active worksheet (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelCopy ( $oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1 ) Send a specified range to the clipboard (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelPaste ( $oExcel, $sRangeOrRow, $iColumn = 1 ) Send the clipboard data to a specified range (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelInsert ( $oExcel, $sRangeOrRow, $iColumn = 1, $iShiftDirection = -4121 ) Insert the clipboard data at a specified cell (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelRowDelete ( $oExcel, $iRow, $iNumRows = 1 ) Delete a number of rows from the active worksheet (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelColumnDelete ( $oExcel, $iColumn, $iNumCols = 1 ) Delete a number of columns from the active worksheet (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelRowInsert ( $oExcel, $iRow, $iNumRows = 1 ) Insert a number of rows into the active worksheet (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelColumnInsert ( $oExcel, $iColumn, $iNumCols = 1 ) Insert a number of columns into the active worksheet (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelNumberFormat ( $oExcel, $sFormat, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1 ) Applies formatting to the cells in an R1C1 Range (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelPictureInsert ( $oExcel, $sFilePath, $iLeft, $iTop, $iWidth, $iHeight, $fLinkToFile = False, $fSaveWithDoc = False ) Insert a picture from a separate file into the active sheet (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelPictureAdjust ( $oPicture, $iHorizontal, $iVertical, $iRotation = 0 ) Change the position or rotation of a picture object created with _ExcelPictureInsert() (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelPictureScale ( $oPicture, $nScaleWidth = 1, $nScaleHeight = 1, $fScaleOrigWidth = True, $fScaleOrigHeight = True, $iScaleFrom = 0 ) Scale a picture object created with _ExcelPictureInsert() (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelCreateBorders ( $oExcel, $sBorderStyle, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iEdgeLeft = 1, $iEdgeTop = 1, $iEdgeBottom = 1, $iEdgeRight = 1, $iEdgeInsideV = 0, $iEdgeInsideH = 0 ) Create Borders around a range of cells (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelHyperlinkInsert ( $oExcel, $sLinkText, $sAddress, $sScreenTip, $sRangeOrRow, $iColumn = 1 ) Inserts a hyperlink into the active page (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelSort ( $oExcel, $sKey, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iDirection = 2 ) Performs a simplified sort on a range (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelSortExtended ( $oExcel, $sKey, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iDirection = 2, $iHeader = 0, $fMatchCase = False, $iOrientation = 2, $iDataOption = 0 ) Performs an advanced sort on a range (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelFindInRange ( $oExcel, $sFindWhat, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iDataType = 0, $iWholeOrPart = 2, $fMatchCase = False, $fMatchFormat = "" ) Finds all instances of a string in a range and returns their addresses as a two dimensional array (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelReplaceInRange ( $oExcel, $sFindWhat, $sReplaceWith, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iWholeOrPart = 2, $fMatchCase = False, $fMatchFormat = "", $fReplaceFormat = False ) Finds all instances of a string in a range and replace them with the replace string (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelCommentAdd ( $oExcel, $sComment, $sRangeOrRow, $iColumn = 1, $fVisible = 0 ) Add a comment (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelCommentDelete ( $oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1 ) Delete a range of comments (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelCommentShow ( $oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $fVisible = False ) Show/hide a range of comments (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelCommentRead ( $oExcel, $sRangeOrRow, $iColumn = 1 ) Read a comment from a cell (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelSplitWindow ( $oExcel, $iSplitRow, $iSplitColumn ) Split the active window into 2 or 4 sections (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelFontSetProperties ( $oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $fBold = False, $fItalic = False, $fUnderline = False ) Set the bold, italic, and underline font properties of a range (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelFontSet ( $oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sFontName = "Arial" ) et the font face property of a range (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelFontGet ( $oExcel, $sRangeOrRow, $iColumn = 1 ) Get the font face property of a range (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelFontSetSize ( $oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iFontSize = 10 ) Set the font size property of a range (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelFontGetSize ( $oExcel, $sRangeOrRow, $iColumn = 1 ) Get the font size property of a range (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelFontSetColor ( $oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iColorIndex = 1, $hColor = 0x000000 ) _ExcelFontSetColor (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelFontGetColor ( $oExcel, $sRangeOrRow, $iColumn = 1 ) Get the font color value of a range (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelCellColorSet ( $oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iColorIndex = 1, $hColor = 0x000000 ) Set the cell interior color value of a range (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelCellColorGet ( $oExcel, $sRangeOrRow, $iColumn = 1 ) Get the cell interior color value of a range (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelHorizontalAlignSet ( $oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sHorizAlign = "left" ) Set the horizontal alignment of each cell in a range (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelVerticalAlignSet ( $oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sVertAlign = "bottom" ) Set the vertical alignment of each cell in a range (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelColWidthSet ( $oExcel, $vColumn, $vWidth ) Set the width of a column (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelColWidthGet ( $oExcel, $vColumn ) Get the width of a column (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelRowHeightSet ( $oExcel, $iRow, $vHeight ) Set the height of a row (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelRowHeightGet ( $oExcel, $iRow ) Get height of a row (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelSheetMove ( $oExcel, $vMoveSheet, $vRelativeSheet = 1, $fBefore = True ) Move a sheet before another sheet (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelSheetAddNew ( $oExcel, $sName = "" ) Add new sheet to workbook - optionally with a name (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelSheetNameGet ( $oExcel ) Return the name of the active sheet (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelSheetNameSet ( $oExcel, $sSheetName ) Set the name of the active sheet (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelSheetActivate ( $oExcel, $vSheet ) Activate a sheet by string name or by number (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelSheetDelete ( $oExcel, $vSheet, $fAlerts = False ) Delete a sheet by string name or by number (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelSheetList ( $oExcel ) Return a list of all sheets in workbook, by name, as an array (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelSheetUsedRangeGet ( $oExcel, $vSheet ) Return the last cell of the sheet's used range in an array (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelCellFormat ( $oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $fWrapText = False, $iOrientation = 0, $fAddIndent = False, $iIndentLevel = 0, $fShrinkToFit = False ) Set/Reset some common cell/range formatting parameters (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelCellMerge ( $oExcel, $fDoMerge, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1 ) Merge/UnMerge cells in a range (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelPrintRange ( $oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iCopies = 1, $sActivePrinter = "", $fPrintToFile = False, $fCollate = False, $sPrToFileName = "" ) Print a range of cells to a printer or a file (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelPrintSheet ( $oExcel, $vSheet, $iCopies = 1, $sActivePrinter = "", $fPrintToFile = False, $fCollate = False, $sPrToFileName = "" ) Print a worksheet (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelBookPropertiesGet ( $oExcel ) Return an array of workbook properties (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelBookPropertiesSet ( $oExcel, $sAuthor = "", $sTitle = "", $sSubject = "" ) Set workbook properties (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelReadSheetToArray ( $oExcel, $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 0, $iColCnt = 0 ) Create a 2D array from the rows/columns of the active worksheet (Requires: #include <ExcelCOM_UDF.au3>)
_ExcelWriteSheetFromArray ( $oExcel, ByRef $aArray, $iStartRow = 1, $iStartColumn = 1, $iRowBase = 1, $iColBase = 1 ) Writes a 2D array to the active worksheet (Requires: #include <ExcelCOM_UDF.au3>)

Just goes to show how much I still don't know!

:)

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law
Tweaky Wayfarer

Tweaky

Posted
Posted

Hi,

I have open a excel file.

Then I have tried to attach a seconde excel file to the first instance.

But this doesn`t work.

I used _ExcelAttach()

It returns always 0.

Is it possible, that this doesn`t work on german office?

Locodarwin Universalist

Locodarwin

Posted
  • Author
Posted

You've tried to attach one Excel file to another? I'm not sure what that means or what you're ultimately trying to do.

Please post the code snippet you're working on so that we can review it and provide further help.

Thanks!

-S

(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
Tweaky Wayfarer

Tweaky

Posted
Posted (edited)

Hi this is the code

#include<ExcelCOM_UDF.au3>
$exceldatei_pfad = "E:\test.xls"
$exceldatei_pfad_2 = "E:\test_2.xls"


$exceldatei = _ExcelBookOpen($exceldatei_pfad)

_ExcelAttach("test.xls", "FileName")

An then I don`t know how I should open the second file

Edited by Tweaky
Locodarwin Universalist

Locodarwin

Posted
  • Author
Posted

Hi this is the code

#include<ExcelCOM_UDF.au3>
$exceldatei_pfad = "E:\test.xls"
$exceldatei_pfad_2 = "E:\test_2.xls"


$exceldatei = _ExcelBookOpen($exceldatei_pfad)

_ExcelAttach("test.xls", "FileName")

An then I don`t know how I should open the second file

First of all, you do not need to use _ExcelAttach() in this code because by using _ExcelBookOpen() you're already "attaching" to the instance by assigning the newly created Excel application object to $exceldatei.

_ExcelAttach() is only for use when the Excel document you want to work with has already been opened some other way, i.e. if you had your Excel workbook opened before you ran your AutoIt script, but you still wanted to reference that workbook in your script. Otherwise, if you'll be opening all of your documents with your script, you don't need to use _ExcelAttach() at all, ever.

So, here is how I would change your code to open the two Excel documents:

#include<ExcelCOM_UDF.au3>
$exceldatei_pfad = "E:\test.xls"
$exceldatei_pfad_2 = "E:\test_2.xls"

$exceldatei = _ExcelBookOpen($exceldatei_pfad)     ; $exceldatei becomes the object identifier of test.xls
$exceldatei2 = _ExcelBookOpen($exceldatei_pfad_2)    ; $exceldatei2 becomes the object identifier of test_2.xlsoÝ÷ ØÚ0§ʰj{m¢«~æ¢Ø^¬LzP1@Å~éܶ*'²ÞØ^®íëv.éí³*.q©e¶ºw-ÚÛ¬zØ^¡¸ÞrØz{b~'«~íæè¢L¨»§¶Ø^~éܶ*'¶©¦­¡*%j׫{azÇ+r¢ç(ºW]¢Ê&zØb  bëayú%"®¶­seôW6VÅw&FT6VÆÂb33c¶W6VÆFFVÂgV÷C´VÆÆòÂGvV·gV÷C²ÂgV÷C´RgV÷C²²Æ6W2FRv÷&G2gV÷C´VÆÆòGvV·gV÷C²çFò6VÆÂRöbFW7BçÇ0¥ôW6VÅw&FT6VÆÂb33c¶W6VÆFFV"ÂgV÷C´VÆÆòÂvâÂGvV·gV÷C²ÂgV÷C´gV÷C²²Æ6W2FRv÷&G2gV÷C´VÆÆòÂvâÂGvV·gV÷C²çFò6VÆÂ#öbFW7Eó"çÇ

...and so on.

Good luck with your Excel endeavors!

-S

(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]
Tweaky Wayfarer

Tweaky

Posted
Posted

$exceldatei = _ExcelBookOpen($exceldatei_pfad) ; $exceldatei becomes the object identifier of test.xls

$exceldatei2 = _ExcelBookOpen($exceldatei_pfad_2) ; $exceldatei2 becomes the object identifier of test_2.xls

Hi,

I have tested this example but it doesn`t work like I want.

The two files will be opend by this script, but the files will be opened in two excel windows.

So excel ist opened two times.

And this is the problem.

I opened the first file.

Then the second file will be opened in a new excel instance.

In the second file are formulas to the first file.

An now excel think, that the first file is not opened and the cells with the formula will not abe updated

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
Go to topic listing

  • Recently Browsing   0 members

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