Excel

XML Events for Application Object

In this tutorial you’ll learn how to capture XML events that occur on any opened Excel workbook when XML data is exported or imported. If you want to capture XML events for a particular workbook, see XML Events for a Particular Workbook. To learn more about the XML, see Understanding XML. The following list shows the XML events that trigger on Application-level:

  1. WorkbookAfterXmlExport
  2. WorkbookAfterXmlImport
  3. WorkbookBeforeXmlExport
  4. WorkbookBeforeXmlImport

To write event listener procedures you need to open VBE. Press Alt+F11 to open the VBE and write the following code in the ThisWorkbook code module to create the Application object variable:

Option Explicit
Dim WithEvents App As Application
Private Sub Workbook_Open()
 Set App = Application
End Sub

After writing an event procedure, you must close and re-open the workbook to able to capture the Application-level events. For more information, visit How to monitor Application-level events.

WorkbookAfterXmlExport

Syntax: App_WorkbookAfterXmlExport(Wb, Map, Url, Result)

The WorkbookAfterXmlExport event occurs after Excel exports or saves data from any open workbook to an XML data file. The procedure for this event uses four arguments:

  1. Wb
    The workbook that triggered the event
  2. Map
    The map used to export or save the data
  3. Url
    Location of the XML file
  4. Result
    True indicates success, False indicates failure.

App_WorkbookAfterXmlExport Example:

Option Explicit
Dim WithEvents App As Application
Private Sub Workbook_Open()
 Set App = Application
End Sub
Private Sub App_WorkbookAfterXmlExport(ByVal Wb As Workbook, ByVal Map As XmlMap, ByVal Url As String, ByVal Result As XlXmlExportResult)
 'Your code
End Sub

Note: Use Workbook_AfterXmlExport procedure for a particular workbook.

WorkbookAfterXmlImport

Syntax: App_WorkbookAfterXmlImport(Wb, Map, IsRefresh, Result)

The WorkbookAfterXmlImport event occurs after an existing XML data connection is refreshed or new XML data is imported into any open Excel workbook. The procedure for this event uses four arguments:

  1. Wb
    The workbook that triggered the event
  2. Map
    The map used to export or save the data
  3. IsRefresh
    Returns True if the event was triggered by refreshing an existing connection and False if triggered by importing from a new data source
  4. Result
    True indicates success, False indicates failure.

App_WorkbookAfterXmlImport Example:

Option Explicit
Dim WithEvents App As Application
Private Sub Workbook_Open()
 Set App = Application
End Sub
Private Sub App_WorkbookAfterXmlImport(ByVal Wb As Workbook, ByVal Map As XmlMap, ByVal IsRefresh As Boolean, ByVal Result As XlXmlImportResult)
 'Your code
End Sub

Note: Use Workbook_AfterXmlImport procedure for a particular workbook.

WorkbookBeforeXmlExport

Syntax: App_WorkbookBeforeXmlExport(Wb, Map, Url, Cancel)

The WorkbookBeforeXmlExport event occurs before Excel saves or exports data from any open workbook to an XML data file. The procedure for this event uses four arguments:

  1. Wb
    The workbook that triggered the event
  2. Map
    The map used to export or save the data
  3. Url
    Location of the XML file
  4. Cancel
    Set to True cancels the export operation

App_WorkbookBeforeXmlExport Example:

Option Explicit
Dim WithEvents App As Application
Private Sub Workbook_Open()
 Set App = Application
End Sub
Private Sub App_WorkbookBeforeXmlExport(ByVal Wb As Workbook, ByVal Map As XmlMap, ByVal Url As String, Cancel As Boolean)
 'Your code
End Sub

Note: Use Workbook_BeforeXmlExport procedure for a particular workbook.

WorkbookBeforeXmlImport

Syntax: App_WorkbookBeforeXmlImport(Wb, Map, Url, IsRefresh, Cancel)

The WorkbookBeforeXmlImport eventoccurs before an existing XML data connection is refreshed or new XML data is imported into any open Excel workbook. The event procedure for this event uses five arguments:

  1. Wb
    The workbook that triggered the event.
  2. Map
    The map used to import the data.
  3. Url
    The location of the XML file.
  4. IsRefresh returns True or False
    • True: if refreshing an existing connection
    • False: if importing from a new data source
  5. Cancel
    Setting Cancel to True cancels the import or refresh operation.

App_WorkbookBeforeXmlImport Example:

Option Explicit
Dim WithEvents App As Application
Private Sub Workbook_Open()
 Set App = Application
End Sub
Private Sub App_WorkbookBeforeXmlImport(ByVal Wb As Workbook, ByVal Map As XmlMap, ByVal Url As String, ByVal IsRefresh As Boolean, Cancel As Boolean)
'Your code
End Sub

Note: Use Workbook_BeforeXmlImport procedure for a particular workbook.