Jump to content

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 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 (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites
  • Replies 538
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

This UDF is now part of AutoIt since 3.3.12.0. New versions of Microsoft Office have been released since the last changes were made to the Excel UDF. The new extensions (e.g. xlsx) are not (fully) s

I have change a bit here and there in the Excel UDF myself. Have a few requests.. Additional possibilities in the font parameters. Right now can only change bold, italic, underline. Perhaps something

Just tested it, and it works fine for me. WinRAR = the best.

Posted Images

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 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.
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 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 (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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 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 (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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

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 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 (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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 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 (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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?

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 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 (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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
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
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 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 (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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 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 (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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 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 (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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!).

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

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.

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 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 (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

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

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By goku200
      I have an Autoit script that lists files from a folder into an array list. Is there a way to separate the filenames by an underscore and include the id, version, name and date into separate columns in Excel.
      Example of filename:
      12345_v1.0_TEST Name [12345]_01.01.2022.html
      12345 would be in one column
      v1.0 would be in another column
      TEST Name [12345] would be in another column
      01.01.2022 would be in another column
      .html would be in another column
      Note: filenames always change each day.
      Here is my code that lists the files into column C and then writes the column Headers into Column D, E, F, G. Just need some help with separating them into columns by the _ delimiter
       
    • By SkysLastChance
      I am having a issue of whenever I try to _Excel_RangeWrite a formula that references another workbook I am getting an error @4 and @extended -2147352567

      #include <Excel.au3> #include <MsgBoxConstants.au3> Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookNew($oExcel) _Excel_RangeWrite($oWorkBook,Default,"=IF(D2=D1,"",VLOOKUP(D2,'J:\Temporary Files\FolderName\FileName.xlsx'!$A:$B,2,0))","W2",False) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "String successfully written.") I am not a 100% sure, but I am guessing this is because I have "" and '' in the formula. However, I am not sure how this can best be resolved. 
       
       
    • By SkysLastChance
      I am trying to autofill a range. I am getting stuck and I don't understand what I am doing wrong. 
      My goal is to auto fill some formulas that are next to a pivot table in columns A-C. 
      _Excel_RangeWrite($oNewWorkBook,Default,"2000","D3") _Excel_RangeWrite($oNewWorkBook,Default,"=(B3-D3)","E3") _Excel_RangeWrite($oNewWorkBook,Default,"100","F3") _Excel_RangeWrite($oNewWorkBook,Default,"=(C3-F3)","G3") $oNewWorkbook.ActiveSheet.Range("D3:G3").Select With $oNewWorkbook .Selection.AutoFill(.Range("D3:G77"),0) EndWith The data is not auto filling. 
      Hoping someone can point me in the right direction. 
    • By Hermes
      Hi, I am struggling in setting the value of a textarea based on the value of clipboard (that contains a long web page source codes). If I use _WD_SetElementValue, it freezes after some time, or appears to be pressing tab and goes out of focus. I can also use send keys but i need the script to run in the background.
      Here is the full script:
      #Include "Chrome.au3" #Include "wd_core.au3" #Include "wd_helper.au3" #Include "WinHttp.au3" #include <MsgBoxConstants.au3> #include <WinAPIFiles.au3> #include <Array.au3> #include <AutoItConstants.au3> #include <WinAPIFiles.au3> #include <GDIPlus.au3> #include <Excel.au3> Local $sDesiredCapabilities, $sSession SetupChrome() _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_LoadWait($sSession) _WD_Navigate($sSession, "http://demo.borland.com/testsite/stadyn_largepagewithimages.html") _WD_LoadWait($sSession) Global $sSource = _WD_GetSource($sSession) Local $Paste = ClipPut($sSource) Local $sData = ClipGet() Local $aArray = 0, _ $iOffset = 1 While 1 $aArray = StringRegExp($sData, '(?s)<p>.*</p>', $STR_REGEXPARRAYMATCH, $iOffset) If @error Then ExitLoop $iOffset = @extended For $i = 0 To UBound($aArray) - 1 Local $Paste = ClipPut($aArray[$i]) Local $sRegExData = ClipGet() ;MsgBox(0, "", "$sRegExData = " & $sRegExData) Next WEnd _WD_Navigate($sSession, "https://www.w3schools.com/tags/tryit.asp?filename=tryhtml5_textarea_placeholder") _WD_WaitElement($sSession, $_WD_LOCATOR_ByCSSSelector, "iframe#iframeResult") Local $sElement1 = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "iframe#iframeResult") _WD_FrameEnter($sSession, $sElement1) _WD_WaitElement($sSession, $_WD_LOCATOR_ByXPath, "//html/body/textarea") $textarea = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//html/body/textarea") _WD_ElementAction($sSession, $textarea, 'click') ;WD SetElementValue(SsSession, Stextarea, $sRegExData) <-- I can do this but the focus goes out, or the browser freezes _WD_FrameLeave($sSession) sleep(2000) Send("^v") _WD_LoadWait($sSession) _WD_Shutdown() Func SetupChrome() _WD_Option('Driver', 'chromedriver.exe') _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true, "args":["start-maximized","disable-infobars"]}}}}' EndFunc ;==>SetupChrome Can someone help me please, or re-direct me to the right path? TIA!
    • By Rskm
      Hi, I have 5 notepad files with lot of data in each of it. The data are arranged in lines and i wish to get it pasted/copied into excel.  Say, i need to read notepad1 and paste the contents into sheet1 of excel and notepad2 to sheet2 and so on.  If i read the notepad and paste it line by line, it is taking lot of time.  Is there a way by which i can paste the whole of notepad file into excel sheet(and get it pasted line by line as shown in the attached excel), instead of using code to write it line by line?.. I was using Filewriteline(data, line i) initially to write to excel.  The 'i' value was incremented with for loop and the excel was updated, but this takes lot of time.  The expected excel format is attached here.  any help is appreciated.  thanks
      Tmp.xls
×
×
  • Create New...