Excel Functions

Post your working scripts, libraries and tools for AHK v1.1 and older
Forum rules
Post Reply
User avatar
TheDewd
Posts: 1513
Joined: 19 Dec 2013, 11:16
Location: USA

Excel Functions

01 Nov 2016, 15:52

Microsoft Excel Functions
Here are some Microsoft Excel functions that I made. The list is limited at the moment, but I intend to continue adding many more and optimize those already existing.

All changes/additions will be listed in the changelog.

Code: Select all

Changelog
================================================================================
2016-12-12
* Added additional functions

2016-11-1
* Initial Release

Code: Select all

; ==============================================================================
; XlObjGet()                                                            [Handle]
; ==============================================================================
; Returns the running Excel object handle, or creates a new instance.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: Specifies the variable to use for the returned object handle.
; ==============================================================================
XlObjGet(ByRef Handle := "") {
    Try {
        Handle := ComObjActive("Excel.Application")
    } Catch {
        Handle := ComObjCreate("Excel.Application")
        Handle.Visible := 1
        Handle.Workbooks.Add
    }

    return Handle
}

; ==============================================================================
; XlActivateMSApp()                                                Handle, Index
; ==============================================================================
; Activates a Microsoft application if running, or creates a new instance.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Index: Specifies the Microsoft application to activate.
; (1) Microsoft Office Word
; (2) Microsoft Office PowerPoint
; (3) Microsoft Office Outlook
; (4) Microsoft Office Access
; (5) Microsoft FoxPro
; (6) Microsoft Office Project
; (7) Microsoft Schedule Plus
;
; Links
; ------------------------------------------------------------------------------
; Application.ActivateMicrosoftApp Method (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff840097.aspx
; ==============================================================================
XlActivateMSApp(Handle, Index) {
    return (Index >= 1 && Index <= 7 ? Handle.ActivateMicrosoftApp(Index))

}

; ==============================================================================
; XlAddCustomList()                                  Handle, ListArray [, ByRow]
; ==============================================================================
; Adds a custom list for custom autofill and/or custom sort.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; ListArray: An array of strings or a Range object.
;
; ByRow: Only used if ListArray is a Range object. True to create a custom list
; from each row in the range, or each column if false. If omitted, a custom list
; will be created from each column in the range if there are more rows than
; columns (or an equal number of rows and columns), or from each row if there
; are more columns than rows.
;
; Links
; ------------------------------------------------------------------------------
; Application.AddCustomList Method (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff196224.aspx
; ==============================================================================
XlAddCustomList(Handle, ListArray, ByRow := "") {
    Try return (ByRow <> "" ? Handle.AddCustomList(ListArray, ComObject(0xB, (ByRow ? -1 : 0))) : Handle.AddCustomList(ListArray))
}

; ==============================================================================
; XlCalculate()                            Handle [, Workbook, Worksheet, Range]
; ==============================================================================
; Calculates all open workbooks, a specific worksheet in a workbook, or a
; specified range of cells on a worksheet.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Links
; ------------------------------------------------------------------------------
; Application.Calculate Method (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff195517.aspx
; ==============================================================================
XlCalculate(Handle, Params*) {
    ; ObjExcel.Workbooks("Book1").Worksheets("Sheet1").Range("A1:D1").Calculate
    ; TBD
}

; ==============================================================================
; XlCalculateFull()                                                       Handle
; ==============================================================================
; Forces a full calculation of the data in all open workbooks.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Links
; ------------------------------------------------------------------------------
; Application.CalculateFull Method (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff194064.aspx
; ==============================================================================
XlCalcFull(Handle) {
    return Handle.CalculateFull
}

; ==============================================================================
; XlCalculateFull()                                                       Handle
; ==============================================================================
; Forces a full calculation and rebuilds dependencies for all open workbooks.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Links
; ------------------------------------------------------------------------------
; Application.CalculateFullRebuild Method (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff822609.aspx
; ==============================================================================
XlCalcFullRebuild(Handle) {
    return Handle.CalculateFullRebuild
}

; ==============================================================================
; XlCalcTilAsyncQueriesDone()                                             Handle
; ==============================================================================
; Runs all pending queries to OLEDB and OLAP data sources.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Links
; ------------------------------------------------------------------------------
; Application.CalculateUntilAsyncQueriesDone Method (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff821008.aspx
; ==============================================================================
XlCalcTilAsyncQueriesDone(Handle) {
    return Handle.CalculateUntilAsyncQueriesDone
}

; ==============================================================================
; XlCentimetersToPoints()                                          Handle, Value
; ==============================================================================
; Converts a measurement from centimeters to points (one point equals 0.035 CM).
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Value: Specifies the centimeter value to be converted to points.
;
; Links
; ------------------------------------------------------------------------------
; Application.CentimetersToPoints Method (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff195406.aspx
; ==============================================================================
XlCentimetersToPoints(Handle, Value) {
    return Handle.CentimetersToPoints(Value)
}

; ==============================================================================
; XlCheckAbort()                                            Handle [, KeepAbort]
; ==============================================================================
; Stops recalculation in a Microsoft Excel application.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; KeepAbort: Allows recalculation to be performed for a Range.
;
; Links
; ------------------------------------------------------------------------------
; Application.CheckAbort Method (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff840247.aspx
; ==============================================================================
XlCheckAbort(Handle, KeepAbort := "") {
    return (KeepAbort <> "" ? Handle.CheckAbort(KeepAbort) : Handle.CheckAbort)
}

; ==============================================================================
; XlCheckSpell()                        Handle, Word [, CustomDict, IgnoreUpper]
; ==============================================================================
; Returns True if the word is found in one of the dictionaries; otherwise False.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Word: The word you want to check.
;
; CustomDict: The filename of the custom dictionary to use if the word isn't
; found in the main dictionary. If omitted, the currently specified dictionary
; is used.
;
; IgnoreUpper: True/False. Specifies if words in all uppercase are ignored.
;
; Links
; ------------------------------------------------------------------------------
; Application.CheckSpelling Method (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff840059.aspx
; ==============================================================================
XlCheckSpell(Handle, Params*) {
    return Handle.CheckSpelling(Params*)
}

; ==============================================================================
; XlConvertFormula()            Handle, Formula, FromRef [, ToRef, ToAbs, RelTo]
; ==============================================================================
; Converts cell references in a formula between the A1 and R1C1 reference
; styles, between relative and absolute references, or both.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Formula: A valid formulate beginning with an equal sign.
;
; FromRef: The reference style of the formula.
; (1) Default. Returns an A1-style reference.
; (-4150) Returns an R1C1-style reference.
;
; ToRef: Specifies the reference style to return. If omitted, the reference
; style isn't changed; the formula stays in the style specified by FromRef.
; (See FromRef for accepted values)
;
; ToAbs: Specifies the converted reference type. If omitted, the reference type
; isn't changed.
; (See FromRef for accepted values)

; RelTo: A Range containing one cell that relative references will relate to.
;
; Links
; ------------------------------------------------------------------------------
; Application.ConvertFormula Method (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff822751.aspx
; ==============================================================================
XlConvertFormula(Handle, Formula, FromRef, Params*) {
    return Handle.ConvertFormula(Formula, FromRef, Params*)
}

; ==============================================================================
; XlDDEExec()                                            Handle, Channel, String
; ==============================================================================
; Runs a command or performs some other action or actions in another application
; by way of the specified DDE channel.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Channel: The channel number returned by the DDEInitiate method.
;
; String: The message defined in the receiving application.
;
; Links
; ------------------------------------------------------------------------------
; Application.DDEExecute Method (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff194507.aspx
; ==============================================================================
XlDDEExec(Handle, Channel, String) {
    return Handle.DDEExecute(Channel, String)
}

; ==============================================================================
; XlDDEInit()                                                 Handle, App, Topic
; ==============================================================================
; Opens a DDE channel to an application.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; App: The application name.
;
; Topic: Specifies what to open a channel to; Usually a document of that app.
;
; Links
; ------------------------------------------------------------------------------
; Application.DDEInitiate Method (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff197893.aspx
; ==============================================================================
XlDDEInit(Handle, App, Topic) {
    return Handle.DDEInitiate(App, Topic)
}

; ==============================================================================
; XlDDEPoke()                                        Handle, Channel, Item, Data
; ==============================================================================
; Sends data to an application.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Channel: The channel number returned by the DDEInitiate method.
;
; Item: The item to which the data is to be sent.
;
; Data: The data to be sent to the application.
;
; Links
; ------------------------------------------------------------------------------
; Application.DDEPoke Method (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff821378.aspx
; ==============================================================================
XlDDEPoke(Handle, Channel, Item, Data) {
    return Handle.DDEPoke(Channel, Item, Data)
}

; ==============================================================================
; XlDDEReq()                                               Handle, Channel, Item
; ==============================================================================
; Returns an array after requesting information from the specified application.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Channel: The channel number returned by the DDEInitiate method.
;
; Item: The item to be requested.
;
; Links
; ------------------------------------------------------------------------------
; Application.DDERequest Method (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff834935.aspx
; ==============================================================================
XlDDEReq(Handle, Channel, Item) {
    return Handle.DDERequest(Channel, Item)
}

; ==============================================================================
; XlDDETerm()                                                    Handle, Channel
; ==============================================================================
; Closes a channel to another application.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Channel: The channel number returned by the DDEInitiate method.
;
; Links
; ------------------------------------------------------------------------------
; Application.DDETerminate Method (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff840740.aspx
; ==============================================================================
XlDDETerm(Handle, Channel) {
    return Handle.DDETerminate(Channel)
}

; ==============================================================================
; XlDeleteCustomList()                                           Handle, ListNum
; ==============================================================================
; Deletes a custom list.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; ListNum: The number of the custom list, which must be greater than or equal to
; 5 (Microsoft Excel has four built-in custom lists that cannot be deleted).
;
; Links
; ------------------------------------------------------------------------------
; Application.DeleteCustomList Method (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff197264.aspx
; ==============================================================================
XlDeleteCustomList(Handle, ListNum) {
    return (ListNum >= 5 ? Handle.DeleteCustomList(ListNum))
}

; ==============================================================================
; XlActiveCell()                                     Handle [, Window, Contents]
; ==============================================================================
; Returns the address or contents of the active cell in the active or specified
; window.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Window: The window name of the desired Excel object handle.
;
; Content: True/False. Specify if the contents of the cell should be returned
; instead of the address.
;
; Links
; ------------------------------------------------------------------------------
; Application.ActiveCell Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff834673.aspx
; ==============================================================================
XlActiveCell(Handle, Window := "", Contents := 0) {
    return (Window ? Contents ? Handle.Windows(Window).ActiveCell.Value : Handle.Windows(Window).ActiveCell.Address : Contents ? Handle.ActiveCell.Value : Handle.ActiveCell.Address)
}

; ==============================================================================
; XlActivePrinter()                                             Handle [, Value]
; ==============================================================================
; Returns or sets the name of the active printer.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Value: The name of the desired printer.
;
; Links
; ------------------------------------------------------------------------------
; Application.ActivePrinter Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff822927.aspx
; ==============================================================================
XlActivePrinter(Handle, Value := "") {
	return (Value ? Handle.ActivePrinter := Value : Handle.ActivePrinter)
}

; ==============================================================================
; XlWbReadOnly()                                             Handle [, Workbook]
; ==============================================================================
; Returns True if the object has been opened as read-only.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Workbook: The workbook name of the desired Excel object handle.
;
; Links
; ------------------------------------------------------------------------------
; Workbook.ReadOnly Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff840925.aspx
; ==============================================================================
XlWbReadOnly(Handle, Workbook := "") {
    return (Workbook ? Handle.Workbooks(Workbook) : Handle.ActiveWorkbook).ReadOnly
}

; ==============================================================================
; XlWbAccuracyVer()                                   Handle [, Workbook, Index]
; ==============================================================================
; Returns or sets the value that specifies whether certain worksheet functions
; use the latest accuracy algorithms to calculate their results.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Workbook: The workbook name of the desired Excel object handle.
;
; Index:
; Specifies the accuracy algorithms used.
; 0 - Use the latest, most accurate algorithms (default)
; 1 - Use Excel 2007 or earlier version algorithms
; 2 - Use Excel 2010 algorithms
;
; Links
; ------------------------------------------------------------------------------
; Workbook.AccuracyVersion Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff838425.aspx
; ==============================================================================
XlWbAccuracyVer(Handle, Workbook := "", Value := "") {
    return (Workbook ? Value >= 0 && Value <= 2 ? Handle.Workbooks(Workbook).AccuracyVersion := Value : Handle.Workbooks(Workbook) : Value >= 0 && Value <= 2 ? Handle.ActiveWorkbook.AccuracyVersion := Value : Handle.ActiveWorkbook).AccuracyVersion
}

; ==============================================================================
; XlWbActiveSheet()                                   Handle [, Workbook, Value]
; ==============================================================================
; Returns or sets the value of the object for the active sheet in the active
; workbook. Returns nothing if there is no active sheet.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Workbook: The workbook name of the desired Excel object handle.
;
; Value: The name of a worksheet in an existing workbook.
;
; Links
; ------------------------------------------------------------------------------
; Workbook.ActiveSheet Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff841181.aspx
; ==============================================================================
XlWbActiveSheet(Handle, Workbook := "", Value := "") {
    return (Workbook ? Value ? Handle.Workbooks(Workbook).ActiveSheet.Name := Value : Handle.Workbooks(Workbook) : Value ? Handle.ActiveWorkbook.ActiveSheet.Name := Value : Handle.ActiveWorkbook).ActiveSheet.Name
}

; ==============================================================================
; XlWbActivate()                                                Handle, Workbook
; ==============================================================================
; Activates the first window associated with the specified workbook.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Workbook: The workbook name of the desired Excel object handle.
;
; Links
; ------------------------------------------------------------------------------
; Workbook.Activate Method (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff821837.aspx
; ==============================================================================
XlWbActivate(Handle, Workbook) {
    return Handle.Workbooks(Workbook).Activate
}

; ==============================================================================
; XlWbAcceptAllChanges()                   Handle [, Workbook, When, Who, Where]
; ==============================================================================
; Accepts all changes in the specified shared workbook.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Workbook: The workbook name of the desired Excel object handle.
;
; When:
; A string that specifies when all the changes are accepted. (Ex: "10/31/2016")
;
; Who:
; A string that specifies by whom all the changes are accepted. (Ex: "Everyone")
;
; Where:
; A string that specifies where all the changes are accepted. (Ex: "A1:Z1000")
;
; Links
; ------------------------------------------------------------------------------
; Workbook.AcceptAllChanges Method (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff835613.aspx
; ==============================================================================
XlWbAcceptAllChanges(Handle, Workbook := "", Params*) {
	return (Workbook ? Handle.Workbooks(Workbook) : Handle.ActiveWorkbook).AcceptAllChanges(Params*)
}

; ==============================================================================
; XlWbRejectAllChanges()                   Handle [, Workbook, Whem, Who, Where]
; ==============================================================================
; Rejects all changes in the specified shared workbook.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Workbook: The workbook name of the desired Excel object handle.
;
; When:
; A string that specifies when all the changes are rejected. (Ex: "10/31/2016")
;
; Who:
; A string that specifies by whom all the changes are rejected. (Ex: "Everyone")
;
; Where:
; A string that specifies where all the changes are rejected. (Ex: "A1:Z1000")
;
; Links
; ------------------------------------------------------------------------------
; Workbook.RejectAllChanges Method (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff837407.aspx
; ==============================================================================
XlWbRejectAllChanges(Handle, Workbook := "", Params*) {
	return (Workbook ? Handle.Workbooks(Workbook) : Handle.ActiveWorkbook).RejectAllChanges(Params*)
}

; ==============================================================================
; XlWbFullName()                                             Handle [, Workbook]
; ==============================================================================
; Returns the name of the workbook object, including its path on disk.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Workbook: The workbook name of the desired Excel object handle.
;
; Links
; ------------------------------------------------------------------------------
; Workbook.FullName Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff834975.aspx
; ==============================================================================
XlWbFullName(Handle, Workbook := "") {
    return (Workbook ? Handle.Workbooks(Workbook) : Handle.ActiveWorkbook).FullName
}

; ==============================================================================
; XlWbName()                                                 Handle [, Workbook]
; ==============================================================================
; Returns the name of the workbook object.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Workbook: The workbook name of the desired Excel object handle.
;
; Links
; ------------------------------------------------------------------------------
; Workbook.Name Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff820899.aspx
; ==============================================================================
XlWbName(Handle, Workbook := "") {
    return (Workbook ? Handle.Workbooks(Workbook) : Handle.ActiveWorkbook).Name
}

; ==============================================================================
; XlWbPath()                                                 Handle [, Workbook]
; ==============================================================================
; Returns the complete path to the workbook/file.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Workbook: The workbook name of the desired Excel object handle.
;
; Links
; ------------------------------------------------------------------------------
; Workbook.Path Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff840974.aspx
; ==============================================================================
XlWbPath(Handle, Workbook := "") {
    return (Workbook ? Handle.Workbooks(Workbook) : Handle.ActiveWorkbook).Path
}

; ==============================================================================
; XlWinActivate()                                                 Handle, Window
; ==============================================================================
; Brings the window to the front of the z-order.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Window: The window name of the desired Excel object handle.
;
; Links
; ------------------------------------------------------------------------------
; Window.Activate Method (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff834651.aspx
; ==============================================================================
XlWinActivate(Handle, Window) {
    return Handle.Windows(Window).Activate
}

; ==============================================================================
; XlWinActivateNext()                                          Handle [, Window]
; ==============================================================================
; Activates the specified window and then sends it to the back of the window
; z-order.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Window: The window name of the desired Excel object handle.
;
; Links
; ------------------------------------------------------------------------------
; Window.ActivateNext Method (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff840688.aspx
; ==============================================================================
XlWinActivateNext(Handle, Window := "") {
    return (Window ? Handle.Windows(Window) : Handle.ActiveWindow).ActivateNext
}

; ==============================================================================
; XlWinActivatePrev()                                          Handle [, Window]
; ==============================================================================
; Activates the specified window and then activates the window at the back of
; the window z-order.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Window: The window name of the desired Excel object handle.
;
; Links
; ------------------------------------------------------------------------------
; Window.ActivatePrevious Method (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff821557.aspx
; ==============================================================================
XlWinActivatePrev(Handle, Window := "") {
    return (Window ? Handle.Windows(Window) : Handle.ActiveWindow).ActivatePrevious
}

; ==============================================================================
; XlWinState()                                          Handle [, Window, Value]
; ==============================================================================
; Returns or sets the state of the window. Read/write XlWindowState.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Window: The window name of the desired Excel object handle.
;
; Value:
; Specifies the value to set for the WindowState property of the window object.
; -4137 - Maximized
; -4140 - Minimized
; -4143 - Normal
;
; Links
; ------------------------------------------------------------------------------
; Window.WindowState Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff838577.aspx
; ==============================================================================
XlWinState(Handle, Window := "", Value := "") {
    return (Window ? Value ? Handle.Windows(Window).WindowState := Value : Handle.Windows(Window).WindowState : Value ? Handle.ActiveWindow.WindowState := Value : Handle.ActiveWindow.WindowState)
}

; ==============================================================================
; XlQuit()                                                                Handle
; ==============================================================================
; Quits Microsoft Excel.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Links
; ------------------------------------------------------------------------------
; Application.Quit Method (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff839269.aspx
; ==============================================================================
XlQuit(Handle) {
    return Handle.Quit
}

; ==============================================================================
; XlWbClose()          Handle [, Workbook, SaveChanges, Filename, RouteWorkbook]
; ==============================================================================
; Closes the workbook object.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Workbook: The workbook name of the desired Excel object handle.
;
; SaveChanges:
; True/False. Specifies whether changes to the workbook should be saved.
;
; Filename: Save changes under this file name.
;
; RouteWorkbook:
; True/False. Specifies if the workbook needs to be sent to the next recipient.
;
; Links
; ------------------------------------------------------------------------------
; Workbook.Close Method (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff838613.aspx
; ==============================================================================
XlWbClose(Handle, Workbook := "", Params*) {
    return (Workbook ? Handle.Workbooks(Workbook) : Handle.ActiveWorkbook).Close(Params*)
}

; ==============================================================================
; XlWbSave()                                                 Handle [, Workbook]
; ==============================================================================
; Saves changes to the specified workbook.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Workbook: The workbook name of the desired Excel object handle.
;
; Links
; ------------------------------------------------------------------------------
; Workbook.Save Method (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff197585.aspx
; ==============================================================================
XlWbSave(Handle, Workbook := "") {
    return (Workbook ? Handle.Workbooks(Workbook) : Handle.ActiveWorkbook).Save
}

; ==============================================================================
; XlWorksheets()                                      Handle [, Workbook, Count]
; ==============================================================================
; Returns a concatenated string of names for the Worksheets of the active or
; specified workbook, or the total number of worksheets.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Workbook: The workbook name of the desired Excel object handle.
;
; Count:
; True/False. Specifies if the number of worksheets should be returned instead.
;
; Links
; ------------------------------------------------------------------------------
; Application.Worksheets Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff840672.aspx
; ==============================================================================
XlWorksheets(Handle, Workbook := "", Count := 0) {
    Local Str := "", Obj := ""
    Str := (Workbook && Count ? Handle.Workbooks(Workbook) : !Workbook && Count ? Handle.ActiveWorkbook).Worksheets.Count
    Obj := (Workbook && !Count ? Handle.Workbooks(Workbook) : !Workbook && !Count ? Handle.ActiveWorkbook)

    If (Obj) {
       For Key, Value In Obj.Worksheets {
           Str .= Obj.Worksheets(A_Index).Name "`n"
       }
    }

    return Str
}

; ==============================================================================
; XlVisible()                                           Handle [, Window, Value]
; ==============================================================================
; Returns or sets the value that determines whether the active or specified
; window is visible.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Window: The window name of the desired Excel object handle.
;
; Value:
; Specifies the value to set for the Visible property of the window object.
;
; Links
; ------------------------------------------------------------------------------
; Application.Visible Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff198119.aspx
; ==============================================================================
XlVisible(Handle, Window := "", Value := 1) {
    return (Window ? Value ? Handle.Windows(Window).Visible := Value : Handle.Windows(Window).Visible : Value ? Handle.ActiveWindow.Visible := Value : Handle.ActiveWindow.Visible := Value)
}

; ==============================================================================
; XlVer()                                                                 Handle
; ==============================================================================
; Returns the Microsoft Excel version number.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Links
; ------------------------------------------------------------------------------
; Application.Version Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff193301.aspx
; ==============================================================================
XlVer(Handle) {
    return Handle.Version
}

; ==============================================================================
; XlOperatingSystem()                                                     Handle
; ==============================================================================
; Returns the name and version number of the current operating system.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Links
; ------------------------------------------------------------------------------
; Application.OperatingSystem Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff837365.aspx
; ==============================================================================
XlOperatingSystem(Handle) {
    return Handle.OperatingSystem
}

; ==============================================================================
; XlUserName()                                                  Handle [, Value]
; ==============================================================================
; Returns or sets the name of the current user.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Links
; ------------------------------------------------------------------------------
; Application.UserName Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff822584.aspx
; ==============================================================================
XlUserName(Handle, Value := "") {
    return (Value ? Handle.UserName := Value : Handle.UserName)
}

; ==============================================================================
; XlValue()                                                               Handle
; ==============================================================================
; Returns the name of the application.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Links
; ------------------------------------------------------------------------------
; Application.Value Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff195664.aspx
; ==============================================================================
XlValue(Handle) {
    return Handle.Value
}

; ==============================================================================
; XlUseSysSeparators()                                          Handle [, Value]
; ==============================================================================
; Returns or sets the current state of the UseSystemSeparators property.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Value:
; True/False. Specifies if the system separators of Microsoft Excel are enabled.
;
; Links
; ------------------------------------------------------------------------------
; Application.UseSystemSeparators Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff840692.aspx
; ==============================================================================
XlUseSysSeparators(Handle, Value := "") {
    return (Value != "" ? Handle.UseSystemSeparators := Value : Handle.UseSystemSeparators)
}

; ==============================================================================
; XlDecimalSeparator()                                          Handle [, Value]
; ==============================================================================
; Returns or sets the character used for the decimal separator.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Value:
; Specifies the character to be used as the decimal separator.
;
; Links
; ------------------------------------------------------------------------------
; Application.DecimalSeparator Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff195207.aspx
; ==============================================================================
XlDecimalSeparator(Handle, Value := "") {
    return (Value != "" ? Handle.DecimalSeparator := Value : Handle.DecimalSeparator)
}

; ==============================================================================
; XlThousandsSeparator()                                        Handle [, Value]
; ==============================================================================
; Returns or sets the character used for the thousands separator.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Value:
; Specifies the character to be used as the thousands separator.
;
; Links
; ------------------------------------------------------------------------------
; Application.ThousandsSeparator Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff839793.aspx
; ==============================================================================
XlThousandsSeparator(Handle, Value := "") {
    return (Value != "" ? Handle.ThousandsSeparator := Value : Handle.ThousandsSeparator)
}

; ==============================================================================
; XlUsableWidth()                                                         Handle
; ==============================================================================
; Returns the maximum width of the space that a window can occupy in the
; application window area, in points. Read-only Double.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Links
; ------------------------------------------------------------------------------
; Application.UsableWidth Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff838216.aspx
; ==============================================================================
XlUsableWidth(Handle) {
    return Handle.UsableWidth
}

; ==============================================================================
; XlUsableHeight()                                                        Handle
; ==============================================================================
; Returns the maximum height of the space that a window can occupy in the
; application window area, in points. Read-only Double.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Links
; ------------------------------------------------------------------------------
; Application.UsableHeight Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff820823.aspx
; ==============================================================================
XlUsableHeight(Handle) {
    return Handle.UsableHeight
}

; ==============================================================================
; XlWidth()                                                     Handle [, Value]
; ==============================================================================
; Returns or sets a Double value for the distance, in points, from the left edge
; of the application window to its right edge.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Value:
; A Double value for the distance, in points, from the left edge of the
; application window to its right edge.
;
; Links
; ------------------------------------------------------------------------------
; Application.Width Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff840678.aspx
; ==============================================================================
XlWidth(Handle, Value := "") {
    return (Value != "" ? Handle.Width := Value : Handle.Width)
}

; ==============================================================================
; XlHeight()                                                    Handle [, Value]
; ==============================================================================
; Returns or sets a Double value for the height, in points, of the main
; application window.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Value:
; A Double value for the height, in points, of the main application window.
;
; Links
; ------------------------------------------------------------------------------
; Application.Height Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff195524.aspx
; ==============================================================================
XlHeight(Handle, Value := "") {
    return (Value != "" ? Handle.Height := Value : Handle.Height)
}

; ==============================================================================
; XlLeft()                                                      Handle [, Value]
; ==============================================================================
; Returns or sets a Double value for the distance, in points, from the left edge
; of the screen to the left edge of the main Microsoft Excel window.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Value:
; A Double value for the distance, in points, from the left edge of the screen
; to the left edge of the main Microsoft Excel window.
;
; Links
; ------------------------------------------------------------------------------
; Application.Left Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff834367.aspx
; ==============================================================================
XlLeft(Handle, Value := "") {
    return (Value != "" ? Handle.Left := Value : Handle.Left)
}

; ==============================================================================
; XlDisplayStatusBar()                                          Handle [, Value]
; ==============================================================================
; Returns or sets the visibility of the status bar.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Value:
; True/False. Specifies the value to determine the visibility of the status bar.
;
; Links
; ------------------------------------------------------------------------------
; Application.DisplayStatusBar Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff838599.aspx
; ==============================================================================
XlDisplayStatusBar(Handle, Value := "") {
    return (Value != "" ? Handle.DisplayStatusBar := Value : Handle.DisplayStatusBar)
}

; ==============================================================================
; XlStatusBar()                                                 Handle [, Value]
; ==============================================================================
; Returns or sets the text string in the status bar.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Value:
; Specifies the string value to be displayed in the status bar. To return to
; normal, simply pass empty quotes to this parameter.
;
; Links
; ------------------------------------------------------------------------------
; Application.StatusBar Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff835916.aspx
; ==============================================================================
XlStatusBar(Handle, Value := "") {
    return (Value || Value = "" ? Handle.StatusBar := Value : Handle.StatusBar)
}

; ==============================================================================
; XlStartupPath()                                                         Handle
; ==============================================================================
; Returns the complete path of the startup folder as a string, excluding the
; final separator.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Links
; ------------------------------------------------------------------------------
; Application.StartupPath Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff193231.aspx
; ==============================================================================
XlStartupPath(Handle) {
    return Handle.StartupPath
}

; ==============================================================================
; XlStdFont()                                                   Handle [, Value]
; ==============================================================================
; Returns or sets the name of the standard font. Changes to this property will
; not take effect until Microsoft Excel has been restarted.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Value:
; The name of the font to be used.
;
; Links
; ------------------------------------------------------------------------------
; Application.StandardFont Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff822527.aspx
; ==============================================================================
XlStdFont(Handle, Value := "") {
    return (Value != "" ? Handle.StandardFont := Value : Handle.StandardFont)
}

; ==============================================================================
; XlStdFontSize()                                               Handle [, Value]
; ==============================================================================
; Returns or sets the standard font size, in points. Changes to this property
; will not take effect until Microsoft Excel has been restarted.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Value:
; Size, in points, of the desired standard font size.
;
; Links
; ------------------------------------------------------------------------------
; Application.StandardFontSize Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff196551.aspx
; ==============================================================================
XlStdFontSize(Handle, Value := "") {
    return (Value != "" ? Handle.StandardFontSize := Value : Handle.StandardFontSize)
}

; ==============================================================================
; XlBuild()                                                               Handle
; ==============================================================================
; Returns the Microsoft Excel build number.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Links
; ------------------------------------------------------------------------------
; Application.Build Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff839811.aspx
; ==============================================================================
XlBuild(Handle) {
    return Handle.Build
}

; ==============================================================================
; XlCaption()                                                             Handle
; ==============================================================================
; Returns or sets the name that appears in the title bar of the main window.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Links
; ------------------------------------------------------------------------------
; Application.Caption Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff821801.aspx
; ==============================================================================
XlCaption(Handle, Value := "") {
    return (Value != "") ? (Handle.Caption := Value) : (Handle.Caption)
}

; ==============================================================================
; XlCaption()                                                   Handle [, Value]
; ==============================================================================
; Returns or sets the appearance of the mouse pointer in Microsoft Excel.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Value:
; Specifies the appearance of the mouse pointer in Excel.
; -4143 - The default pointer.
; 3 - The I-beam pointer.
; 1 - The northwest-arrow pointer.
; 2 - The hourglass pointer.
;
; Links
; ------------------------------------------------------------------------------
; Application.Cursor Property (Excel)
; https://msdn.microsoft.com/en-us/library/office/ff198335.aspx
; ==============================================================================
XlCursor(Handle, Value := "") {
    return (Value != "" ? Handle.Cursor := Value : Handle.Cursor)
}

; ==============================================================================
; XlDispFullScreen()                                            Handle [, Value]
; ==============================================================================
; Returns or sets the value that indicates if Excel is in full-screen mode.
;
; Parameters
; ------------------------------------------------------------------------------
; Handle: A Microsoft Excel object handle.
;
; Value:
; True/False. Specifies the Microsoft Excel full-screen mode option.
; ==============================================================================
XlDisplayFullScreen(Handle, Value := "") {
    return (Value != "" ? Handle.DisplayFullScreen := Value : Handle.DisplayFullScreen)
}
Last edited by TheDewd on 12 Dec 2016, 10:48, edited 2 times in total.
Top
sv270190
Posts: 45
Joined: 06 Feb 2014, 11:48

Re: Excel Functions

11 Dec 2016, 07:45

dear sir please clarify
I could get the value of range assigned to a variable.
I also could assign the same value to another range.
but when it comes to fileappend, %myvar%, c:\download\good.txt
it fails why

myxl := XlObjGet()

;msgbox, % XlActiveCell(myxl,,true) ; Handle [, Window, Contents] ; works fine


FileDelete, c:\download\good.txt

myxl.worksheets("ravana").range("a1:a50") := "kpatti"

myvar := myxl.worksheets("ravana").range("a1").currentregion.value

myxl.worksheets("ram").range("a1") := myvar ; this is also not upto my expectation it assigns only the first value not all the value


FileAppend, %myvar%, c:\download\good.txt ; i could not load the variable to this file why what and where it is wrong

Run, c:\download\good.txt
S.V. SRINIVASAN
SRIVILLIPUTTUR
TAMIL NADU
Top
User avatar
TheDewd
Posts: 1513
Joined: 19 Dec 2013, 11:16
Location: USA

Re: Excel Functions

12 Dec 2016, 10:35

Code: Select all

#SingleInstance, Force

MyXL := XlObjGet()

FileDelete, C:\download\good.txt

MyXL.Worksheets("ravana").Range("A1:A50") := "kpatti"

MyVar := MyXL.Worksheets("ravana").Range("A1").CurrentRegion

For Index In MyVar {
    MyXL.Worksheets("ram").Range("A1").Value := Index.Value
    FileAppend, % Index.Value, C:\download\good.txt
    IfEqual, A_Index, 1, Break
}

Run, C:\download\good.txt

/*
For Index In MyVar {
   MyXL.Worksheets("ram").Range("A" A_Index) := Index.Value
}
*/

XlObjGet(ByRef Handle := "") {
    Try {
        Handle := ComObjActive("Excel.Application")
    } Catch {
        Handle := ComObjCreate("Excel.Application")
        Handle.Visible := 1
        Handle.Workbooks.Add
    }

    return Handle
}

XlActiveCell(Handle, Window := "", Contents := 0) {
    return (Window ? Contents ? Handle.Windows(Window).ActiveCell.Value : Handle.Windows(Window).ActiveCell.Address : Contents ? Handle.ActiveCell.Value : Handle.ActiveCell.Address)
}
Top
hisrRB57
Posts: 69
Joined: 13 Jan 2019, 11:43

Re: Excel Functions

13 Mar 2024, 07:43

@TheDewd:
I was testing some functions for AHK v2 and ran into an error with XlWbActiveSheet():
  • When I call the function with an excel application object as the first parameter it runs ok and returns the name of the Active sheet.
  • When I call the function with an excel application object as the first parameter and a workbookname as the 2nd parameter it returns the name of the active sheet in that workbook
  • But when I call it with 3 parameters (the 3rd is the name of a sheet) it gives the following error when the sheet exists: ==> (0x800A03EC) Name already exists. Please use another name.
  • And if the sheet does not exists it also errors with: This value of type "String" has no property named "ActiveSheet".
Of course I may have misinterpretated the guideline, but I think that the function is not correctly coded.

Here is an update that returns the Active worksheet of a workbook or the active workbook or
sets the worksheetname of a workbook or the active workbook when the new name does not exist.
When the sheetname already exists then it activates that sheet in a workbook or the activeworkbook:

Code: Select all

XlWbActiveSheet2(Handle, Workbook := "", Value := "") {
	iValueExists := false
	if (workbook) {
		try oWbk := Handle.Workbooks(Workbook)
	} else {
		try oWbk := Handle.ActiveWorkbook
	}
	if (value) {
		for oSheet in oWBK.sheets
		{
			if (oSheet.name = value) {
				oSheet.Activate
				iValueExists := true
				break
			}
		}
		if !(iValueExists) {
			try oWbk.ActiveSheet.Name := Value
		}
	}
	if (oWbk) {
		return oWbk.ActiveSheet.Name
	} else {
		return ""
	}
}
Feel free to comment and use it as an improvement if you agree.
It should work with both Autohotkey v1 and v2
Top
Post Reply

Return to “Scripts and Functions (v1)”

Who is online

Users browsing this forum: gwarble and 46 guests