Yet Another -- ExcelCOM UDF

DaLiMan · October 24, 2006

@DaLiMan: All 4 of the functions will be added to the UDF, albeit in different form....

That'll be great! Looking forward to the next release.....

At any change, did you have time to check into the problem of copy & past with 2 different files?

I've been testing and trying to get de handle wich is created for the excel object, but I think it's in a trange format for the only thing I get is a square in the MsgBox() :whistle:

JackB · October 24, 2006

With regard to the proposed UDF functions to return number of sheet columns or rows used (_ExcelSheetNameUsedColGet() , etc.) be careful. My reading of the proposed code makes use of Excel's "UsedRange" property. According to Steven Roman's Writing Excel Macros with VBA (O'Reilly pg 251) this property is not to be trusted. From the book

Unfortunately, the UsedRange property has had a rather rocky history... Thus, I strongly suggest that you use this method with caution, for it sometimes seems to include cells that once had contents but have since been completely cleared.

I have had to write my own functions based on examples from Steven's book to get around this "feature"

Nice work on this project though.

Edited October 24, 2006 by JackB

Locodarwin · October 24, 2006

At any change, did you have time to check into the problem of copy & past with 2 different files?
I've been testing and trying to get de handle wich is created for the excel object, but I think it's in a trange format for the only thing I get is a square in the MsgBox()

You're going to be very happy today.

I found the solution: don't use the variable $oExcel in your code when working with 2 different Excel objects. For some (probably obvious) reason (read: oversight on my part), it's screwing up the object handles.

Try this adaptation of your previous code:

#include <ExcelCOM_UDF.au3> ; or wherever you've stashed my UDF

; Create new workbook, visible, and return an object handle to it
$oFoo = _ExcelBookNew()
$oBar = _ExcelBookNew()

_ExcelWriteCellA1($oFoo, "B23", "Daniel")

_ExcelCopyA1($oFoo, "B23")
_ExcelPasteA1($oBar, "A1")

Viola!

Incidentally, the object handle that is returned isn't a string, so when you MsgBox() it, you won't get anything recognizable in return. It will look like garbage, and that is to be expected.

@ptrex: That's a good workaround. At this point, I'm not sure I want to go as far as Dale and big_daddy have gone with COM error handling in their UDFs. Some of their routines seem like overkill to me.

@JackB: Thanks for the heads-up. That explains some of the strange behavior I've witnessed.

-S

Locodarwin · October 24, 2006

Updated original post. v1.2 attached.

Major update! This will most likely break existing scripts! Please check the changelog in first post for a list of changes, updates, additions, and so on. There are many.

Bottom line, all A1 and R1C1 range functions have been combined. This alters function parameters and their order drastically. Additionally, any other function that accepted $sRange will now accept either A1 or R1C1 format parameters.

Also note, functions with either "A1" or "R1C1" in their names will be removed next release. References to them, as well as their headers, have already been removed.

Enjoy!

-S

DaLiMan · October 25, 2006

You're going to be very happy today.
I found the solution: don't use the variable $oExcel in your code when working with 2 different Excel objects. For some (probably obvious) reason (read: oversight on my part), it's screwing up the object handles.

My day is just starting, but already I now it will be glorious after reading this......

Thanx for taking the time at solving this issue. :whistle:

DaLiMan · October 25, 2006

For myself I've altered the _ExcelSheetUsedColGet() and _ExcelSheetUsedRowGet() functions to _ExcelSheetUsedRangeGet().

The reason is that they were very unrelyable. For example if you use Columns B,C,D the function would return 3. Now the function is right, but if you were to use that in a script to add data you might end up overwriting some of it.

The new function will give you the last used cell in format $A$1

@Locodarwin: Might be a better alternatif for the 2 functions, also it saves 1 in the UDF.....

Let me know what you think.

;===============================================================================
;
; Description:      Return the last cell in the used range in the specified worksheet.
; Syntax:           _ExcelSheetUsedRangeGet($oExcel, $vSheet)
; Parameter(s):     $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   $vSheet - The sheet name or number to be checked.
; Requirement(s):   None
; Return Value(s):  On Success - Returns the ID of the last used cell. (ex.$B$5)
;                   On Failure - Returns 0 and sets @error on errors:
;                   @error=1 - Specified object does not exist
; Author(s):        DaLiMan
;
;===============================================================================

Func _ExcelSheetUsedRangeGet($oExcel, $vSheet)
    Dim $iColCount
    If NOT IsObj($oExcel) Then
        $iColCount = SetError(1, 0, 0)
    Else
        $oExcel.Application.DisplayAlerts = 0
        $oExcel.Application.ScreenUpdating = 0
        $oExcel.ActiveWorkbook.Sheets($vSheet).Select
        $iColCount = $oExcel.Application.Selection.SpecialCells(11).Address
        $oExcel.Application.DisplayAlerts = 1
        $oExcel.Application.ScreenUpdating = 1
    EndIf
    If $iColCount = "$A$1" And $oExcel.Activesheet.Range($iColCount).Value = "" Then $iColCount = 0
    Return $iColCount
EndFunc;==>_ExcelSheetUsedRangeGet

NOTE: Now stated from the remark earlier by JackB this keeps affecting the function....

But I think it's a excel problem all together. When the book is closed and re-opened the function works properly.

Unfortunately, the UsedRange property has had a rather rocky history... Thus, I strongly suggest that you use this method with caution, for it sometimes seems to include cells that once had contents but have since been completely cleared.

Locodarwin · October 25, 2006

I found the Microsoft Q article regarding this issue:

http://support.microsoft.com/kb/231007/en-us

There is a VBA/VB workaround listed there. I'll work on adapting it to the UDF soon, unless someone else wants to take a stab at it.

-S

EDIT: Never mind - I'll adapt DaLiMan's code. Looks like it does the same thing, albeit in a slightly different way.

Edited October 25, 2006 by Locodarwin

Locodarwin · October 25, 2006

Howdy!

Okay, I have an all-encompassing solution to the above issue. I plan on adding the following function to the UDF:

;===============================================================================
;
; Description:      Return the address of the last cell in the used range of the specified worksheet.
; Syntax:           $array = _ExcelSheetUsedRangeGet($oExcel, $vSheet)
; Parameter(s):     $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   $vSheet - The sheet name or number to be checked.
; Requirement(s):   None
; Return Value(s):  On Success - Returns an array of used range values:
;                       $array[0] - The last cell used, in A1 format (if 0 is returned, worksheet is blank)
;                       $array[1] - The last cell used, in R1C1 format
;                       $array[2] - The last column used, as an integer
;                       $array[3] - The last row used, as an integer
;                   On Failure - Returns 0 (as non-array numeric value) and sets @error on errors:
;                       @error=1 - Specified object does not exist
;                       @error=2 - Invalid sheet number
;                       @error=3 - Invalid sheet name
; Author(s):        DaLiMan, SEO <locodarwin at yahoo dot com>
; Note(s):          Upon return, $array[0] will equal numeric value 0 if the worksheet is blank
;
;===============================================================================
Func _ExcelSheetUsedRangeGet($oExcel, $vSheet)
    Local $aSendBack[4], $sTemp, $aSheetList, $fFound = 0
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If IsNumber($vSheet) Then
        If $oExcel.ActiveWorkbook.Sheets.Count < $vSheet Then Return SetError(2, 0, 0)
    Else
        $aSheetList = _ExcelSheetList($oExcel)
        For $xx = 1 To $aSheetList[0]
            If $aSheetList[$xx] = $vSheet Then $fFound = 1
        Next
        If NOT $fFound Then Return SetError(3, 0, 0)
    EndIf
    $oExcel.ActiveWorkbook.Sheets($vSheet).Select
    $aSendBack[0] = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address
    $aSendBack[1] = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)
    $aSendBack[0] = StringReplace($aSendBack[0], "$", "")
    $sTemp = StringSplit($aSendBack[1], "C")
    $aSendBack[2] = Number($sTemp[2])
    $aSendBack[3] = Number(StringRegExpReplace($aSendBack[0], "\a", ""))
    If $aSendBack[0] = "A1" And $oExcel.Activesheet.Range($aSendBack[0]).Value = "" Then $aSendBack[0] = 0
    Return $aSendBack
EndFunc;==>_ExcelSheetUsedRangeGet

This uses two new constants that I've added to the UDF, so be advised that if you're going to use this function before I incorporate it, you'll need to do the following:

Replace the constant $xlR1C1 with the literal number -4150

Replace the constant $xlCellTypeLastCell with the literal number 11

This function should cover just about any desired "used range" scenario you can think of. Enjoy. :whistle:

-S

P.S. Thanks to DaLiMan for the initial code. You get first credit on the revised code.

Edited October 25, 2006 by Locodarwin

DaLiMan · October 26, 2006

P.S. Thanks to DaLiMan for the initial code. You get first credit on the revised code.

Thank's for the credit..... :whistle:

Great improvement on the function btw!

DaLiMan · October 26, 2006

Just a thought, could it be handy to include the function name's at the top of the UDF? :whistle:

I distilled this from the current version because I became sligtly irritated about finding the right function.....

#cs ---  Available function's  ---
_ExcelBookNew($fVisible = 1)
_ExcelBookOpen($sFilePath, $fVisible = 1)
_ExcelBookSave($oExcel, $fAlerts = 0)
_ExcelCellColorGet($oExcel, $sRangeOrRow, $iColumn = 1)
_ExcelCellColorSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, _
_ExcelCloseDoc($oExcel, $fSave = 1, $fAlerts = 0)
_ExcelColumnDelete($oExcel, $iColumn, $iNumCols)
_ExcelColumnInsert($oExcel, $iColumn, $iNumCols)
_ExcelColWidthGet($oExcel, $sColumn)
_ExcelColWidthSet($oExcel, $vColumn, $vWidth)
_ExcelCommentAdd($oExcel, $sComment, $sRangeOrRow, $iColumn = 1, $fVisible = 0)
_ExcelCommentDelete($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)
_ExcelCommentRead($oExcel, $sRangeOrRow, $iColumn = 1)
_ExcelCommentShow($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $fVisible = False)
_ExcelCopy($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd)
_ExcelFontGet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)
_ExcelFontGetColor($oExcel, $sRangeOrRow, $iColumn = 1)
_ExcelFontGetSize($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)
_ExcelFontSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sFontName = "Arial")
_ExcelFontSetColor($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, _
_ExcelFontSetProperties($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, _
_ExcelFontSetSize($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iFontSize = 10)
_ExcelHide($oExcel)
_ExcelHorizontalAlignSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sHorizAlign = "left")
_ExcelHyperlinkInsert($oExcel, $sLinkText, $sAddress, $sRangeOrRow, $iColumn = 1)
_ExcelNumberFormatR1C1($oExcel, $sRangeOrRowStart, $iColStart, $iRowEnd, $iColEnd, $sFormat)
_ExcelPaste($oExcel, $iRangeOrRow, $iColumn)
_ExcelPictureAdjust($oPicture, $iHorizontal, $iVertical, $iRotation = 0)
_ExcelPictureInsert($oExcel, $sFilePath, $iLeft, $iTop, $iWidth, $iHeight, $fLinkToFile = False, $fSaveWithDoc = False)
_ExcelPictureScale($oPicture, $nScaleWidth = 1, $nScaleHeight = 1, $iScaleOrigWidth = True, $iScaleOrigHeight = True, $iScaleFrom = 0)
_ExcelReadCell($oExcel, $sRangeOrRow, $iColumn = 1)
_ExcelRowDelete($oExcel, $iRow, $iNumRows)
_ExcelRowHeightGet($oExcel, $sRow)
_ExcelRowHeightSet($oExcel, $sRow, $vHeight)
_ExcelRowInsert($oExcel, $iRow, $iNumRows)
_ExcelSaveDocAs($oExcel, $sFilePath, $sType = "xls", $fAlerts = 0, $fOverWrite = 0)
_ExcelSheetAddNew($oExcel, $sName)
_ExcelSheetList($oExcel)
_ExcelSheetMove($oExcel, $vMoveSheet, $vBeforeSheet)
_ExcelSheetNameActivate($oExcel, $sSheetName)
_ExcelSheetNameDelete($oExcel, $sSheetName)
_ExcelSheetNameGet($oExcel)
_ExcelSheetNameSet($oExcel, $sSheetName)
_ExcelSheetNumActivate($oExcel, $iSheetNum)
_ExcelSheetNumDelete($oExcel, $iSheetNum)
_ExcelSheetNumUsedRowGet($oExcel, $vSheet)
_ExcelSheetUsedRangeGet($oExcel, $vSheet)
_ExcelShow($oExcel)
_ExcelSort($oExcel, $sKey, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iDirection = 2)
_ExcelSortExtended($oExcel, $sKey, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, _
_ExcelSplitWindow($oExcel, $iSplitRow, $iSplitColumn)
_ExcelVerticalAlignSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sVertAlign = "bottom")
_ExcelWriteArray($oExcel, $iStartRow, $iStartColumn, $aArray)
_ExcelWriteCell($oExcel, $sValue, $sRangeOrRow, $iColumn = 1)
_ExcelWriteFormula($oExcel, $iRow, $iColumn, $sFormula)
#ce

Locodarwin · October 26, 2006

"Slightly irritated?" Yikes. Sorry about that.

A function list has been added, along with a number of other updates, fixes, error checking, and cleanup. The next release will contain a number of cool new functions as well.

To those who have been putting up with the drastic changes, my apologies. The UDF is just having its share of growing pains. I think we're at the point now where the existing function names & syntax won't change too much from here on out.

-S

casonbang · October 28, 2006

Do you plan on adding an option to open a book as read-only?

I have a project due next week where I have to rotate through displaying several workbooks in full screen mode and sometimes they might be opened by another user for editing.

I'll try to add this myself, but I'm not too good with AutoIt yet.

Locodarwin · October 28, 2006

Do you plan on adding an option to open a book as read-only?

I have a project due next week where I have to rotate through displaying several workbooks in full screen mode and sometimes they might be opened by another user for editing.

I'll try to add this myself, but I'm not too good with AutoIt yet.

I have plans to add even more functionality to _ExcelBookOpen(), as the workbook.Open() method has quite a few useful parameters.

In the meantime, here's an update to the function that will provide what you need. This update will be in the next release of the UDF candidate, as well. I'm providing it here in case I'm unable to post the next release by the time you need it. If you're going to use it before I update the UDF, I suggest you rename it slightly (or replace the code in the UDF with it) as otherwise it will conflict with the existing function.

;===============================================================================
;
; Description:      Opens an existing workbook and returns its object identifier.
; Syntax:           $oExcel = _ExcelBookOpen($sFilePath, $fVisible = 1)
; Parameter(s):     sFilePath - Path and filename of the file to be opened
;                   $fVisible - Flag, whether to show or hide the workbook (0=not visible, 1=visible) (default=1)
;                   $fReadOnly - Flag, whether to open the workbook as read-only (True or False) (default=False)
; Requirement(s):   None
; Return Value(s):  On Success - Returns new object identifier
;                   On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
;                       @error=2 - File does not exist
; Author(s):        SEO <locodarwin at yahoo dot com>
; Note(s):          None
;
;===============================================================================
Func _ExcelBookOpen($sFilePath, $fVisible = 1, $fReadOnly = False)
    $oExcel = ObjCreate("Excel.Application")
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If NOT FileExists($sFilePath) Then Return SetError(2, 0, 0)
    If $fVisible > 1 Then $fVisible = 1
    If $fVisible < 0 Then $fVisible = 0
    With $oExcel
        .Visible = $fVisible
        .WorkBooks.Open($sFilePath, Default, $fReadOnly)
        .ActiveWorkbook.Sheets(1).Select()
    EndWith
    Return $oExcel
EndFunc ;==>_ExcelBookOpen

Good luck with your project!

-S

APRES · November 2, 2006

Sorry, there is missed link in the first post:

Attached examples: ExcelCOM_UDF_Example01.au3

If I could see example I could (hopefully) make quite simple function I need:

Open excel file

Copy 3rd column into array ( this is the simple point which I still didn't sort out )

Close excel file

Select unique record ( It could be done if easier also in Excel by filtering )

Return array back to the program

Edited November 2, 2006 by APRES

DaLiMan · November 2, 2006

I Needed a .PasteSpecial function so I've created one from your _ExcelPaste()

Maybe needs some improvements.....

;===============================================================================
;
; Description:      Send the clipboard data to a specified range.
; Syntax:           _ExcelPasteSpecial($oExcel, $sRangeOrRow, $iColumn, $xPasteType, $xPasteSpecialOp)
; Parameter(s):     $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   $sRangeOrRowStart - Either an A1 range, or an integer row number to start from if using R1C1
;                   $iColumn - The column to paste to if using R1C1 (default = 1)
;                   $xPasteType [optional] - The part of the range to be pasted.
;                           XlPasteType can be one of these XlPasteType constants (default = $xlPasteAll)
;                           - $xlPasteAll
;                           - $xlPasteAllExceptBorders
;                           - $xlPasteFormats
;                           - $xlPasteFormulas
;                           - $xlPasteComments
;                           - $xlPasteValues
;                           - $xlPasteColumnWidths
;                           - $xlPasteValidation
;                           - $xlPasteFormulasAndNumberFormats
;                           - $xlPasteValuesAndNumberFormats
;
;                   $xPasteSpecialOp [optional] - The paste operation.
;                           XlPasteSpecialOperation can be one of these constants (default = $xlPasteAll)
;                           - $xlPasteSpecialOperationAdd
;                           - $xlPasteSpecialOperationDivide
;                           - $xlPasteSpecialOperationMultiply
;                           - $xlPasteSpecialOperationNone
;                           - $xlPasteSpecialOperationSubtract
;
; Requirement(s):   None
; Return Value(s):  On Success - Returns 1
;                   On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
;                       @error=2 - Row or column invalid
;                           @extended=0 - Row invalid
;                           @extended=1 - Column invalid
; Author(s):        DaLiMan, SEO <locodarwin at yahoo dot com>
; Note(s):          None
;
;===============================================================================
Func _ExcelPasteSpecial($oExcel, $sRangeOrRow, $iColumn = 1, $xPasteType = -4104, $xPasteSpecialOp = -4142)
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If NOT StringRegExp($sRangeOrRow, "[A-Z,a-z]", 0) Then
        If $sRangeOrRow < 1 Then Return SetError(2, 0, 0)
        If $iColumn < 1 Then Return SetError(2, 1, 0)
        $oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Select
        $oExcel.Selection.PasteSpecial($xPasteType, $xPasteSpecialOp)
        Return 1
    Else
        $oExcel.Activesheet.Range($sRangeOrRow).Select
        $oExcel.Selection.PasteSpecial($xPasteType, $xPasteSpecialOp)
        Return 1
    EndIf

EndFunc ;==>_ExcelPasteSpecial

Here are the Constants so you don't have to search for them....

; Excel Constants .PasteSpecial

Const $xlPasteSpecialOperationAdd = 2

Const $xlPasteSpecialOperationDivide = 5

Const $xlPasteSpecialOperationMultiply = 4

Const $xlPasteSpecialOperationNone = -4142

Const $xlPasteSpecialOperationSubtract = 3

Const $xlPasteAll = -4104

Const $xlPasteAllExceptBorders = 7

Const $xlPasteFormats = -4122

Const $xlPasteFormulas = -4123

Const $xlPasteComments = -4144

Const $xlPasteValues = -4163

Const $xlPasteColumnWidths = 8

Const $xlPasteValidation = 6

Const $xlPasteFormulasAndNumberFormats = 11

Const $xlPasteValuesAndNumberFormats = 12

DaLiMan · November 2, 2006

Sorry, there is missed link in the first post:
Attached examples: ExcelCOM_UDF_Example01.au3

Had a copy of this example on my PC

Enjoy... :whistle:

ExcelCOM_UDF_Example01.au3

big_daddy · November 2, 2006

My honest opinion, SLOW DOWN! You are creating a monster that you will never be able to keep control of. Until you have good error checking in place keep it simple. Start one function at a time and try to break it, throw it abnormal parameters and see if your error checking is working as planned. Once you are satisfied with the error checking start working on the help file documentation and examples. After this is all complete then move on to the next function.

JackB · November 2, 2006

Further upon Big Daddy's sage advice, start (if you already haven't) a suite of tests that exercise the corners. This will then allow you to do regression testing for every release. I know this is not the fun part but from many years of experience (scars) this will be a godsend for you.

Blue_Drache · November 2, 2006

;===============================================================================
;
; Description:    Saves the active workbook of the specified Excel object with a new filename and/or type.
; Syntax:          _ExcelSaveDocAs($oExcel, $sFilePath, $sType = "xls", $fAlerts = 0, $fOverWrite = 0)
; Parameter(s):  $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   $sFilePath - Path and filename of the file to be read
;                   $sType - Excel writable filetype string = "xls|csv|txt|template|html", default "xls"
;                   $fAlerts - Flag for disabling/enabling Excel message alerts (0=disable, 1=enable)
;                   $fOverWrite - Flag for overwriting the file, if it already exists (0=no, 1=yes)
; Requirement(s):   None
; Return Value(s):  On Success - Returns 1
;                  On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
;                       @error=2 - Invalid filetype string
;                       @error=3 - File exists, overwrite flag not set
; Author(s):        SEO <locodarwin at yahoo dot net>
; Note(s):        You can only SaveAs back to the same working path the workbook was originally opened from at this time
;                   (not applicable to newly created, unsaved books).
;
;===============================================================================
Func _ExcelBookSaveAs($oExcel, $sFilePath, $sType = "xls", $fAlerts = 0, $fOverWrite = 0)
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)

Function name in the header does not match the function name header describes. To avoid using a depricated function, what is the final decision on which one will be used?

Also, when trying to use this function, I get an error.

; save the worksheet
            $temp = @TempDir & "    emp.xls"
            _ExcelBookSaveAs($oExcel, $temp, "xls", 0, 1)
            $temp = ""

With this error:

C:\DOCUME~1\AutoIt\Include\ExcelCOM_UDF.au3 (217) : ==> The requested action with this object has failed.: 
$oExcel.ActiveWorkBook.SaveAs ($sFilePath, $sType) 
$oExcel.ActiveWorkBook.SaveAs ($sFilePath, $sType)^ ERROR
+>AutoIT3.exe ended.rc:0
>Exit code: 0   Time: 1.930

I am attempting to save the file back to the existing location. Input file is an XML in the @tempdir. (OWCSheet?????.XML) and I'm trying to save as type xlNormal ("xls" or default with your function).

Microsoft Excel 2002, (version 10.4302.4219) SP-2

Oh, as a side note...using that code, it causes Excel to hang.

Edited November 2, 2006 by Blue_Drache

Locodarwin · November 2, 2006

Howdy do!

@APRES - The example file was depricated after some major updates to the existing functions were made. I haven't posted the new ones I'm working on yet. Fortunately I'll not only have examples, but another release of the UDF candidate very shortly, which will include the array reading function you're asking about.

@DaLiMan - Thanks for your contribution - it looks great.

@big_daddy - While I appreciate the advice, I hope you can appreciate the fact that I've chosen to model my efforts along different lines. I honestly don't see the need for such beefed-up COM error handling. Functions end up being twice to three times the size they would be without it, and for what? So that I can print some basic COM errors to the console? I dunno - I've been trying to keep it simple and effective without all the extra chowder. So far I've been successful - over 100 downloads per release and very few actual problems reported (aside from some header typos and such).

Furthermore, it has been my philosophy since the beginning of my career to worry about getting the beans into the pot before I worry about cleaning up the kitchen. I have a lot of functions in my UDF simply because they are needed now, not later.

To illustrate the point - you've had your UDF candidate up for quite some time, and yet I still don't use it because the features I need still aren't there. I'd rather be able to use it and deal with a few potential errors than not be able to use it at all.

@JackB - Thanks for the advice. Not only do I have regression test cases in place, I also have performance metrics that I match against, and I recently started creating interoperability suites that I run between my code and the code from other COM UDFs. I make my living doing QA. The biggest hurdle? I simply don't have the time to check every corner case. I'm hoping some folks will offer to help.

@Blue Drache - Of all things most likely to contain glaring mistakes, you can be reasonably sure it'll be the headers. These are things that aren't possible to run code QA on, because they're not code. :whistle:

So in the function you pointed out, it is the header that is wrong. A simple typo. It'll be fixed for next release. Thanks!

As to your error, is the filepath you're trying to assign to $temp really @TempDir & "{SPACE 4}emp.xls" ?

Have you tried looking at $temp with a MsgBox() after that assignment? It's an invalid path. You need to place a "\" between @TempDir and " emp.xls." And consider ditching the leading spaces of the filename, too.

-S

Yet Another -- ExcelCOM UDF

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Top Posters In This Topic

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members