Excel

XML Import and Export Events

This tutorial introduces you to XML events that occur before and after data is exported, imported, or refreshed via the XML map. You can write event listener procedures for these events to control what happens before and after import & export. (See How to import XML data file and How to export data to XML file).

Let's get started. Open a workbook and press Alt + F11 to open the Visual Basic Editor (VBE) window. Write the XML events procedures code in the ThisWorkbook code window, see following figure:
XML Events Code

To control XML data import and export, the Workbook object provides the following event handler procedures:

Workbook_AfterXmlExport

Syntax: Workbook_AfterXmlExport (Map, Url, Result)

The AfterXmlExport event occurs after the user exports or saves data to an XML data file. To handle this event, the Workbook_AfterXmlExport event listener procedure is used which required the following parameters:

  • Map
    The XML schema map that was used to save or export data.
  • Url
    The location of the XML file that was exported.
  • Result
    A constant that confirms the success or failure of the save or export operation. To check the result, use following constants:
    1. xlXmlExportSuccess
      Specifies that the XML data file was successfully exported.
    2. xlXmlExportValidationFailed
      Specifies that the content of the XML data file does not match the specified schema map.

Workbook_AfterXmlExport Example:

Private Sub Workbook_AfterXmlExport(ByVal Map As XmlMap, ByVal Url As String, ByVal Result As XlXmlExportResult)
 If Result = xlXmlExportSuccess Then
  MsgBox ("XML Export Successful")
 Else
  MsgBox ("XML Export Failed")
 End If
End Sub

Workbook_AfterXmlImport

Syntax: Workbook_AfterXmlImport (Map, IsRefresh, Result)

This event occurs after an existing XML data connection is refreshed or new XML data is imported. To handle this event, the Workbook_AfterXmlImport event listener procedure is used which required the following parameters:

  • Map
    The XML schema map that was used to import data.
  • IsRefresh
    True if the event was triggered by refreshing an existing connection and False if triggered by importing from a new data source
  • Result
    A constant that confirms the result of the refresh or import operation. To check the result, use following constants::
    1. xlXmlImportElementsTruncated
      Specifies that the content of the specified XML data file has been truncated because the XML data file is too large for the worksheet.
    2. xlXmlImportSuccess
      Specifies that the XML data file was successfully imported.
    3. xlXmlImportValidationFailed
      Specifies that the content of the XML data file does not match the specified schema map

Workbook_AfterXmlImport Example:

Private Sub Workbook_AfterXmlImport(ByVal Map As XmlMap, ByVal IsRefresh As Boolean, ByVal Result As XlXmlImportResult)
 If Result = xlXmlImportSuccess Then
  MsgBox ("XML Import Successful")
 ElseIf Result = xlXmlImportElementsTruncated Then
  MsgBox ("XML Import Truncated")
 Else
  MsgBox ("XML Import Failed")
 End If
End Sub

Workbook_BeforeXmlExport

Syntax: Workbook_BeforeXmlExport (Map, Url, Cancel)

The BeforeXmlExport event occurs before the user exports or saves data to an XML data file. To handle this event, the Workbook_BeforeXmlExport event listener procedure is used which required the following parameters:

  • Map
    The XML schema map that was used to save or export data.
  • Url
    The location of the XML file that was being exported.
  • Cancel
    Can be set to True or False. Setting Cancel to True cancels the save or export operation.

Workbook_BeforeXmlExport Example:

Private Sub Workbook_BeforeXmlExport(ByVal Map As XmlMap, ByVal Url As String, Cancel As Boolean)
 If Map.Name = "pets_Map" Then
  MsgBox ("Sorry, you can not export this file")
  Cancel = True
 End If
End Sub

Workbook_BeforeXmlImport

Syntax: Workbook_BeforeXmlImport (Map, Url, IsRefresh, Cancel)

The BeforeXmlImport event occurs when the user imports new XML data or refreshes existing XML data connection. To handle this event, the Workbook_BeforeXmlImport event listener procedure is used which required the following parameters:

  • Map
    The XML schema map that was used to import data.
  • Url
    The location of the XML file to be imported.
  • IsRefresh
    True if the event was triggered by refreshing an existing connection and False if the event was triggered by importing from a new data source.
  • Cancel
    Can be set to True or False. Setting Cancel to True cancels the import or refresh operation.

Workbook_BeforeXmlImport Example:

Private Sub Workbook_BeforeXmlImport(ByVal Map As XmlMap, ByVal Url As String, ByVal IsRefresh As Boolean, Cancel As Boolean)
 If IsRefresh = False Then
  MsgBox ("Avoid new data connections")
  Cancel = True
 End If
End Sub