Yet Another -- ExcelCOM UDF

muncherw · June 3, 2008

In the documentation for _ExcelFindInRange it says:

; $array[x][2] - The row of found cell x as an integer
; $array[x][3] - The column of found cell x as an integer

Is that correct? I seem to be getting the column from $array[x][2] and the row form $array[x][3] when I'm testing.

CODE

For $x = 1 to $array[0][0]

MsgBox(0,"","The row of found cell " & $x & " is: " & $array[$x][2] _

& @CR & "The column of found cell " & $x & " is: " & $array[$x][3])

fakefaulty · June 17, 2008

Is it possible to use these UDF with Excel Portable?

With Excel portable (Thinstall) there are no registry entries and I need to know how to create the COM entries so that

$oExcel = _ExcelBookNew( 1) ==> $oExcel = ObjCreate("Excel.Application")

will work

Need to get ObjCreate("Excel.Application") to work with portable (Thinstall) version of Excel (Microsoft office)

Can some one please give me some guidance.

Thanks in advance

Vakari · June 17, 2008

Ok I just found out this sucks.

$oExcel.ActiveSheet.Cells($sRangeOrRow, $iColumn).Locked on a cell marked as locked will return True whether the sheet is protected or not. So even if the sheet is unprotected, a cell marked as 'locked' will not be written to. I've played around a little in Excel to find out if a sheet is protected and I can't seem to figure it out. I can use ThisWorkbook.ProtectStructure but it only returns True if the workbook is protected. I can't find an equivalent value for just the sheet. Does anyone know how I can find this out?

I've found how to check if the sheet is locked: oExcel.ActiveSheet.ProtectContents Returns True if the current sheet is protected.

Merging it with _ExcelWriteCell() creates this:

Func _ExcelWriteCell($oExcel, $sValue, $sRangeOrRow, $iColumn = 1)
    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)
        If $oExcel.ActiveSheet.ProtectContents And $oExcel.ActiveSheet.Cells($sRangeOrRow, $iColumn).Locked Then ; Added
            SetError(3, 0, 0) ; Added
        Else ; Added
            $oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Value = $sValue
            Return 1
        EndIf ; Added
    Else
        If $oExcel.ActiveSheet.ProtectContents And $oExcel.ActiveSheet.Range($sRangeOrRow).Locked Then ; Added
            SetError(3, 0, 0) ; Added
        Else ; Added
            $oExcel.Activesheet.Range($sRangeOrRow).Value = $sValue
            Return 1
        EndIf ; Added
    EndIf
EndFunc

This will not attempt to write to the cell if the cell is locked AND the sheet is protected and will return 0 with an error of 3.

Brickoneer · June 19, 2008

First of all, I'd like to say thank you to all of you who've worked on this amazing UDF.

I've been using this for a while to compile large amounts of collected data into a nice excel sheet that I can view, sort and edit... it works like a dream.

Unfortunately, with excessive amounts data it slows down drastically. (several thousand rows by 4-5 columns can take a few minutes to enter into excel.)

Can anyone think of a way to enter large amounts of data into excel quickly?

(I used the array-to-sheet function as all my data is already in an array... would it be faster to simply use a for loop and a write-cell function?)

I thought about parsing it all as a giant string and using Excel's built in Text to Columns feature... but that seemed a bit crude. I figured I'd ask you guys to see if there was a more elegant solution, first.

Thanks for your time!

Nanix84 · June 21, 2008

Can anyone help me on how we can modify SaveAs... which it can be able to save a db4 format?

I have created one but I think it's not working...

The following Constant for xlDBF4 is 11.

I just added the constant:

Const $xlDBF4 = 11oÝ÷ Ù8^¨v'âyÛayû§q«¢+ÙÕ¹}á±  ½½MÙÌ ÀÌØí½á°°ÀÌØíÍ¥±AÑ °ÀÌØíÍQåÁôÅÕ½Ðíá±ÌÅÕ½Ðì°ÀÌØí±ÉÑÌôÀ°ÀÌØí=ÙÉ]É¥ÑôÀ°ÀÌØíÍAÍÍÝ½ÉôÅÕ½ÐìÅÕ½Ðì°ÀÌØíÍ]É¥ÑAÍÍÝ½ÉôÅÕ½ÐìÅÕ½Ðì°ÀÌØí¥ÍÍ5½ôÄ°|($$$$$$ÀÌØí¥
½¹±¥ÑIÍ½±ÕÑ¥½¸ôÈ¤(%%9=P%Í=¨ ÀÌØí½á°¤Q¡¸IÑÕÉ¸MÑÉÉ½È Ä°À°À¤(%%ÀÌØíÍQåÁôÅÕ½Ðíá±ÌÅÕ½Ðì½ÈÀÌØíÍQåÁôÅÕ½ÐíÍØÅÕ½Ðì½ÈÀÌØíÍQåÁôÅÕ½ÐíÑáÐÅÕ½Ðì½ÈÀÌØíÍQåÁôÅÕ½ÐíÑµÁ±ÑÅÕ½Ðì½ÈÀÌØíÍQåÁôÅÕ½Ðí¡Ñµ°ÅÕ½ÐìQ¡¸($%%ÀÌØíÍQåÁôÅÕ½Ðíá±ÌÅÕ½ÐìQ¡¸ÀÌØíÍQåÁôÀÌØíá±9½Éµ°($%%ÀÌØíÍQåÁôÅÕ½ÐíÍØÅÕ½ÐìQ¡¸ÀÌØíÍQåÁôÀÌØíá±
MY5M=L($%%ÀÌØíÍQåÁôÅÕ½ÐíÑáÐÅÕ½ÐìQ¡¸ÀÌØíÍQåÁôÀÌØíá±QáÑ]¥¹½ÝÌ($%%ÀÌØíÍQåÁôÅÕ½ÐíÑµÁ±ÑÅÕ½ÐìQ¡¸ÀÌØíÍQåÁôÀÌØíá±QµÁ±Ñ($%%ÀÌØíÍQåÁôÅÕ½Ðí¡Ñµ°ÅÕ½ÐìQ¡¸ÀÌØíÍQåÁôÀÌØíá±!Ñµ°($%%ÀÌØíÍQåÁôÅÕ½ÐíÐÅÕ½ÐìQ¡¸ÀÌØíÍQåÁôÀÌØíá±    Ð(%±Í($%IÑÕÉ¸MÑÉÉ½È È°À°À¤(%¹%(%%ÀÌØí±ÉÑÌÐìÄQ¡¸ÀÌØí±ÉÑÌôÄ(%%ÀÌØí±ÉÑÌ±ÐìÀQ¡¸ÀÌØí±ÉÑÌôÀ($ÀÌØí½á°¹ÁÁ±¥Ñ¥½¸¹¥ÍÁ±å±ÉÑÌôÀÌØí±ÉÑÌ($ÀÌØí½á°¹ÁÁ±¥Ñ¥½¸¹MÉ¹UÁÑ¥¹ôÀÌØí±ÉÑÌ(%%¥±á¥ÍÑÌ ÀÌØíÍ¥±AÑ ¤Q¡¸($%%9=PÀÌØí=ÙÉ]É¥ÑQ¡¸%IÑÕÉ¸MÑÉÉ½È Ì°À°À¤($%¥±±Ñ ÀÌØíÍ¥±AÑ ¤(%¹%(%%ÀÌØíÍAÍÍÝ½ÉôÅÕ½ÐìÅÕ½Ðì¹ÀÌØíÍ]É¥ÑAÍÍÝ½ÉôÅÕ½ÐìÅÕ½ÐìQ¡¸ÀÌØí½á°¹Ñ¥Ù]½É   ½½¬¹MÙÌ ÀÌØíÍ¥±AÑ °ÀÌØíÍQåÁ°Õ±Ð°Õ±Ð°Õ±Ð°Õ±Ð°ÀÌØí¥ÍÍ5½°ÀÌØí¥
½¹±¥ÑIÍ½±ÕÑ¥½¸¤(%%ÀÌØíÍAÍÍÝ½É±ÐìÐìÅÕ½ÐìÅÕ½Ðì¹ÀÌØíÍ]É¥ÑAÍÍÝ½ÉôÅÕ½ÐìÅÕ½ÐìQ¡¸ÀÌØí½á°¹Ñ¥Ù]½É   ½½¬¹MÙÌ ÀÌØíÍ¥±AÑ °ÀÌØíÍQåÁ°ÀÌØíÍAÍÍÝ½É°Õ±Ð°Õ±Ð°Õ±Ð°ÀÌØí¥ÍÍ5½°ÀÌØí¥
½¹±¥ÑIÍ½±ÕÑ¥½¸¤(%%ÀÌØíÍAÍÍÝ½É±ÐìÐìÅÕ½ÐìÅÕ½Ðì¹ÀÌØíÍ]É¥ÑAÍÍÝ½É±ÐìÐìÅÕ½ÐìÅÕ½ÐìQ¡¸ÀÌØí½á°¹Ñ¥Ù]½É   ½½¬¹MÙÌ ÀÌØíÍ¥±AÑ °ÀÌØíÍQåÁ°ÀÌØíÍAÍÍÝ½É°ÀÌØíÍ]É¥ÑAÍÍÝ½É°Õ±Ð°Õ±Ð°ÀÌØí¥ÍÍ5½°ÀÌØí¥
½¹±¥ÑIÍ½±ÕÑ¥½¸¤(%%ÀÌØíÍAÍÍÝ½ÉôÅÕ½ÐìÅÕ½Ðì¹ÀÌØíÍ]É¥ÑAÍÍÝ½É±ÐìÐìÅÕ½ÐìÅÕ½ÐìQ¡¸ÀÌØí½á°¹Ñ¥Ù]½É   ½½¬¹MÙÌ ÀÌØíÍ¥±AÑ °ÀÌØíÍQåÁ°Õ±Ð°ÀÌØíÍ]É¥ÑAÍÍÝ½É°Õ±Ð°Õ±Ð°ÀÌØí¥ÍÍ5½°ÀÌØí¥
½¹±¥ÑIÍ½±ÕÑ¥½¸¤(%%9=PÀÌØí±ÉÑÌQ¡¸($$ÀÌØí½á°¹ÁÁ±¥Ñ¥½¸¹¥ÍÁ±å±ÉÑÌôÄ($$ÀÌØí½á°¹ÁÁ±¥Ñ¥½¸¹MÉ¹UÁÑ¥¹ôÄ(%¹%(%IÑÕÉ¸Ä)¹Õ¹ìôôÐí}á±  ½½MÙ

Please tell me where did I go wrong...

friends · June 27, 2008

i wonder if this Excel UDF's syntax format has been added into SciTe so that when we type it, it will come out the syntax for us as a reference.

it would be great if being added to SciTe !

anyone ?

litlmike · July 9, 2008

i wonder if this Excel UDF's syntax format has been added into SciTe so that when we type it, it will come out the syntax for us as a reference.
it would be great if being added to SciTe !
anyone ?

I am working on that, hope to be within the next week or two to have it ready for UDF submission. Then it will be up to GaryFrost to get this going from there.

litlmike · July 14, 2008

FYI to everyone, LocoDarwin will be away from this project for a while, and has asked that I help manage it while he is away. I don't have the knowledge base that he does, but I will assist in any way that I can. We have been working on this project for awhile and now we will officially have it included in the next AutoIt install set. The following functions will be available for in the official versions of AutoIt. More coming later.

;_ExcelBookNew
;_ExcelBookOpen
;_ExcelBookSave
;_ExcelBookSaveAs
;_ExcelBookClose
;_ExcelWriteCell
;_ExcelWriteFormula
;_ExcelWriteArray
;_ExcelWriteSheetFromArray
;_ExcelHyperlinkInsert
;_ExcelNumberFormat
;_ExcelReadCell
;_ExcelReadArray
;_ExcelReadSheetToArray
;_ExcelRowDelete
;_ExcelColumnDelete
;_ExcelRowInsert
;_ExcelColumnInsert
;_ExcelSheetAddNew
;_ExcelSheetDelete
;_ExcelSheetNameGet
;_ExcelSheetNameSet
;_ExcelSheetList
;_ExcelSheetActivate
;_ExcelSheetMove

Edited July 14, 2008 by litlmike

bowain · July 15, 2008

This UDF should come with a warning label....

Use of this UDF can be addicting, use with proper supervision.

I've been hold up the last few days at home after an operation and decided to work on a few 'ideas' to make work easier. This UDF has become the backbone of at least 4 more 'ideas', thanks guys

In all honestly thanks for all the hard work on this, it is greatly appreciated and credit will be given in the script and the application.

muttley You guys ROCK! as my 10 year old step daughter would say!

big_daddy · July 16, 2008

I've rewrote/renamed _ExcelAttach() to _ExcelBookAttach(). I've also rewritten _ExcelBookClose() to accept a workbook object. See related topic.

; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelBookAttach
; Description ...: Attach to the first existing instance of Microsoft Excel where the search string matches based on the selected mode.
; Syntax.........: _ExcelBookAttach($s_string, $s_mode = "FilePath")
; Parameters ....: $s_string - String to search for
;                  $s_mode   - Optional: specifies search mode:
;                  |FileName - Name of the open workbook
;                  |FilePath - (Default) Full path to the open workbook
;                  |Title    - Title of the Excel window
; Return values .: Success   - Returns an object variable pointing to the Excel.Application, workbook object
;                  Failure   - Returns 0 and sets @ERROR = 1
; Author ........: Bob Anthony (big_daddy)
; Modified.......:
; Remarks .......:
; Related .......:
; Link ..........;
; Example .......;
; ===============================================================================================================================
Func _ExcelBookAttach($s_string, $s_mode = "FilePath")

    Local $o_Result, $o_workbook, $o_workbooks
    
    If $s_mode = "filepath" Then
        $o_Result = ObjGet($s_string)
        If Not @error And IsObj($o_Result) Then
            Return $o_Result
        EndIf
    EndIf

    $o_Result = ObjGet("", "Excel.Application")
    If @error Or Not IsObj($o_Result) Then
        ConsoleWrite("--> Warning from function _ExcelAttach, No existing Excel.Application object" & @CR)
        Return SetError(1, 0, 0)
    EndIf

    $o_workbooks = $o_Result.Application.Workbooks
    If Not IsObj($o_workbooks) Or $o_workbooks.Count = 0 Then
        ConsoleWrite("--> Warning from function _ExcelAttach, No existing Excel.Application windows" & @CR)
        Return SetError(1, 0, 0)
    EndIf

    For $o_workbook In $o_workbooks

        Switch $s_mode
            Case "filename"
                If $o_workbook.Name = $s_string Then
                    Return $o_workbook
                EndIf
            Case "filepath"
                If $o_workbook.FullName = $s_string Then
                    Return $o_workbook
                EndIf
            Case "title"
                If ($o_workbook.Application.Caption) = $s_string Then
                    Return $o_workbook
                EndIf
            Case Else
                ConsoleWrite("--> Error from function _ExcelAttach, Invalid Mode Specified" & @CR)
                Return SetError(1, 0, 0)
        EndSwitch
    Next
    
    ConsoleWrite("--> Warning from function _ExcelAttach, No Match" & @CR)
    Return SetError(1, 0, 0)
EndFunc   ;==>_ExcelBookAttach

;===============================================================================
;
; Description:      Closes the active workbook and removes the specified Excel object.
; Syntax:           _ExcelBookClose($oExcel, $fSave = 1, $fAlerts = 0)
; Parameter(s):     $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   $fSave - Flag for saving the file before closing (0=no save, 1=save) (default = 1)
;                   $fAlerts - Flag for disabling/enabling Excel message alerts (0=disable, 1=enable) (default = 0)
; 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 - File exists, overwrite flag not set
; Author(s):        SEO <locodarwin at yahoo dot com>
; Note(s):          None
;
;===============================================================================
Func _ExcelBookClose($oExcel, $fSave = 1, $fAlerts = 0)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    $sObjName = ObjName($oExcel)
    
    If $fSave > 1 Then $fSave = 1
    If $fSave < 0 Then $fSave = 0
    If $fAlerts > 1 Then $fAlerts = 1
    If $fAlerts < 0 Then $fAlerts = 0
    
    ; Save the users specified settings
    $fDisplayAlerts = $oExcel.Application.DisplayAlerts
    $fScreenUpdating = $oExcel.Application.ScreenUpdating
    ; Make necessary changes
    $oExcel.Application.DisplayAlerts = $fAlerts
    $oExcel.Application.ScreenUpdating = $fAlerts
    
    If $fSave Then
        $oExcel.ActiveWorkBook.Save
    EndIf
    
    ; Restore the users specified settings
    $oExcel.Application.DisplayAlerts = $fDisplayAlerts
    $oExcel.Application.ScreenUpdating = $fScreenUpdating
    
    Switch $sObjName
        Case "_Workbook"
            ; Check if multiple workbooks are open
            ; Do not close application if there are
            If $oExcel.Application.Workbooks.Count > 1 Then
                $oExcel.Close
            Else
                $oExcel.Application.Quit
            EndIf
        Case "_Application"
            $oExcel.Quit
        Case Else
            Return SetError(1, 0, 0)
    EndSwitch
    
    Return 1
EndFunc   ;==>_ExcelBookClose

litlmike · July 16, 2008

I've rewrote/renamed _ExcelAttach() to _ExcelBookAttach(). I've also rewritten _ExcelBookClose() to accept a workbook object. See

This is lovely, thanks for the rewrites. I have been avoiding including _ExcelBookAttach() because of its limitations, thanks for taking the lead on that. Good modification to _ExcelBookClose() as well. I will package and send it to GaryFrost. Thanks again!

litlmike · July 17, 2008

Here is the new package:

Excel.zip

ChrisJakarta · July 19, 2008

Here is the new package:
Excel.zip

May we asssume this new version is fully compatible with the the last version of ExcelCOM_UDF? So that all we need to do is change the include name?

Thanks

Chris

Edited July 19, 2008 by ChrisJakarta

ivan · July 19, 2008

I'm using v3.2.13.4 (beta), examples fail to open in scite because they use

#include <ExcelCOM_UDF.au3>

which should read

#include <Excel.au3>

since ExcelCOM_UDF.au3 does not exist in the include directory, but Excel.au3 does.

IVAN

litlmike · July 21, 2008

May we asssume this new version is fully compatible with the the last version of ExcelCOM_UDF? So that all we need to do is change the include name?
Thanks
Chris

They should work for all the functions that are still included. The original ExcelCOM_UDF.au3 contained 68 Functions, whereas the new version Excel.au3 contains 26 functions. Those 26 functions will still work the same, but if you have a script that contains the other 42 functions that are not yet included, you will have to #include both UDFs for now. The goal is to get all 68 functions in the official version of Au3, but that will take some considerable time. Let me know if there are functions that you would like to see included in the new version and I will add it to the top of the list.

Thanks

litlmike · July 21, 2008

I'm using v3.2.13.4 (beta), examples fail to open in scite because they use
#include <ExcelCOM_UDF.au3>
which should read
#include <Excel.au3>
since ExcelCOM_UDF.au3 does not exist in the include directory, but Excel.au3 does.
IVAN

You are correct, this was an oversight that I made when I submitted that version to be included in Au3. Those corrections were made last week, but my guess is that GaryFrost has not had the opportunity to update Au3 with the newest version of the Excel UDF. Thanks for pointing out the typos, but they have been changed, but not updated as of yet. For now, you can just change the #include <ExcelCOM_UDF.au3> to #include <Excel.au3>. Thanks

GaryFrost · July 21, 2008

You are correct, this was an oversight that I made when I submitted that version to be included in Au3. Those corrections were made last week, but my guess is that GaryFrost has not had the opportunity to update Au3 with the newest version of the Excel UDF. Thanks for pointing out the typos, but they have been changed, but not updated as of yet. For now, you can just change the #include <ExcelCOM_UDF.au3> to #include <Excel.au3>. Thanks

You need to send me the file(s) when they need to be updated, I don't keep up with all the threads.

So I'll be waiting for the updated file(s).

ChrisJakarta · July 23, 2008

Let me know if there are functions that you would like to see included in the new version and I will add it to the top of the list.

That's a great offer - thanks!

I've just checked it out, and it seems the following two functions are those I would like to see:

_ExcelSheetUsedRangeGet()

_ExcelFindInRange()

But I can manage with the 'old' version, so it's not urgent...

Again, thanks

Chris

enaiman · July 28, 2008

Would someone be so kind and provide a link to version 1.4?

Version 1.5 is not complete yet and there is no other version available.

Thank you,

PsaltyDS · July 29, 2008

Would someone be so kind and provide a link to version 1.4?
Version 1.5 is not complete yet and there is no other version available.
Thank you,

The handling of the first post has gotten kind of sloppy. It looks like Big_daddy actually tried to fix it, but you have to ignore the text to see it. In the little quote block that says "Attached file: ExcelCOM_UDF.au3 (v1.32)", the link to ExcelCOM_UDF.au3 is actually for v1.4 dated 01-04-08.

Reproduced link here: ExcelCOM_UDF.au3 ver. 1.4 dated 01-04-08

P.S. Fixed: Big_daddy's on the job!

Edited July 29, 2008 by PsaltyDS

Sign In

Yet Another -- ExcelCOM UDF

Recommended Posts

muncherw

Top Posters In This Topic

Top Posters In This Topic

fakefaulty

Vakari

Brickoneer

Nanix84

friends

litlmike

litlmike

bowain

big_daddy

litlmike

litlmike

ChrisJakarta

ivan

litlmike

litlmike

GaryFrost

ChrisJakarta

enaiman

PsaltyDS

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Browse

AutoIt Resources

Release

Beta