• 0

[VS 2008 & SQL] Writing contents of Table out to XLS file]


Question

I'm trying to add code to a VB app I'm writing in VS 2008, and one of the features I'm adding is the ability to export the current Database out to a .xls file. So far, I've gotten the basic concept using the following code to do the following:

1.) Create a .xls file

2.) Open it, create a sheet, and add row names, then input data in, then save and close it:

Dim db As ADODB.Connection
Dim rs As ADODB.Recordset

db = New ADODB.Connection
db.Open("DRIVER={MICROSOFT EXCEL DRIVER (*.XLS)};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=""c:\Temp\USMC_Installation_Details.xls"";DBQ=c:\Temp\USMC_Installation_Details.xls")
db.Execute("CREATE TABLE USMCSiteInstallations (ReaderName TEXT,CurrentReaderSN NUMBER)")

db.Execute("INSERT INTO USMCSiteInstallations (ReaderName,CurrentReaderSN) VALUES ('Dave Sell',35)")

What that does is open the new xls file and create two columns, "ReaderName" and "CurrentReaderSN", then put one row in with values "Dave Sell" and "35" respectively under each column. This works beautifully, and gets me 99% of the way to what I want to accomplish.

Here's what I need to change. Instead of specifying column names and values to enter, I want it to export my current table to the file. I tried rewriting the code to be as follows:

Dim db As ADODB.Connection
Dim rs As ADODB.Recordset

db = New ADODB.Connection
db.Open("DRIVER={MICROSOFT EXCEL DRIVER (*.XLS)};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=""c:\Temp\USMC_Installation_Details.xls"";DBQ=c:\Temp\USMC_Installation_Details.xls")
db.Execute("CREATE TABLE USMCSiteInstallations")

db.Execute("SELECT * INTO [Excel 8.0;Database=c:\Temp\USMC_Installation_Details.xls].[Sheet1] FROM [USMC Data]")

when I run it, and select "save as XLS" From my menu, it creates the xls file, opens it, but then errors out on the SELECT statement. I got the code for the select statement from microsoft: http://support.microsoft.com/kb/295646/EN-US/ specifically, the following:

Dim strSQL As String
strSQL = "SELECT * INTO [Excel 8.0;Database=" & App.Path & _ 
	"\book1.xls].[Sheet1] FROM Customers"
cnSrc.Execute strSQL

but it doesn't seem to work for some reason, I get the following error:

[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'USMC Data'. Make sure the object exists and that you spell its name and the path name correctly.
USMC Data is the table name.

Thanks in advance.

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

Nevermind, I found a addon called Spreadsheet from GemBox Software, that does what i want, using the following code (in case anyone else is interested)

' Create new ExcelFile.
		Dim ef2 As New ExcelFile

		' Imports all the tables from DataSet to new file.
		For Each table In USMCDataSet.Tables
			' Add new worksheet to the file.
			Dim ws As ExcelWorksheet = ef2.Worksheets.Add("USMC Installation")

			' Insert the data from DataTable to the worksheet starting at cell "A1".
			ws.InsertDataTable(CType(table, DataTable), "A1", True)
		Next

		' Save the file to XLS format.
		ef2.SaveXls("D:\DataSet.xls")

Gembox Software

Link to comment
Share on other sites

  • 0

ooo, i am interested..

i need something to write some spreadsheets at the moment as i was previously using office automation but that is certainly not recommended..

will take a look!

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.