Add Method

Adds a new add-in file to the list of add-ins. Returns an AddIn object.

expression.Add(FileName, CopyFile)

expression    Required. An expression that returns an AddIns object.

Filename    Required String. The name of the file that contains the add-in you want to add to the list in the add-in manager.

CopyFile    Optional Variant. Ignored if the add-in file is on a hard disk. True to copy the add-in to your hard disk, if the add-in is on a removable medium (a floppy disk or compact disc). False to have the add-in remain on the removable medium. If this argument is omitted, Microsoft Excel displays a dialog box and asks you to choose.

Remarks

This method doesn't install the new add-in. You must set the Installed property to install the add-in.

Adds a range that can be edited on a protected worksheet. Returns a AllowEditRange object.

expression.Add(Title, Range, Password)

expression    Required. An expression that returns an AllowEditRanges object.

Title   Required String. The title of range.

Range   Required Range object. The range allowed to be edited.

Password   Optional Variant. The password for the range.

Creates a new calculated field. Returns a PivotField object.

expression.Add(Name, Formula, UseStandardFormula)

expression    Required. An expression that returns a CalculatedFields object.

Name   Required String. The name of the field.

Formula   Required String. The formula for the field.

UseStandardFormula   Optional Variant. False (default) for upward compatibility. True for strings contained in any arguments that are field names, will be interpreted as having been formatted in standard U.S. English instead of local settings.

Creates a new calculated item. Returns a PivotItem object.

expression.Add(Name, Formula, UseStandardFormula)

expression    Required. An expression that returns a CalculatedItems object.

Name   Required String. The name of the item.

Formula   Required String. The formula for the item.

UseStandardFormula   Optional Variant. False (default) for upward compatibility. True for strings contained in any arguments that are item names, will be interpreted as having been formatted in standard U.S. English instead of local settings.

Adds a calculated field or calculated item to a PivotTable. Returns a CalculatedMember object.

expression.Add(Name, Formula, SolveOrder, Type)

expression    Required. An expression that returns a CalculatedMembers object.

Name   Required String. The name of the calculated member.

Formula   Required String. The formula of the calculated member.

SolveOrder   Optional Variant. The solve order for the calculated member.

Type   Optional Variant. The type of calculated member.

Remarks

The Formula argument must have a valid MDX (Multidimensional Expression) syntax statement. The Name argument has to be acceptable to the Online Analytical Processing (OLAP) provider and the Type argument has to be defined.

If you set the Type argument of this method to xlCalculatedSet, then you must call the AddSet method to make the new field set visible in the PivotTable.

Creates a new embedded chart. Returns a ChartObject object.

expression.Add(Left, Top, Width, Height)

expression    Required. An expression that returns a ChartObjects object.

Left   ,TopRequired Double. The initial coordinates of the new object (in points), relative to the upper-left corner of cell A1 on a worksheet or to the upper-left corner of a chart.

Width   ,HeightRequired Double. The initial size of the new object, in points.

Creates a new chart sheet. Returns a Chart object.

expression.Add(Before, After, Count)

expression    Required. An expression that returns a Charts object.

Before   Optional Variant. An object that specifies the sheet before which the new sheet is added.

After   Optional Variant. An object that specifies the sheet after which the new sheet is added.

Count   Optional Variant. The number of sheets to be added. The default value is one.

Remarks

Adds custom property information. Returns a CustomProperty object.

expression.Add(Name, Value)

expression    Required. An expression that returns a CustomProperties object.

Name   Required String. The name of the custom property.

Value   Required Variant. The value of the custom property.

Creates a new custom view. Returns a CustomView object that represents the new view.

expression.Add(ViewName, PrintSettings, RowColSettings)

expression    Required. An expression that returns a CustomViews object.

ViewName   Required String. The name of the new view.

PrintSettings   Optional Variant. True to include print settings in the custom view.

RowColSettings   Optional Variant. True to include settings for hidden rows and columns (including filter information) in the custom view.

Adds a new conditional format. Returns a FormatCondition object that represents the new conditional format.

expression.Add(Type, Operator, Formula1, Formula2)

expression    Required. An expression that returns a FormatConditions object.

Type   Required XlFormatConditionType. Specifies whether the conditional format is based on a cell value or an expression.

Operator   Optional Variant. The conditional format operator. Can be one of the following XlFormatConditionOperator constants: xlBetween, xlEqual, xlGreater, xlGreaterEqual, xlLess, xlLessEqual, xlNotBetween, or xlNotEqual. If Type is xlExpression, the Operator argument is ignored.

Formula1   Optional Variant. The value or expression associated with the conditional format. Can be a constant value, a string value, a cell reference, or a formula.

Formula2   Optional Variant. The value or expression associated with the second part of the conditional format when Operator is xlBetween or xlNotBetween (otherwise, this argument is ignored). Can be a constant value, a string value, a cell reference, or a formula.

Remarks

You cannot define more than three conditional formats for a range. Use the Modify method to modify an existing conditional format, or use the Delete method to delete an existing format before adding a new one.

Adds a horizontal page break. Returns an HPageBreak object.

expression.Add(Before)

expression    Required. An expression that returns an HPageBreaks object.

Before   Required Object. A Range object. The range above which the new page break will be added.

Adds a hyperlink to the specified range or shape. Returns a Hyperlink object.

expression.Add(Anchor, Address, SubAddress, ScreenTip, TextToDisplay)

expression    Required. An expression that returns a Hyperlinks object.

Anchor    Required Object. The anchor for the hyperlink. Can be either a Range or Shape object.

Address   Required String. The address of the hyperlink.

SubAddress   Optional Variant. The subaddress of the hyperlink.

ScreenTip    Optional Variant. The screen tip to be displayed when the mouse pointer is paused over the hyperlink.

TextToDisplay   Optional Variant. The text to be displayed for the hyperlink.

Remarks

When you specify the TextToDisplay argument, the text must be a string.

Adds a new column to the list object. Returns a ListColumn object.

expression.Add(Position)

expression   Required. An expression that returns a ListColumns object for the newly created column.

Position   Optional Integer. Specifies the relative position of the new column that starts at 1. The previous column at this position is shifted outward.

Remarks

If Position is not specified, a new rightmost column is added. A name for the column is automatically generated. The name of the new column can be changed after the column is added.

Creates a new list object. Returns a ListObject object.

expression.Add(SourceType, Source, LinkSource, HasHeaders, Destination)

expression    Required. An expression that returns a ListObjects object.

SourceType    Optional XlListObjectSourceType. Indicates the kind of source for the query. Can be one of the following XlListObjectSourceType constants: xlSrcExternal, or xlSrcRange. If omitted, the SourceType will default to xlSrcRange.

Source    Optional when SourceType = xlSrcRange. A Range object representing the data source. If omitted, the Source    will default to the range returned by list range detection code. Required when SourceType = xlSrcExternal. An array of String values specifying a connection to the source.

LinkSource    Optional Boolean. Indicates whether an external data source is to be linked to the ListObject object. If SourceType is xlSrcExternal, default is True. Invalid if SourceType is xlSrcRange, and will return an error if not omitted.

HasHeaders    Optional Variant. An XlYesNoGuess constant that indicates whether the data being imported has column labels. If the Source does not contain headers, Excel will automatically generate headers.

Destination    Optional Variant. A Range object specifying a single-cell reference as the destination for the top-left corner of the new list object. If the Range object refers to more than one cell, an error is generated. The Destination    argument must be specified when SourceType is set to xlSrcExternal. The Destination argument is ignored if SourceType is set to xlSrcRange. The destination range must be on the worksheet that contains the ListObjects collection specified by expression. New columns will be inserted at the Destination to fit the new list. Therefore, existing data will not be overwritten.

Remarks

When the list has headers, the first row of cells will be converted to Text, if not already set to text. The conversion will be based on the visible text for the cell. This means that if there is a date value with a Date format that changes with locale, the conversion to a list might produce different results depending on the current system locale. Moreover, if there are two cells in the header row that have the same visible text, an incremental Integer will be appended to make each column header unique.

Adds a new row to the list object. Returns a ListRow object.

expression.Add(Position)

expression   Required. An expression that returns a ListRows object for the newly created row.

Position   Optional Integer. Specifies the relative position of the new row.

Remarks

If Position is not specified, a new bottom row is added.

Defines a new name. Returns a Name object.

expression.Add(Name, RefersTo, Visible, MacroType, ShortcutKey, Category, NameLocal, RefersToLocal, CategoryLocal, RefersToR1C1, RefersToR1C1Local)

expression    Required. An expression that returns a Names object.

Name   Optional Variant. Required if NameLocal isn't specified. The text to use as the name (in the language of the macro). Names cannot include spaces and cannot look like cell references.

RefersTo   Optional Variant. Required unless one of the other RefersTo arguments is specified. Describes what the name refers to (in the language of the macro, using A1-style notation). Note Nothing is returned if the reference does not exist.

Visible   Optional Variant. True to define the name normally. False to define the name as a hidden name (that is, it doesn't appear in either the Define Name, Paste Name, or Goto dialog box). The default value is True.

MacroType   Optional Variant. The macro type, as shown in the following table.

ShortcutKey   Optional Variant. The macro shortcut key. Must be a single letter, such as "z" or "Z". Applies only for command macros.

Category   Optional Variant. The category of the macro or function if MacroType is 1 or 2. The category is used in the Function Wizard. Existing categories can be referred to either by number (starting at 1) or by name (in the language of the macro). Microsoft Excel creates a new category if the specified category doesn't already exist.

NameLocal   Optional Variant. Required if Name isn't specified. The text to use as the name (in the language of the user). Names cannot include spaces and cannot look like cell references.

RefersToLocal    Optional Variant. Required unless one of the other RefersTo arguments is specified. Describes what the name refers to (in the language of the user, using A1-style notation).

CategoryLocal    Optional Variant. Required if Category isn't specified. Text identifying the category of a custom function in the language of the user.

RefersToR1C1    Optional Variant. Required unless one of the other RefersTo arguments is specified. Describes what the name refers to (in the language of the macro, using R1C1-style notation).

RefersToR1C1Local    Optional Variant. Required unless one of the other RefersTo arguments is specified. Describes what the name refers to (in the language of the user, using R1C1-style notation).

Adds a new OLE object to a sheet. Returns an OLEObject object.

expression.Add(ClassType, FileName, Link, DisplayAsIcon, IconFileName, IconIndex, IconLabel, Left, Top, Width, Height)

expression    Required. An expression that returns an OLEObjects object.

ClassType   Optional Variant. (you must specify either ClassType or FileName). A string that contains the programmatic identifier for the object to be created. If ClassType is specified, FileName and Link are ignored.

FileName   Optional Variant. (you must specify either ClassType or FileName). A string that specifies the file to be used to create the OLE object.

Link   Optional Variant. True to have the new OLE object based on FileName be linked to that file. If the object isn't linked, the object is created as a copy of the file. The default value is False.

DisplayAsIcon   Optional Variant. True to display the new OLE object either as an icon or as its regular picture. If this argument is True, IconFileName and IconIndex can be used to specify an icon.

IconFileName   Optional Variant. A string that specifies the file that contains the icon to be displayed. This argument is used only if DisplayAsIcon is True. If this argument isn't specified or the file contains no icons, the default icon for the OLE class is used.

IconIndex   Optional Variant. The number of the icon in the icon file. This is used only if DisplayAsIcon is True and IconFileName refers to a valid file that contains icons. If an icon with the given index number doesn't exist in the file specified by IconFileName, the first icon in the file is used.

IconLabel   Optional Variant. A string that specifies a label to display beneath the icon. This is used only if DisplayAsIcon is True. If this argument is omitted or is an empty string (""), no caption is displayed.

Left   ,Top Optional Variant. The initial coordinates of the new object, in points, relative to the upper-left corner of cell A1 on a worksheet, or to the upper-left corner of a chart.

Width   ,Height Optional Variant. The initial size of the new object, in points.

Creates a new query parameter. Returns a Parameter object.

expression.Add(Name, iDataType)

expression    Required. An expression that returns a Parameters object.

Name   Required String. The name of the specified parameter. The parameter name should match the parameter clause in the SQL statement.

iDataType    Optional Variant. The data type of the parameter. Can be any XlParameterDataType constant:

Adds phonetic text to the specified cellt.

expression.Add(Start, Length, Text)

expression    Required. An expression that returns a Phonetics object.

Start   Required Long. The position that represents the first character in the specified cell.

Length   Required Long. The number of characters from the Start position to the end of the text in the cell.

Text    Required String. Collectively, the characters that represent the phonetic text in the cell.

Adds a new PivotTable cache to a PivotCaches collection. Returns a PivotCache object.

expression.Add(SourceType, SourceData)

expression    Required. An expression that returns a PivotCaches object.

SourceType   Required XlPivotTableSourceType. The source of the PivotTable cache data.

SourceData   Optional Variant. The data for the new PivotTable cache. This argument is required if SourceType isn't xlExternal. Can be a Range object, an array of ranges, or a text constant that represents the name of an existing PivotTable report. For an external database, this is a two-element array. The first element is the connection string specifying the provider of the data. The second element is the SQL query string used to get the data. If you specify this argument, you must also specify SourceType.

Remarks

If the PivotTable cache isn't referenced by a PivotTable object, the PivotTable cache is automatically deleted before the workbook is saved.

Creates a new PivotTable formula. Returns a PivotFormula object.

expression.Add(Formula, UseStandardFormula)

expression    Required. An expression that returns one of the above objects.

Formula   Required String. The new PivotTable formula.

UseStandardFormula   Optional Variant. A standard PivotTable formula.

Creates a new PivotTable item.

expression.Add(Name)

expression    Required. An expression that returns a PivotItems object.

Name   Required String. The name of the new PivotTable item.

Adds a new PivotTable report. Returns a PivotTable object.

expression.Add(PivotCache, TableDestination, TableName, ReadData, DefaultVersion)

expression    Required. An expression that returns a PivotTables object.

PivotCache Required PivotCache. The PivotTable cache on which the new PivotTable report is based. The cache provides data for the report.

TableDestination   Required Variant. The cell in the upper-left corner of the PivotTable report's destination range (the range on the worksheet where the resulting report will be placed). You must specify a destination range on the worksheet that contains the PivotTables object specified by expression   .

TableName   Optional Variant. The name of the new PivotTable report.

ReadData   Optional Variant. True to create a PivotTable cache that contains all records from the external database; this cache can be very large. False to enable setting some of the fields as server-based page fields before the data is actually read.

DefaultVersion   Optional Variant. The version of Microsoft Excel the PivotTable was originally created in.

Creates an object that represents an item in a document saved to a Web page. Such objects facilitate subsequent updates to the Web page while automated changes are being made to the document in Microsoft Excel. Returns a PublishObject object.

expression.Add(SourceType, FileName, Sheet, Source, HtmlType, DivID, Title)

expression    Required. An expression that returns a PublishObjects object.

SourceType   Required XlSourceType. The source type.

FileName   Required String. The URL (on the intranet or the Web) or path (local or network) to which the source object was saved.

Sheet   Optional Variant. The name of the worksheet that was saved as a Web page.

Source   Optional Variant. A unique name used to identify items that have one of the following constants as their SourceType argument: xlSourceAutoFilter, xlSourceChart, xlSourcePivotTable, xlSourcePrintArea, xlSourceQuery, or xlSourceRange. If SourceType is xlSourceRange, Source specifies a range, which can be a defined name. If SourceType is xlSourceChart, xlSourcePivotTable, or xlSourceQuery, Source specifies the name of a chart, PivotTable report, or query table.

HtmlType   Optional Variant. Specifies whether the item is saved as an interactive Microsoft Office Web component or as static text and images. Can be one of the XlHTMLType constants listed in the following table.

DivID   Optional Variant. The unique identifier used in the HTML DIV tag to identify the item on the Web page.

Title   Optional Variant. The title of the Web page.

Creates a new query table. Returns a QueryTable object that represents the new query table.

expression.Add(Connection, Destination, Sql)

expression    Required. An expression that returns a QueryTables object.

Connection   Required Variant. The data source for the query table. Can be one of the following:

• A string containing an OLE DB or ODBC connection string. The ODBC connection string has the form "ODBC;<connection string>".
• A QueryTable object from which the query information is initially copied, including the connection string and the SQL text, but not including the Destination range. Specifying a QueryTable object causes the Sql argument to be ignored.
• An ADO or DAO Recordset object. Data is read from the ADO or DAO recordset. Microsoft Excel retains the recordset until the query table is deleted or the connection is changed. The resulting query table cannot be edited.
• A Web query. A string in the form "URL;<url>", where "URL;" is required but not localized and the rest of the string is used for the URL of the Web query.
• Data Finder. A string in the form "FINDER;<data finder file path>" where "FINDER;" is required but not localized. The rest of the string is the path and file name of a Data Finder file (*.dqy or *.iqy). The file is read when the Add method is run; subsequent calls to the Connection property of the query table will return strings beginning with "ODBC;" or "URL;" as appropriate.
• A text file. A string in the form "TEXT;<text file path and name>", where TEXT is required but not localized.

Destination   Required Range. The cell in the upper-left corner of the query table destination range (the range where the resulting query table will be placed). The destination range must be on the worksheet that contains the QueryTables object specified by expression   .

Sql   Optional Variant. The SQL query string to be run on the ODBC data source. This argument is optional when you're using an ODBC data source (if you don't specify it here, you should set it by using the Sql property of the query table before the table is refreshed). You cannot use this argument when a QueryTable object, text file, or ADO or DAO Recordset object is specified as the data source.

Remarks

A query created by this method isn't run until the Refresh method is called.

Adds a file to the list of recently used files. Returns a RecentFile object.

expression.Add(Name)

expression    Required. An expression that returns a RecentFiles object.

Name   Required String. The file name.

Creates a new scenario and adds it to the list of scenarios that are available for the current worksheet. Returns a Scenario object.

expression.Add(Name, ChangingCells, Values, Comment, Locked, Hidden)

expression    Required. An expression that returns a Scenarios object.

Name   Required String. The scenario name.

ChangingCells   Required Variant. A Range object that refers to the changing cells for the scenario.

Values   Optional Variant. An array that contains the scenario values for the cells in ChangingCells. If this argument is omitted, the scenario values are assumed to be the current values in the cells inChangingCells.

Comment   Optional Variant. A string that specifies comment text for the scenario. When a new scenario is added, the author's name and date are automatically added at the beginning of the comment text.

Locked   Optional Variant. True to lock the scenario to prevent changes. The default value is True.

Hidden   Optional Variant. True to hide the scenario. The default value is False.

Remarks

A scenario name must be unique; Microsoft Excel generates an error if you try to create a scenario with a name that's already in use.

Adds one or more new series to the SeriesCollection collection.

expression.Add(Source, Rowcol, SeriesLabels, CategoryLabels, Replace)

expression    Required. An expression that returns a SeriesCollection object.

Source   Required Variant. The new data, either as a Range object or an array of data points.

Rowcol   Optional XlRowCol. Specifies whether the new values are in the rows or columns of the specified range.

SeriesLabels   Optional Variant. Ignored if Source is an array. True if the first row or column contains the name of the data series. False if the first row or column contains the first data point of the series. If this argument is omitted, Microsoft Excel attempts to determine the location of the series name from the contents of the first row or column.

CategoryLabels   Optional Variant. Ignored if Source is an array. True if the first row or column contains the name of the category labels. False if the first row or column contains the first data point of the series. If this argument is omitted, Microsoft Excel attempts to determine the location of the category label from the contents of the first row or column.

Replace   Optional Variant. If CategoryLabels is True and Replace is True, the specified categories replace the categories that currently exist for the series. If Replace is False, the existing categories will not be replaced. The default value is False.

Remarks

This method does not actually return a SeriesCollection object as stated in the Object Browser. This method is not available for PivotChart reports.

Creates a new worksheet, chart, or macro sheet. The new worksheet becomes the active sheet.

expression.Add(Before, After, Count, Type)

expression    Required. An expression that returns one of the above objects.

Before   Optional Variant. An object that specifies the sheet before which the new sheet is added.

After   Optional Variant. An object that specifies the sheet after which the new sheet is added.

Count   Optional Variant. The number of sheets to be added. The default value is one.

Type   Optional Variant. Specifies the sheet type. Can be one of the following XlSheetType constants: xlWorksheet, xlChart, xlExcel4MacroSheet, or xlExcel4IntlMacroSheet. If you are inserting a sheet based on an existing template, specify the path to the template. The default value is xlWorksheet.

Remarks

If Before and After are both omitted, the new sheet is inserted before the active sheet.

Adds a smart tag. Returns a SmartTag object.

expression.Add(SmartTagType)

expression    Required. An expression that returns a SmartTags object.

SmartTagType   Required String. The type of smart tag.

Creates a new style and adds it to the list of styles that are available for the current workbook. Returns a Style object.

expression.Add(Name, BasedOn)

expression    Required. An expression that returns a Styles object.

Name   Required String. The new style name.

BasedOn   Optional Variant. A Range object that refers to a cell that's used as the basis for the new style. If this argument is omitted, the newly created style is based on the Normal style.

Remarks

If a style with the specified name already exists, this method redefines the existing style based on the cell specified in BasedOn. The following example redefines the Normal style based on the active cell.

ActiveWorkbook.Styles.Add Name := "Normal", BasedOn := ActiveCell

Creates a new trendline. Returns a Trendline object.

expression.Add(Type, Order, Period, Forward, Backward, Intercept, DisplayEquation, DisplayRSquared, Name)

expression    Required. An expression that returns a Trendlines object.

Type   Optional XlTrendlineType. The trendline type.

Order   Optional Variant. Optional Variant. Required if Type is xlPolynomial. The trendline order. Must be an integer from 2 to 6, inclusive.

Period   Optional Variant. Required if Type is xlMovingAvg. The trendline period. Must be an integer greater than 1 and less than the number of data points in the series you're adding a trendline to.

Forward   Optional Variant. The number of periods (or units on a scatter chart) that the trendline extends forward.

Backward   Optional Variant. The number of periods (or units on a scatter chart) that the trendline extends backward.

Intercept   Optional Variant. The trendline intercept. If this argument is omitted, the intercept is automatically set by the regression.

DisplayEquation   Optional Variant. True to display the equation of the trendline on the chart (in the same data label as the R-squared value). The default value is False.

DisplayRSquared   Optional Variant. True to display the R-squared value of the trendline on the chart (in the same data label as the equation). The default value is False.

Name   Optional Variant. The name of the trendline as text. If this argument is omitted, Microsoft Excel generates a name.

Adds a user access list. Returns a UserAccess object.

expression.Add(Name, AllowEdit)

expression    Required. An expression that returns a UserAccessList object.

Name   Required String. The name of the user access list.

AllowEdit   Required Boolean. True allows users on the access list to edit the editable ranges on a protected worksheet.

Adds data validation to the specified range.

expression.Add(Type, AlertStyle, Operator, Formula1, Formula2)

expression    Required. An expression that returns a Validation object.

Type   Required XlDVType. The validation type.

AlertStyle   Optional Variant. The validation alert style. Can be one of the following XlDVAlertStyle constants: xlValidAlertInformation, xlValidAlertStop, or xlValidAlertWarning.

Operator   Optional Variant. The data validation operator. Can be one of the following XlFormatConditionOperator constants: xlBetween, xlEqual, xlGreater, xlGreaterEqual, xlLess, xlLessEqual, xlNotBetween, or xlNotEqual.

Formula1   Optional Variant. The first part of the data validation equation.

Formula2   Optional Variant. The second part of the data validation when Operator is xlBetween or xlNotBetween (otherwise, this argument is ignored).

Remarks

The Add method requires different arguments, depending on the validation type, as shown in the following table.

Adds a vertical page break. Returns a VPageBreak object.

expression.Add(Before)

expression    Required. An expression that returns a VPageBreaks object.

Before   Required Object. A Range object. The range to the left of which the new page break will be added.

Adds a range which is tracked when the worksheet is recalculated. Returns a Watch object.

expression.Add(Source)

expression    Required. An expression that returns a Watches object.

Source   Required Variant. The source for the range.

Creates a new workbook. The new workbook becomes the active workbook. Returns a Workbook object.

expression.Add(Template)

expression    Required. An expression that returns a Workbooks object.

Template   Optional Variant. Determines how the new workbook is created. If this argument is a string specifying the name of an existing Microsoft Excel file, the new workbook is created with the specified file as a template. If this argument is a constant, the new workbook contains a single sheet of the specified type. Can be one of the following XlWBATemplate constants: xlWBATChart, xlWBATExcel4IntlMacroSheet, xlWBATExcel4MacroSheet, or xlWBATWorksheet. If this argument is omitted, Microsoft Excel creates a new workbook with a number of blank sheets (the number of sheets is set by the SheetsInNewWorkbook property).

Remarks

If the Template argument specifies a file, the file name can include a path.

Note  XML features, except for saving files in the XML Spreadsheet format, are available only in Microsoft Office Professional Edition 2003 and Microsoft Office Excel 2003.

Adds an XML map to the specified workbook. Returns an XmlMap object.

expression.Add(Schema, RootElementName)

expression    Required. An expression that returns a Workbook object.

Schema    Required String. The schema to be added as an XML map. The string can be a path to a schema file, or the schema itself. The path can be specified in the Universal Naming Convention (UNC) or Uniform Resource Locator (URL) format.

RootElementName    Optional String. The name of the root element. This argument can be ignored if the schema contains only one root element.

This example inserts the add-in Myaddin.xla from drive A. When you run this example, Microsoft Excel copies the file A:\Myaddin.xla to the Library folder on your hard disk and adds the add-in title to the list in the Add-Ins dialog box.

Sub UseAddIn()

    Set myAddIn = AddIns.Add(Filename:="A:\MYADDIN.XLA", _
        CopyFile:=True)
    MsgBox myAddIn.Title & " has been added to the list"

End Sub
				

This example allows edits to range "A1:A4" on the active worksheet, notifies the user, then changes the password for this specified range and notifies the user of this change.

Sub UseChangePassword()

    Dim wksOne As Worksheet

    Set wksOne = Application.ActiveSheet

    ' Protect the worksheet.
    wksOne.Protect

    ' Establish a range that can allow edits
    ' on the protected worksheet.
    wksOne.Protection.AllowEditRanges.Add _
        Title:="Classified", _
        Range:=Range("A1:A4"), _
        Password:="secret"

    MsgBox "Cells A1 to A4 can be edited on the protected worksheet."

    ' Change the password.
    wksOne.Protection.AllowEditRanges(1).ChangePassword _
        Password:="moresecret"

    MsgBox "The password for these cells has been changed."

End Sub
				

This example adds a calculated field to the first PivotTable report on worksheet one.

Worksheets(1).PivotTables(1).CalculatedFields.Add "PxS", _
    "= Product * Sales"
				

The following example adds a set to a PivotTable, assuming a PivotTable exists on the active worksheet.

Sub UseAddSet()

    Dim pvtOne As PivotTable
    Dim strAdd As String
    Dim strFormula As String
    Dim cbfOne As CubeField

    Set pvtOne = ActiveSheet.PivotTables(1)

    strAdd = "[MySet]"
    strFormula = "'{[Product].[All Products].[Food].children}'"

    ' Establish connection with data source if necessary.
    If Not pvtOne.PivotCache.IsConnected Then pvtOne.PivotCache.MakeConnection

    ' Add a calculated member titled "[MySet]"
    pvtOne.CalculatedMembers.Add Name:=strAdd, _
        Formula:=strFormula, Type:=xlCalculatedSet

    ' Add a set to the CubeField object.
    Set cbfOne = pvtOne.CubeFields.AddSet(Name:="[MySet]", _
        Caption:="My Set")

End Sub
				

This example creates a new embedded chart..

Set co = Sheets("Sheet1").ChartObjects.Add(50, 40, 200, 100)
co.Chart.ChartWizard Source:=Worksheets("Sheet1").Range("A1:B2"), _
    Gallery:=xlColumn, Format:=6, PlotBy:=xlColumns, _
    CategoryLabels:=1, SeriesLabels:=0, HasLegend:=1
				

This example creates an empty chart sheet and inserts it before the last worksheet.

ActiveWorkbook.Charts.Add Before:=Worksheets(Worksheets.Count)
				

This example adds identifier information to the active worksheet and returns the name and value to the user.

Sub CheckCustomProperties()

    Dim wksSheet1 As Worksheet

    Set wksSheet1 = Application.ActiveSheet

    ' Add metadata to worksheet.
    wksSheet1.CustomProperties.Add _
        Name:="Market", Value:="Nasdaq"

    ' Display metadata.
    With wksSheet1.CustomProperties.Item(1)
        MsgBox .Name & vbTab & .Value
    End With

End Sub
				

This example creates a new custom view named "Summary" in the active workbook.

ActiveWorkbook.CustomViews.Add "Summary", True, True
				

This example adds a conditional format to cells E1:E10.

With Worksheets(1).Range("e1:e10").FormatConditions _
    .Add(xlCellValue, xlGreater, "=$a$1")
    With .Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 6
    End With
    With .Font
        .Bold = True
        .ColorIndex = 3
    End With
End With
				

This example adds a horizontal page break above cell F25 and adds a vertical page break to the left of this cell.

With Worksheets(1)
    .HPageBreaks.Add .Range("F25")
    .VPageBreaks.Add .Range("F25")
End With
				

This example adds a hyperlink to cell A5.

With Worksheets(1)
    .Hyperlinks.Add Anchor:=.Range("a5"), _
        Address:="http://example.microsoft.com", _
        ScreenTip:="Microsoft Web Site", _
        TextToDisplay:="Microsoft"
End With
				

This example adds an email hyperlink to cell A5.

With Worksheets(1)
    .Hyperlinks.Add Anchor:=.Range("a5"), _
        Address:="mailto:[email protected]?subject=hello", _
        ScreenTip:="Write us today", _
        TextToDisplay:="Support"
End With

				

The following example adds a new column to the default ListObject object in the first worksheet of the workbook. Because no position is specified, a new rightmost column is added.

Set myNewColumn = ActiveWorkbook.Worksheets(1).ListObjects(1).ListColumns.Add

Note  A name for the column is automatically generated. You can choose to change the name after the column has been added.

The following example adds a new ListObject object based on data from a Microsoft Windows SharePoint Services site to the default ListObjects collection and places the list in cell A1 in the first worksheet of the workbook.

Note  The following code example assumes that you will substitute a valid server name and the list guid in the variables strServerName and strListGUID. Additionally, the server name must be followed by "/_vti_bin" or the sample will not work.

Set objListObject = ActiveWorkbook.Worksheets(1).ListObjects.Add(SourceType:= xlSrcExternal, _
      Source:= Array(strServerName, StrListGUID), TRUE, XlGuess, Destination:= Range("A1")

Note  If there is existing data at cell A1, the existing list data will be moved to the right to accommodate the new list.

The following example adds a new row to the default ListObject object in the first worksheet of the workbook. Because no position is specified, the new row is added to the bottom of the list.

Set myNewColumn = ActiveWorkbook.Worksheets(1).ListObject(1).ListRows.Add

This example defines a new name for the range A1:D3 on Sheet1 in the active workbook. Note     Nothing is returned if Sheet1 does not exist.

Sub MakeRange()

    ActiveWorkbook.Names.Add _
        Name:="tempRange", _
        RefersTo:="=Sheet1!$A$1:$D$3"

End Sub
				

This example creates a new Microsoft Word OLE object on Sheet1.

ActiveWorkbook.Worksheets("Sheet1").OLEObjects.Add _
    ClassType:="Word.Document"
				

This example adds a command button to sheet one.

Worksheets(1).OLEObjects.Add ClassType:="Forms.CommandButton.1", _
    Link:=False, DisplayAsIcon:=False, Left:=40, Top:=40, _
    Width:=150, Height:=10
				

This example changes the SQL statement for query table one. The clause "(city=?)" indicates that the query is a parameter query, and the value of city is set to the constant "Oakland."

Set qt = Sheets("sheet1").QueryTables(1)
qt.Sql = "SELECT * FROM authors  WHERE (city=?)"
Set param1 = qt.Parameters.Add("City Parameter", _
    xlParamTypeVarChar)
param1.SetParam xlConstant, "Oakland"
qt.Refresh
				

This example adds three phonetic text strings to the active cell. The example then sets the character type to Hiragana, sets the font color to blue, and sets the text to visible.

ActiveCell.FormulaR1C1 = ""
ActiveCell.Phonetics.Add Start:=1, Length:=3, Text:=""
ActiveCell.Phonetics.Add Start:=4, Length:=3, Text:=""
ActiveCell.Phonetics.CharacterType = xlHiragana
ActiveCell.Phonetics.Font.Color = vbBlue
ActiveCell.Phonetics.Visible = True
				

This example creates a new PivotTable cache based on an OLAP provider and then it creates a new PivotTable report based on the cache, at cell A3 on the active worksheet.

Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command

' Open the connection.
Set cnnConn = New ADODB.Connection
With cnnConn
    .ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0"
    .Open "C:\perfdate\record.mdb"
End With

' Set the command text.
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
    .CommandText = "Select Speed, Pressure, Time From DynoRun"
    .CommandType = adCmdText
    .Execute
End With

' Open the recordset.
Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = cnnConn
rstRecordset.Open cmdCommand

' Create a PivotTable cache and report.
Set objPivotCache = ActiveWorkbook.PivotCaches.Add( _
    SourceType:=xlExternal)
Set objPivotCache.Recordset = rstRecordset
With objPivotCache
    .CreatePivotTable TableDestination:=Range("A3"), _
        TableName:="Performance"
End With

With ActiveSheet.PivotTables("Performance")
    .SmallGrid = False
    With .PivotFields("Pressure")
        .Orientation = xlRowField
        .Position = 1
    End With
    With .PivotFields("Speed")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With .PivotFields("Time")
        .Orientation = xlDataField
        .Position = 1
    End With
End With

' Close the connections and clean up.
cnnConn.Close
Set cmdCommand = Nothing
Set rstRecordSet = Nothing
Set cnnConn = Nothing
				

This example creates a new PivotTable formula for the first PivotTable report on worksheet one.

Worksheets(1).PivotTables(1).PivotFormulas _
    .Add "Year['1998'] Apples = (Year['1997'] Apples) * 2"
				

This example creates a new PivotTable item in the first PivotTable report on worksheet one.

Worksheets(1).PivotTables(1).PivotItems("Year").Add "1998"
				

This example creates a new PivotTable cache based on an OLAP provider, and then it creates a new PivotTable report based on the cache, at cell A1 on the first worksheet.

Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command

' Open the connection.
Set cnnConn = New ADODB.Connection
With cnnConn
    .ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0"
    .Open "C:\perfdate\record.mdb"
End With

' Set the command text.
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
    .CommandText = "Select Speed, Pressure, Time From DynoRun"
    .CommandType = adCmdText
    .Execute
End With

' Open the recordset.
Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = cnnConn
rstRecordset.Open cmdCommand

' Create PivotTable cache and report.
Set objPivotCache = ActiveWorkbook.PivotCaches.Add( _
    SourceType:=xlExternal)
Set objPivotCache.Recordset = rstRecordset

ActiveSheet.PivotTables.Add _
    PivotCache:=objPivotCache, _
    TableDestination:=Range("A3"), _
    TableName:="Performance"

With ActiveSheet.PivotTables("Performance")
    .SmallGrid = False
    With .PivotFields("Pressure")
        .Orientation = xlRowField
        .Position = 1
    End With
    With .PivotFields("Speed")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With .PivotFields("Time")
        .Orientation = xlDataField
        .Position = 1
    End With
End With

' Close the connections and clean up.
cnnConn.Close
Set cmdCommand = Nothing
Set rstRecordSet = Nothing
Set cnnConn = Nothing
				

This example saves the range D5:D9 on the First Quarter worksheet in the active workbook to a Web page called Stockreport.htm. You use the Spreadsheet component to add interactivity to the Web page.

ActiveWorkbook.PublishObjects.Add( _
    SourceType:=xlSourceRange, _
    Filename:="\\Server2\Q1\Stockreport.htm", _
    Sheet:="First Quarter", _
    Source:="D5:D9", _
    HTMLType:=xlHTMLCalc).Publish
				

This example creates a query table based on an ADO recordset. The example preserves the existing column sorting and filtering settings and layout information for backward compatibility.

Dim cnnConnect As ADODB.Connection
Dim rstRecordset As ADODB.Recordset

Set cnnConnect = New ADODB.Connection
cnnConnect.Open "Provider=SQLOLEDB;" & _
    "Data Source=srvdata;" & _
    "User ID=testac;Password=4me2no;"

Set rstRecordset = New ADODB.Recordset
rstRecordset.Open _
    Source:="Select Name, Quantity, Price From Products", _
    ActiveConnection:=cnnConnect, _
    CursorType:=adOpenDynamic, _
    LockType:=adLockReadOnly, _
    Options:=adCmdText

With ActiveSheet.QueryTables.Add( _
        Connection:=rstRecordset, _
        Destination:=Range("A1"))
    .Name = "Contact List"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = True
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
End With
				

This example imports a fixed width text file into a new query table. The first column in the text file is five characters wide and is imported as text. The second column is four characters wide and is skipped. The remainder of the text file is imported into the third column and has the General format applied to it.

Set shFirstQtr = Workbooks(1).Worksheets(1)
Set qtQtrResults = shFirstQtr.QueryTables.Add( _
    Connection := "TEXT;C:\My Documents\19980331.txt",
    Destination := shFirstQtr.Cells(1,1))
With qtQtrResults
    .TextFileParsingType = xlFixedWidth
    .TextFileFixedColumnWidths := Array(5,4)
    .TextFileColumnDataTypes := _
        Array(xlTextFormat, xlSkipColumn, xlGeneralFormat)
    .Refresh
End With
				

This example creates a new query table on the active worksheet.

sqlstring = "select 96Sales.totals from 96Sales where profit < 5"
connstring = _
    "ODBC;DSN=96SalesData;UID=Rep21;PWD=NUyHwYQI;Database=96Sales"
With ActiveSheet.QueryTables.Add(Connection:=connstring, _
        Destination:=Range("B1"), Sql:=sqlstring)
    .Refresh
End With
				

This example adds Oscar.xls to the list of recently used files.

Application.RecentFiles.Add Name:="Oscar.xls"
				

This example adds a new scenario to Sheet1.

Worksheets("Sheet1").Scenarios.Add Name:="Best Case", _
    ChangingCells:=Worksheets("Sheet1").Range("A1:A4"), _
    Values:=Array(23, 5, 6, 21), _
    Comment:="Most favorable outcome."
				

This example creates a new series in Chart1. The data source for the new series is range B1:B10 on Sheet1.

Charts("Chart1").SeriesCollection.Add _
    Source:=ActiveWorkbook.Worksheets("Sheet1").Range("B1:B10")
				

This example creates a new series on the embedded chart on Sheet1.

Worksheets("Sheet1").ChartObjects(1).Activate
ActiveChart.SeriesCollection.Add _
    Source:=Worksheets("Sheet1").Range("B1:B10")
				

This example inserts a new worksheet before the last worksheet in the active workbook.

ActiveWorkbook.Sheets.Add Before:=Worksheets(Worksheets.Count)
				

This example adds a smart tag titled MSFT to cell A1, then adds extra metadata called Market with the value of Nasdaq to the smart tag and then returns the value of the property to the user. This example assumes the host system is connected to the Internet.

Sub UseProperties()

    Dim strLink As String
    Dim strType As String

    ' Define smart tag variables.
    strLink = "urn:schemas-microsoft-com:smarttags#stocktickerSymbol"
    strType = "stockview"

    Range("A1").Formula = "MSFT"

    ' Add a property for MSFT smart tag and define its value.
    Range("A1").SmartTags.Add(strLink).Properties.Add _
        Name:="Market", Value:="Nasdaq"

    ' Notify the user of the smart tag's value.
    MsgBox Range("A1").SmartTags.Add(strLink).Properties("Market").Value

End Sub
				

This example defines a new style based on cell A1 on Sheet1.

With ActiveWorkbook.Styles.Add(Name:="theNewStyle")
    .IncludeNumber = False
    .IncludeFont = True
    .IncludeAlignment = False
    .IncludeBorder = False
    .IncludePatterns = False
    .IncludeProtection = False
    .Font.Name = "Arial"
    .Font.Size = 18
End With
				

This example creates a new linear trendline in Chart1.

ActiveWorkbook.Charts("Chart1").SeriesCollection(1).Trendlines.Add
				

This example adds data validation to cell E5.

With Range("e5").Validation
    .Add Type:=xlValidateWholeNumber, _
        AlertStyle:= xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="5", Formula2:="10"
    .InputTitle = "Integers"
    .ErrorTitle = "Integers"
    .InputMessage = "Enter an integer from five to ten"
    .ErrorMessage = "You must enter a number from five to ten"
End With
				

This example adds a horizontal page break above cell F25 and adds a vertical page break to the left of this cell.

With Worksheets(1)
    .HPageBreaks.Add .Range("F25")
    .VPageBreaks.Add .Range("F25")
End With
				

This example creates a summation formula in cell A3 and then adds this cell to the watch facility.

Sub AddWatch()

    With Application
        .Range("A1").Formula = 1
        .Range("A2").Formula = 2
        .Range("A3").Formula = "=Sum(A1:A2)"
        .Range("A3").Select
        .Watches.Add Source:=ActiveCell
    End With

End Sub
				

This example creates a new workbook.

Workbooks.Add