Jump to content
Sign in to follow this  
water

Update the Excel UDF that comes with AutoIt

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 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
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

Share this post


Link to post
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.

Share this post


Link to post
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 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
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 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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

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

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

Share this post


Link to post
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

Share this post


Link to post
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 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
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 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
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?

Share this post


Link to post
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 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
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 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
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 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
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 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
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!).

Share this post


Link to post
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

Share this post


Link to post
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.

Share this post


Link to post
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 2020-07-21 - Version 1.5.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
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
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Langmeister
      Hi, I'm sure that this is a simple question regarding webdriver udf but my search on the forum did not result in the exact same scenario that I need. I will adapt it to my corporate needs but at first I want it to work with an example everybody can reproduce if needed.
      I navigate to https://www.daysoftheyear.com/ and want to find out what special day today is. Using the following Code returns the wanted text that today is 'shark awareness day' in the console but it is not pasted in notepad as it is not copied as it probably should be.
      #include "wd_core.au3" #include "wd_helper.au3" Local $sDesiredCapabilities, $sSession, $sID _WD_Option("Driver", "C:\Program Files (x86)\AutoIt3\chromedriver.exe") _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true }}}}' _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_Navigate($sSession, "https://www.daysoftheyear.com/?timezone_offset=nan") _WD_LoadWait($sSession) Sleep(3000) Local $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "/html/body/div[2]/header/div/div[1]/div/div[2]/h3/a") _WD_ElementAction($sSession, $sElement, 'text') ClipGet() Run("notepad.exe") Sleep(500) Send("^v") Scite returns this, therefore the text has been found but the last mile to paste it to notepad or excel does not work for me.
      __WD_Get: URL=HTTP://127.0.0.1:9515/session/b72166b774d7fefb258b3721fc1d4306/element/657e6b5d-31a9-46a8-85c4-acb7f72bf659/text __WD_Get: StatusCode=200; $iResult = 0; $sResponseText={"value":"Shark Awareness Day"}... _WD_ElementAction: {"value":"Shark Awareness Day"}... Thanks in advance! 
       
    • By pat4005
      A tiny UDF that can shoot magic packets at your computers to wake them the heck up. All credits to Olish.
      The only parameter it needs to be specified – is your machine's MAC-address (ip address (the second parameter), at which you will be sending magic packet is generating automatically from @IPAddress1 macro)
      Example:
      _WoL_WakeDevice('001CC0CAED7A') ; the second parameter (if necessery) must be a broadcast address of your local network segment (i.e. 192.168.0.255 for a network 192.168.0.0)  
      _WakeOnLan.au3
    • By GOSM
      Hey,
      as part of a larger Programm I am trying to copy the values from one cell in an Excel file to another. The code I am using is the folowing.
       
      Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_RangeCopyPaste($oExcel.ActiveSheet, "V4:W43", "X4", False, $xlPasteValues, Default, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "Error copying rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "2 Rows successfully pasted from the clipboard to row 1.") As I understood it the option $xlPasteValues should do the trick  but it is still copying the inserted formulas. I attached a test code and excel file.
      Thanks for the help Simon
      Copy Values Test.zip
    • By Chimp
      An "improper" "unusual" use of Excel
      the script allows the creation of artistic images by simply coloring the background of the individual cells of the Excel workbook.
      Although definitely useless ... I find it quite funny though
      have a good time
      many thanks to @UEZ , @Malkey , @water
      p.s.
      I think the pixelite + color to array process can be simplified, but I used the two ready-made functions provided by UEZ and Malkey. I thank both of you (credits  and links in listing)
      p.p.s.
      strange behaviour: while excell is filling cells, if you move the mouse pointer off the excell window, the fill speed increases ... (?)

       
      ; =============================================================================================================================== ; Name ..........: Excel in art ; Description ...: This script allows the creation of artistic images in Excel from a choosed picture. ; The picture is done by simply coloring the background of the individual cells of an Excel workbook. ; Although definitely useless, I find it quite funny though ; ; Return values .: An artistic image in an Excel workbook ; Author ........: Addiego Gianni (chimp) ; Modified ......: ; Remarks .......: Many thanks to UEZ, Malkey and Water ; Related .......: ; Link ..........: ; Example .......: ; =============================================================================================================================== #include <GDIPlus.au3> #include <Excel.au3> _PixelsToCells(50) ; <-- Parameter 50 is the cumber of horizontal Excel cells to be filled MsgBox(64, "All done", "Excel art is ready") Func _PixelsToCells($iHcells = 50) ; Check application object Local $oExcel = _Excel_Open() If Not IsObj($oExcel) Then MsgBox(16, "Error", "Sorry, You need to have 'Excel' intalled") ; Choose Image File Local $sPath = FileOpenDialog("Choose Image File", @ScriptDir & "", "Images (*.gif;*.png;*.jpg;*.bmp)| All (*.*)") If $sPath = '' Then Exit MsgBox(16, "Error", "Sorry, no image was chosen") ; Create a new Excel workbook $oWorkbook = _Excel_BookNew($oExcel, 1) ; initialize GDI+ _GDIPlus_Startup() Local $hBmp = _GDIPlus_BitmapCreateFromFile($sPath) Local $iWidth = _GDIPlus_ImageGetWidth($hBmp) ; get image width ; Local $iHeight = _GDIPlus_ImageGetHeight($hBmp) ; not needed here Local $iStep = $iWidth / $iHcells ; calculate the pixelation factor Local $hBitmap_new = _GDIPlus_PixelateBitmap($hBmp, $iStep) ; pixelate the image Local $aPixelColors = _FileImageToArray($hBitmap_new) ; get pixel colors ; reduce Excel columns width Local $xx = 1, $yy = 1 For $iCol = 0 To UBound($aPixelColors, 2) - 1 Step $iStep $oWorkbook.Sheets(1).Columns($xx).ColumnWidth = 1 $xx += 1 Next ; reduce Excel rows height For $iRow = 0 To UBound($aPixelColors) - 1 Step $iStep $oWorkbook.Sheets(1).Rows($yy).RowHeight = 9 $yy += 1 Next $xx = 1 $yy = 1 For $iRow = 0 To UBound($aPixelColors) - 1 Step $iStep For $iCol = 0 To UBound($aPixelColors, 2) - 1 Step $iStep $oWorkbook.Sheets(1).Range(_Excel_ColumnToLetter($xx) & $yy).Interior.Color = Number("0x" & $aPixelColors[$iRow][$iCol]) $xx += 1 Next $yy += 1 $xx = 1 Next _GDIPlus_BitmapDispose($hBmp) _GDIPlus_Shutdown() EndFunc ;==>_PixelsToCells ; by UEZ ; https://www.autoitscript.com/forum/topic/167707-imagepixelate/?do=findComment&comment=1227509 Func _GDIPlus_PixelateBitmap($hBitmap, $iPixelate, $bSmooth = 1) Local $iWidth = _GDIPlus_ImageGetWidth($hBitmap), $iHeight = _GDIPlus_ImageGetHeight($hBitmap) Local $iNewW = Round($iWidth / $iPixelate, 0), $iNewH = Round($iHeight / $iPixelate, 0) Local $hBitmap_scaled = _GDIPlus_BitmapCreateFromScan0($iNewW, $iNewH) Local $hCtxt = _GDIPlus_ImageGetGraphicsContext($hBitmap_scaled) Local $iInterpolation = 5 If $bSmooth Then $iInterpolation = $GDIP_INTERPOLATIONMODE_BILINEAR _GDIPlus_GraphicsSetInterpolationMode($hCtxt, $iInterpolation) _GDIPlus_GraphicsDrawImageRect($hCtxt, $hBitmap, 0, 0, $iNewW, $iNewH) _GDIPlus_GraphicsDispose($hCtxt) Local $hBitmap_pixelated = _GDIPlus_BitmapCreateFromScan0($iWidth, $iHeight) $hCtxt = _GDIPlus_ImageGetGraphicsContext($hBitmap_pixelated) _GDIPlus_GraphicsSetInterpolationMode($hCtxt, $GDIP_INTERPOLATIONMODE_NearestNeighbor) _GDIPlus_GraphicsDrawImageRectRect($hCtxt, $hBitmap_scaled, 0, 0, $iNewW, $iNewH, -$iPixelate, -$iPixelate, $iWidth + 2 * $iPixelate, $iHeight + 2 * $iPixelate) _GDIPlus_GraphicsDispose($hCtxt) Return $hBitmap_pixelated EndFunc ;==>_GDIPlus_PixelateBitmap ; by Malkey ; https://www.autoitscript.com/forum/topic/112540-is-there-a-function-for-reading-images-into-2d-arrays/?do=findComment&comment=788472 Func _FileImageToArray($hImage) Local $Reslt, $stride, $format, $Scan0, $iIW, $iIH ; , $hImage Local $v_Buffer, $width, $height ; _GDIPlus_Startup() ; $hImage = _GDIPlus_ImageLoadFromFile($sFileName) $iIW = _GDIPlus_ImageGetWidth($hImage) $iIH = _GDIPlus_ImageGetHeight($hImage) ProgressOn("Progress Bar", "Filling a " & $iIW & " x " & $iIH & " size array.", "0 percent") $Reslt = _GDIPlus_BitmapLockBits($hImage, 0, 0, $iIW, $iIH, $GDIP_ILMREAD, $GDIP_PXF32ARGB) ;Get the returned values of _GDIPlus_BitmapLockBits () $width = DllStructGetData($Reslt, "width") $height = DllStructGetData($Reslt, "height") $stride = DllStructGetData($Reslt, "stride") $format = DllStructGetData($Reslt, "format") $Scan0 = DllStructGetData($Reslt, "Scan0") Local $aArray[$height][$width] For $j = 0 To $iIH - 1 For $i = 0 To $iIW - 1 $v_Buffer = DllStructCreate("dword", $Scan0 + ($j * $stride) + ($i * 4)) $aArray[$j][$i] = StringRegExpReplace(Hex(DllStructGetData($v_Buffer, 1), 6), "(.{2})(.{2})(.{2})", "\3\2\1") ; To RGB format Next ProgressSet(Int(100 * $j / ($iIH)), Int(100 * $j / ($iIH)) & " percent") Next _GDIPlus_BitmapUnlockBits($hImage, $Reslt) ProgressOff() _GDIPlus_ImageDispose($hImage) Return $aArray EndFunc ;==>_FileImageToArray  
    • By MrCreatoR
      This UDF allows to create formatted label using pseudo element RichLabel (RichEdit actually). Formating is set by using special modificator similar to <font> tag in Html.
      Notes: This UDF is a transformation-continuation of related UDF


      Example:
      Download:
      GUIRichLabel_1.2.zip
      Small syntax related fix: GUIRichLabel_1.1.zip
      GUIRichLabel_1.1.zip
       
      History version:
×
×
  • Create New...