Jump to content
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 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
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 (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - 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 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
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 (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - 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 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
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 (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - 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 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
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 (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - 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 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
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 (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - 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 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
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 (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - 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 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
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 (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - 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 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
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 (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - 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 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
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 (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - 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 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - Wiki
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 (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - 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

  • Similar Content

    • By Stormgrade
      Hello,
      I'm searching the UDF GDIpProgress.au3 from ProgAndy
      None of the links in topic
       works
      Can't someone upload it please ?
    • By Taxyo
      Hi,
       
      I've been trying to automate modification of an excel file and the last thing I am stuck on is deleting all the rows where the value of Column 13 is 0. 
      I believe the error is due to me not fully understanding the syntax so this is where I'm stuck: 
       
      Func Hotkey2() Global $aUsedRange = _Excel_RangeRead($oWorkbook, 1) _ArrayDisplay($aUsedRange) For $iRow = UBound($aUsedRange) - 1 to 3 Step -1 If $aUsedRange[$iRow][13] = 0 Then _Excel_RangeDelete($oWorkbook.Worksheets(1), $aUsedRange[$iRow] & ":" & $aUsedRange[$iRow], default, 1) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeDelete Example 2", "Error deleting rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended) EndIf Next EndFunc  
      While my script properly locates the row which contains value 0 in Column 13, I am not sure how to set it to the corresponding row in the excel workbook?  My above experiment gives me $vRange error and I've been toying around with it to no avail. The only way I get the Script to delete a row is by actually specifying "4:4" or "6:8" etc. 
      Where am I going wrong?
       
      Thanks! 
    • By Most
      #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> ; Create application object and open an example workbook Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\trans.xlsx") If @error Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\trans.xlsx'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oExcel) Exit EndIf ; ***************************************************************************** ; Read data from a single cell on the active sheet of the specified workbook ; ***************************************************************************** Local $sResult = _Excel_RangeRead($oWorkbook, Default, "A1") If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Data successfully read." & @CRLF & "Value of cell A1: " & $sResult) Hi, all.
      Ok, here is the deal. I have simple excel file called trans.xlsx. It's located in the directory of script. In general i don't care where to store it. 
      What i do need is to open excel file and copy one by one numbers from cells. I've tried different ways, examples. But i only get error, says: error = 3, extended = 1. I saw different posts from different years. I even tried to use simple example from manual file. But always get error.

      In general my goal get numbers one by one and post it to let's say search filed in my PC one by one. Or to notepad (but one by one, in kind of loop). 
      I've learned how to copy or show in message box some info from other apps. But with excel i'm stuck. 

      I'm able to open needed window based on "title" of excel. But i don't succeed of copying info from cells. 

      Would be appreciate for any help. 
      So, in this code i'm trying at least to read from cell A1. Doesn't matter what Sheet. 

      I use Windows 10, Excel for Office 365. 
      Thank you in advance. 
    • By VinMe
      Dear all, i am unable to open a xml file to excel in the "xml table format" Please help me out in where i am missing
      Local $strFileToOpen = _WinAPI_OpenFileDlg('Select xml file', @WorkingDir, 'All Files(*.*)', 1, '', '', BitOR($OFN_PATHMUSTEXIST, $OFN_FILEMUSTEXIST, $OFN_HIDEREADONLY)) Global $xlXmlLoadImportToList = 2 ; Places the contents of the XML data file in an XML table $oExcel = _Excel_Open() $oWorkbook1=$oExcel.Workbooks.OpenXML($strFileToOpen, "", $xlXmlLoadImportToList) If $strFileToOpen <> False Then     Local $oWorkbook1 = _Excel_BookOpen($oExcel, $strFileToOpen) EndIf Error i am getting is:
      ......\81e_Compare_v1.au3" (46) : ==> The requested action with this object has failed.:
      $oWorkbook1=$oExcel.Workbooks.OpenXML($strFileToOpen, "", $xlXmlLoadImportToList)
      $oWorkbook1=$oExcel.Workbooks^ ERROR
      >Exit code: 1    Time: 7.338
    • By VinMe
      Dear all, 
      I am unable to get the right result after applying the filter to the excel. please let me know on the same.
      issue: After applying the filter the output $lastRow11 not giving the right output of complete visible rows. (its breaking at row skips)
       
      ;DATA EXTRACTION FROM LOC EXCEL
      ;=============================================================================
      $oWorkbook = _Excel_BookAttach($sWorkbook)
      Local $sMSN = InputBox("MSN NO", "Enter MSN in XX FORMAT", "")
      ;~ Local $LastRow1 = ($oWorkbook.ACTIVESHEET.Range("A1").SpecialCells($xlCellTypeLastCell).Row)
      $LastRow1 = $oWorkbook.ActiveSheet.UsedRange.Rows.Count
      MsgBox(0, "lastrow1", $LastRow1)
      _Excel_FilterSet($oWorkbook, $oWorkbook.activesheet, "AF1", 32, "*" & $sMSN & "*")
      Local $oLocDS = $oWorkbook.ActiveSheet.Range("S1:S" & $LastRow1).SpecialCells($xlCellTypeVisible)
      Local $LastRow11 = $oLocDS.rows.count    ;error output
      MsgBox(0, "lastrow11", $LastRow11)
      Local $aLocDS1 = _Excel_RangeRead($oWorkbook, Default, $oLocDS)
      Local $oLocNr = $oWorkbook.ActiveSheet.Range("A1:A" & $LastRow1).SpecialCells($xlCellTypeVisible)
      Local $aLocNr1 = _Excel_RangeRead($oWorkbook, Default, $oLocNr)
      _ArrayDisplay($aLocDS1)
      _ArrayDisplay($aLocNr1)
      _ArrayTrim($aLocDS1, 6, 1)
      _ArrayTrim($aLocNr1, 6, 1)
      _ArrayTrim($aLocNr1, 6, 0)
      _ArrayDisplay($aLocDS1)
      _ArrayDisplay($aLocNr1)
×
×
  • Create New...