Connection string for xml document

Hi. I have two files (file1.xlsm and file2.xlsx) and I want to bring a series of data from file2.xlsx to file1.xlsm, without

opening the first one (file2.xlsx). For this I am using this code, which I run from file1.xlsm:

Sub get_data()

Dim objConnection As Object
Dim objRecordset As Object
Dim strSQL As String

Set objRecordset = CreateObject("ADODB.Recordset")

Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open Join$(Array("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=", _
ThisWorkbook.Path & "\file2.xlsx", ";Extended Properties=""Excel 12.0 Xml; HDR=YES ;"""), vbNullString)

With objRecordset
.Open "SELECT * FROM [Sheet1$A:DA]", objConnection, 3, 2
For i = 0 To objRecordset.Fields.Count - 1
   Workbooks("file1.xlsm").Worksheets("Sheet1").Cells(1, i + 1).Value = objRecordset.Fields(i).Name
Next i
Workbooks("file1.xlsm").Worksheets("Sheet1").Range("A2").CopyFromRecordset objRecordset
End With

objConnection.Close
Set objRecordset = Nothing
Set objConnection = Nothing

End Sub

Now I want to do the same, but substituting file2.xlsx by file2.xml. This is an xml document and I can open it using Excel

directly, so I thought I could use the above code by simply changing the extension, but it is not so.

I could just open the xml file with Excel, and "Save as" Excel file, but I wonder if there is an option with which to modify my

coding so I can get data from a xml file directly using ADO without having to convert it previously.

Regards and many thanks for your help!

0 answers