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)
}