Creating Excel file with Jet OLE DB in VB.NET

[Guest]

I want to be able to create an Excel file with a VB.NET program on a server that does not have Microsoft Excel loaded on it. I am using the Jet OLE DB to read other data files. Can this be used to save an array in an Excel .XLS format?

 

[William Ryan eMVP]

If you don't have Excel, it's going to be a bear. I'd recommend using
FarPoint's Spread. You can use WriteXML of the dataset to save it as XML
and then open it with Excel in you have XP, but short of that you are going
to have to use a third party tool or write the file format AFAIK.

--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups

I want to be able to create an Excel file with a VB.NET program on a

server that does not have Microsoft Excel loaded on it. I am using the Jet
OLE DB to read other data files. Can this be used to save an array in an
Excel .XLS format?

 

[Paul Clement]

¤ I want to be able to create an Excel file with a VB.NET program on a server that does not have Microsoft Excel loaded on it. I am using the Jet OLE DB to read other data files. Can this be used to save an array in an Excel .XLS format?

You can use COM ADOX (Microsoft ADO Ext 2.x for DDL and Security) to create an Excel workbook:

Dim tbl As New ADOX.Table
Dim col As New ADOX.Column
Dim cat As New ADOX.Catalog
Dim cnn As New ADODB.Connection

cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\ExcelWB.xls;Extended Properties=Excel 8.0"
cnn.Open()

cat.ActiveConnection = cnn

tbl = New ADOX.Table
tbl.Name = "TestTable"
col = New ADOX.Column
With col
.Name = "Col1"
.Type = ADOX.DataTypeEnum.adDouble
End With
tbl.Columns.Append(col)
col = Nothing
col = New ADOX.Column
With col
.Name = "Col2"
.Type = ADOX.DataTypeEnum.adVarWChar
End With
tbl.Columns.Append(col)
cat.Tables.Append(tbl)

cat = Nothing


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)