Excel Functions

Post your working scripts, libraries and tools
User avatar
TheDewd
Posts: 1390
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.
Image Bulldozer - Sokoban inspired game from 1994 recreated in AutoHotkey.
sv270190
Posts: 45
Joined: 06 Feb 2014, 11:48
Facebook: [email protected]

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
User avatar
TheDewd
Posts: 1390
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)
}
Image Bulldozer - Sokoban inspired game from 1994 recreated in AutoHotkey.

Return to “Scripts and Functions”

Who is online

Users browsing this forum: boiler, WatsonEnterprises and 60 guests