Jump to content

Update the Excel UDF that comes with AutoIt


water
 Share

Recommended Posts

Which of the functions of the written by golfinhu should be implemented in the "official" Excel UDF?

_ExcelColumnLetter: Convert the column number to letter

_ExcelColumnNumber: Convert the column letter to number

_ExcelFormatR1C1ToA1: Convert R1C1 format to A1 format

_ExcelFormatA1ToR1C1: Convert A1 format to R1C1 format

_ExcelReadSheetToArray: Create a 2D array from the rows/columns of the active worksheet (*) Already went into production as of Beta 3.3.9.5 per BugTrack #2219

_ExcelPasteSpecial: Send the clipboard data to a specified range

_ExcelFormatPainter: Copy the formatting of a range to another range

_ExcelWriteSheetFromArray: Writes a 2D array to the active worksheet (*)

_ExcelWriteArray: Write an array to a row or column on the active worksheet of the specified Excel object (*)

(*) This are faster versions of already existing functions

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

If your scripts work with Excel 2000 now they should work with the brushed up version of the UDF as well.

Backward compatibility is one of the main goals.

Well, some of what works on Excel 2000, I do in COM and not use a UDF. One of the things in particular that I'd like to use are the sort functions, which are very different from older versions to newer versions. Version checking will most likely be mandatory if we're to implement such a function in the revised UDF. Edited by GMK
Link to comment
Share on other sites

Which of the functions of the written by golfinhu should be implemented in the "official" Excel UDF?

_ExcelColumnLetter: Convert the column number to letter

_ExcelColumnNumber: Convert the column letter to number

_ExcelFormatR1C1ToA1: Convert R1C1 format to A1 format

_ExcelFormatA1ToR1C1: Convert A1 format to R1C1 format

_ExcelReadSheetToArray: Create a 2D array from the rows/columns of the active worksheet (*)

_ExcelPasteSpecial: Send the clipboard data to a specified range

_ExcelFormatPainter: Copy the formatting of a range to another range

_ExcelWriteSheetFromArray: Writes a 2D array to the active worksheet (*)

_ExcelWriteArray: Write an array to a row or column on the active worksheet of the specified Excel object (*)

(*) This are faster versions of already existing functions

I think the faster (fastest possible) versions of existing functions should be implemented. _ExcelPasteSpecial and _ExcelFormatPainter could also come in handy. I'm not sure the column and row formatting are necessary as core functions, but may come in handy as undocumented internal functions.
Link to comment
Share on other sites

Functions we now have in the Excel UDF will work the same way after the brush up. This means that only new optional parameters will be added or existing parameters will get additional values.

For new functions I will try to make them as compatible with older versions as possible. At least Office 2003 and later will be supported.

I have Office 2002 to test here so new functions will work for this version as well.

I will post a new version of the UDF on page 1 as soon as testing is sensible. You could then give it a try with Office 2000.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

I'm not sure the column and row formatting are necessary as core functions, but may come in handy as undocumented internal functions.

This are functions called internally by some of the listed functions.

If anyone else wants to see this new functions, please vote.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

That one already went into production as of Beta 3.3.9.5 per BugTrack #2219.

Yeh, I was going to mention that I committed that change already.

UDF List:

 
_AdapterConnections()_AlwaysRun()_AppMon()_AppMonEx()_ArrayFilter/_ArrayReduce_BinaryBin()_CheckMsgBox()_CmdLineRaw()_ContextMenu()_ConvertLHWebColor()/_ConvertSHWebColor()_DesktopDimensions()_DisplayPassword()_DotNet_Load()/_DotNet_Unload()_Fibonacci()_FileCompare()_FileCompareContents()_FileNameByHandle()_FilePrefix/SRE()_FindInFile()_GetBackgroundColor()/_SetBackgroundColor()_GetConrolID()_GetCtrlClass()_GetDirectoryFormat()_GetDriveMediaType()_GetFilename()/_GetFilenameExt()_GetHardwareID()_GetIP()_GetIP_Country()_GetOSLanguage()_GetSavedSource()_GetStringSize()_GetSystemPaths()_GetURLImage()_GIFImage()_GoogleWeather()_GUICtrlCreateGroup()_GUICtrlListBox_CreateArray()_GUICtrlListView_CreateArray()_GUICtrlListView_SaveCSV()_GUICtrlListView_SaveHTML()_GUICtrlListView_SaveTxt()_GUICtrlListView_SaveXML()_GUICtrlMenu_Recent()_GUICtrlMenu_SetItemImage()_GUICtrlTreeView_CreateArray()_GUIDisable()_GUIImageList_SetIconFromHandle()_GUIRegisterMsg()_GUISetIcon()_Icon_Clear()/_Icon_Set()_IdleTime()_InetGet()_InetGetGUI()_InetGetProgress()_IPDetails()_IsFileOlder()_IsGUID()_IsHex()_IsPalindrome()_IsRegKey()_IsStringRegExp()_IsSystemDrive()_IsUPX()_IsValidType()_IsWebColor()_Language()_Log()_MicrosoftInternetConnectivity()_MSDNDataType()_PathFull/GetRelative/Split()_PathSplitEx()_PrintFromArray()_ProgressSetMarquee()_ReDim()_RockPaperScissors()/_RockPaperScissorsLizardSpock()_ScrollingCredits_SelfDelete()_SelfRename()_SelfUpdate()_SendTo()_ShellAll()_ShellFile()_ShellFolder()_SingletonHWID()_SingletonPID()_Startup()_StringCompact()_StringIsValid()_StringRegExpMetaCharacters()_StringReplaceWholeWord()_StringStripChars()_Temperature()_TrialPeriod()_UKToUSDate()/_USToUKDate()_WinAPI_Create_CTL_CODE()_WinAPI_CreateGUID()_WMIDateStringToDate()/_DateToWMIDateString()Au3 script parsingAutoIt SearchAutoIt3 PortableAutoIt3WrapperToPragmaAutoItWinGetTitle()/AutoItWinSetTitle()CodingDirToHTML5FileInstallrFileReadLastChars()GeoIP databaseGUI - Only Close ButtonGUI ExamplesGUICtrlDeleteImage()GUICtrlGetBkColor()GUICtrlGetStyle()GUIEventsGUIGetBkColor()Int_Parse() & Int_TryParse()IsISBN()LockFile()Mapping CtrlIDsOOP in AutoItParseHeadersToSciTE()PasswordValidPasteBinPosts Per DayPreExpandProtect GlobalsQueue()Resource UpdateResourcesExSciTE JumpSettings INISHELLHOOKShunting-YardSignature CreatorStack()Stopwatch()StringAddLF()/StringStripLF()StringEOLToCRLF()VSCROLLWM_COPYDATAMore Examples...

Updated: 22/04/2018

Link to comment
Share on other sites

That one already went into production as of Beta 3.3.9.5 per BugTrack #2219.

Thanks a lot for this information. Mentioned on all relevant places of this thread.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Spiff59,

which of the two possibilities I have listed would you like to see implemented?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

I've come to agree that the FileOpen()/FileClose() test is inappropriate.

The assumption ought to be that in most cases the spreadsheet will be available for update and finding it already in a locked state will be exceptional. So, implementing additional code that is much faster in the rare cases, but slower in normal cases, no longer seems a good idea to me. The $oExcel.ActiveWorkBook.Readonly test seems the correct way to identify a failed open-for-write call.

That leaves us to decide how to treat the result. Treating a request (for an updatable spreadsheet) that can not be accomodated as an error seems the proper route to me. I guess we'd have to weigh in the likelyhood of script-breaking, or the effort required to fix a broken script, and decide whether a "patch" is justified over a "fix".

What do others think?

Link to comment
Share on other sites

Let's assume we close the read-only copy of the workbook and return an error.

This will break all scripts which just do read-only operations but didn't set the $fReadOnly flag to True.

All scripts modifying the workbook would crash at the latest when saving the workbook.

I think we should return an error and close the read-only copy. As there is a flag to define the access mode the function should only do what it is told to do. If the function can't do it an error schould be returned. Even when this is script breaking.

I would be glad to hear what others think!

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

You're right, since the function defaults to $fReadOnly = False, and it's an optional parameter, this could break a lot more scripts than I was thinking. Were this day one of the function, I might vote for $fReadOnly = True being the optional default parameter, and making users specifically request write access, then returning an error when it isn't given. But since this function has been "in the wild" for a long time, now I'm wavering on what to do.

Am betting (or hoping) others will pipe in to help form a consensus.

Edited by Spiff59
Link to comment
Share on other sites

Neither the production UDF or golfinhu's ExcelEX UDF have any sort of search function.

I wrote this when bored to help out someone in a forum thread here. I wonder if it might be a worthy addition?

#include <Array.au3>

$Workbook = @ScriptDir & "test.xls"

;===================================================================================================================================
; example calling function when workbook is not open, passing workbook as a path/filename
;===================================================================================================================================
; Find "1939" in Column C, exact match, case insensitive, return matching column data and data of column to left of match
$aResult = _ExcelFindCells($Workbook, "1939", "C:C", True, False, "0,0|0,-1")
_ArrayDisplay($aResult, "search using filename")

;===================================================================================================================================
; example calling function (repeatedly) when workbook is already open, passing workbook as an object
;===================================================================================================================================
$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 0
$oExcel.WorkBooks.Open($Workbook)

; Find "The" in workbook, partial match, case sensitive, return matching column data
$aResult = _ExcelFindCells($oExcel, "The", "*", False, True, "0,0")
_ArrayDisplay($aResult, "search using object")

; Find "20" between A10 and B25, partial match, case insensitive, return matching column data
$aResult = _ExcelFindCells($oExcel, "20", "A10:D25", False, False, "0,0")
_ArrayDisplay($aResult, "search using object")

; Find "* Story" in workbook, exact match (with wildcard), case sensitive, return matching column data and 2 columns to the right
$aResult = _ExcelFindCells($oExcel, "* Story", "*", True, True, "0,0|0,2")
_ArrayDisplay($aResult, "search using object")

; Find "1959" in workbook, exact match, case sensitive, return no column data
$aResult = _ExcelFindCells($Workbook, "1959", "*", True)
_ArrayDisplay($aResult, "search using object")

$oExcel.Quit
$oExcel = ""
Exit

; #FUNCTION# =======================================================================================================================
; Name...........: _ExcelFindCells
; Description ...: Searches an Excel workbook returning the addresses of cells matching the search string, and optionally the
;    context of any cells in proximity to each matched cell as specified by the $sData parameter.
; Syntax.........: _ExcelFindCells($sFilePath, $sSearch [, $sRange] [, $bFull] [, $bCase] [,$sData])
; Parameters ....: $sFilePath - Object to an open workbook, or, full path/filename of an unopened workbook
;                $sSearch - Search string (* wildcard allowed - only effective when $bFull = True)
;                $sRange    - Range of cells to search. Format = "A1:D99". Default = "*"
;                $bFull  - False = allow partial match (default), True = entire cell contents must match exactly
;                $bCase  - False = case insensitive (default), True = case sensitive
;                $sData  - Cell(s) from which to return cell values. Specified as "row,col" offsets from the matching cell,
;       delimited by "|". Default = "" (do not return cell contents)
;                            Example: "0,0|0,1" will return the values of the matched cell and one cell to the right.
; Return values .: Success  - Returns a 2-dimension, 1-based array containing matching cell addresses in column 0, and
;        a variable number of columns containing cell contents (per $sData).
;       Element [0][0] contains number of rows returned. Element [0][1] contains number of data columns requested
;                @error=1 - Unable to start Excel application
;                @error=2 - $sFilePath does not exist
; Author ........: Spiff59
; ==================================================================================================================================
Func _ExcelFindCells($sFilePath, $sSearch = "*", $sRange = "*", $bFull = False, $bCase = False, $sData = "")
    If IsObj($sFilePath) Then
        Local $oExcel = $sFilePath
    Else
        If Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
        Local $oExcel = ObjCreate("Excel.Application") ; if called with filename, start excel
        If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
        $oExcel.Visible = 0
        $oExcel.WorkBooks.Open($sFilePath)
    EndIf
    Local $aData[1] = [0], $sBEL = Chr(7), $sBEL2 = $sBEL & $sBEL
    Local $sAddressList, $sValueList, $aTemp
    If $bFull <> True Then $bFull = False
    If $bCase <> True Then $bCase = False
    If $sData Then $aData = StringSplit($sData, "|") ; offsets of cell data to return
    If $sRange = "*" Then
;       $oRange = $oExcel.ActiveSheet.UsedRange
        $oRange = $oExcel.Cells
    Else
        $oRange = $oExcel.Range($sRange)
    EndIf
    $oMatch = $oRange.Find($sSearch, Default, Default, $bFull, Default, Default, $bCase)
    If Isobj($oMatch) Then
        $oFirst = $oMatch.Address
        While IsObj($oMatch)
            $sAddressList &= StringReplace($oMatch.Address, "$", "") & $sBEL ; save address of match
            If $aData[0] Then ; cell data requested
                For $x = 1 to $aData[0]
                   $aTemp = StringSplit($aData[$x], ",")
                    $sValueList &= $oMatch.Offset($aTemp[1], $aTemp[2]).Value & $sBEL
                Next
                $sValueList &= $sBEL
            EndIf
            $oMatch = $oRange.Findnext($oMatch)
            If $oMatch.Address = $oFirst then ExitLoop
        WEnd
        $oFirst = ""
    EndIf
    $oMatch = ""
    $oRange = ""
    If Not IsObj($sFilePath) Then $oExcel.Quit ; if called with filename, shutdown excel
    $oExcel = ""

    Local $iCols = $aData[0] + 1 + ($aData[0] = 0)
    If Not $sAddressList Then ; no matches, return array with 0 count
        Local $sReturn[1][$iCols] = [[0,$aData[0]]] ;format empty output array
     Return $sReturn
    EndIf

    $sAddressList = StringSplit(StringTrimRight($sAddressList, 1), $sBEL) ; create array of matching cell addresses
    Local $sReturn[$sAddressList[0] + 1][$iCols] = [[$sAddressList[0], $aData[0]]] ; format output array
    $sValueList = StringSplit(StringTrimRight($sValueList, 2), $sBEL2, 1) ; create array of requested cell values
    For $x = 1 to $sReturn[0][0]
        $sReturn[$x][0] = $sAddressList[$x] ; matching cell address
        If $aData[0] Then ; cell data requested
           $aTemp = StringSplit($sValueList[$x], $sBEL)
         For $y = 1 to $aTemp[0] ; requested cell data
                $sReturn[$x][$y] = $aTemp[$y]
         Next
        EndIf
    Next
    Return $sReturn
EndFunc

I sometimes assume if you're bothering to write code you ought to at least be able to send a function valid parameters, so it may be that some additional parameter (bounds) checking or error handling would be a good idea.

Edit: added test.xls attachment

test.xls

Edited by Spiff59
Link to comment
Share on other sites

Hi Spiff59,

that's a very good idea - will add it to the ToDo list right now.

I hope to post the first alpha of the enhanced UDF for you to play with in the next few days.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

I have added the first Alpha version of the new Excel UDF to post #1 for you to play with.

Any feedback is needed and welcome!

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Backward compatibility is one of the main goals when enhancing the Excel UDF. But there are some issues with the design of some functions.

I would like to discuss this issues and get as much feedback as possible before I decide what to do.

--------------------------------------------------------------------------------------------------------------------------

_ExcelBookOpen and _ExcelBookNew start a new instance of Excel for every workbook

That's a waste of ressources.

The way it works now it's not easy to get a list of open workbooks (opened by the UDF or the user)

Suggestion: I would like to change this functions so they attach to an existing instance of Excel

--------------------------------------------------------------------------------------------------------------------------

_ExcelBookOpen opens an already open workbook as readonly even when the user wants write access (parameter $fReadOnly = False)

_ExcelBookOpen now doesn't return an error but returns an object to a read-only copy of the spreadsheet. This could crash the script when you modify the read-only copy or try to save the workbook.

Possible solutions:

1) Still return the read-only copy and set @error to 0 but set @extended to 1 so the user can decide what to do. This will have the least impact on existing scripts

2) Return an error. The user has to set parameter $fReadOnly to True to get a read-only copy of the workbook. Could be script breaking.

Suggestion: Return an error. It's a clean solution and will only break a few scripts

Please tell me what you think and which solution I should implement!

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

I believe at work my Excel.au3 already has a modified _ExcelBookOpen() function in it.

I made the changes mentioned here to the function:

And also applied the logic talked about in the very next post in that thread...

I changed the start of the function to behave like the _ExcelFindCell function. Where if it was passed a filename, it would start and then stop an excel process, but if passed an object, it would use the existing object and not start and stop excel. I occasionally run a batch process that updates as many as 20,000 spreadsheets, and found ExcelBookOpen() unusable in it's current form (the job would run for hours!).

Link to comment
Share on other sites

Why would you have excelbookopen attach to an opened file? You would attach to one that's open using the function designed for it, ExcelBookAttach, and open a file using ExcelBookOpen. If someone is using the wrong function, why would you aid them in doing that?

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

Link to comment
Share on other sites

I;m not suggesting attaching to an open file, but using an already running instance of Excel.

If you're processing a large number of Excel files, _ExcelBookOpen is dog-slow because it starts and stops Excel for every file processed. I'm not sure what I changed on my copy of the function at work, I don't have access to it presently. Or, it may be I opted to not use the UDF at all in multiple-file scripts. But I've always considered the inability to have a loop that calls _ExcelBookOpen, does some processing, and then calls _ExcelBookSave, without constantly starting and stopping the Excel process a big drawback to the UDF.

Link to comment
Share on other sites

The functions will be changed this way: Check if an instance of Excel is already running. If true, don't start a new instance.

$oExcel = ObjGet("", "Excel.Application")
if @error Then $oExcel = ObjCreate("Excel.Application")
This saves ressources (processing time, memory) and allows to access all workbooks at once. Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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
 Share

×
×
  • Create New...