Excel range to dataset

[Guest]

Hi

I have a spreadsheet which I want to create an xml document from a range.

In the past I did this by scrolling through the range and writing the lines
to the document however this was fine but since we "upgraded" to dot.net
(from vba), the speed of this process has dropped off considerably so I need
to find another way around.

What would be nice would be able to get the range into a dataset then to an
xml document.

Does anyone have any ideas on how I could achieve this? (or any idea on how
to speed the process up)

TIA

Mac

 

[Paul Clement]

¤ Hi
¤
¤ I have a spreadsheet which I want to create an xml document from a range.
¤
¤ In the past I did this by scrolling through the range and writing the lines
¤ to the document however this was fine but since we "upgraded" to dot.net
¤ (from vba), the speed of this process has dropped off considerably so I need
¤ to find another way around.
¤
¤ What would be nice would be able to get the range into a dataset then to an
¤ xml document.
¤
¤ Does anyone have any ideas on how I could achieve this? (or any idea on how
¤ to speed the process up)

You should be able to use ADO.NET to do this:

Dim ConnectionString As String

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\Book20.xls;Extended Properties=""Excel
8.0;HDR=NO;IMEX=1"""

Dim ExcelConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
ExcelConnection.Open()

Dim da As New System.Data.OleDb.OleDbDataAdapter("Select * from [ReportSheet$A1:J10]",
ExcelConnection)
Dim ds As New DataSet("ExcelData")

da.Fill(ds, "MyRange")
ds.WriteXml("e:\My Documents\MyRange.xml", XmlWriteMode.WriteSchema)

ExcelConnection.Close()


Paul
~~~~
Microsoft MVP (Visual Basic)