Listing 3: Accessing a Microsoft Excel Spreadsheet

' Declare connection and connection string variables
Dim conExcel, csExcel

' Declare command and command text variables
Dim cmdContacts, ctContacts

' Declare recordset variable
Dim rsContacts

' Declare argument constants
Const adPersistXML = 1
Const adStateOpen = 1

' Create Connection object
Set conExcel = CreateObject("ADODB.Connection")

' Define connection string
csExcel = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=C:\Info\Contacts.xls;" & _
  "Extended Properties=Excel 8.0"

' Open connection    
conExcel.Open csExcel
       
' Create Command object and set the connection
Set cmdContacts = CreateObject("ADODB.Command")
Set cmdContacts.ActiveConnection = conExcel

' Define and set the command text
ctContacts = "SELECT * FROM [Contacts$A1:B11]"
cmdContacts.CommandText = ctContacts

' Create and populate recordset object
Set rsContacts = CreateObject("ADODB.Recordset")
rsContacts.Open cmdContacts

' Save data to XML file 
rsContacts.Save "C:\Info\ExcelContacts.xml", adPersistXML

' Clean up
If rsContacts.State = adStateOpen then
  rsContacts.Close
End If

If conExcel.State = adStateOpen then
  conExcel.Close
End If

Set conExcel = Nothing
Set csExcel = Nothing
Set cmdContacts = Nothing
Set ctContacts = Nothing
Set rsContacts = Nothing