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.
Question
SirEvan
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:
but it doesn't seem to work for some reason, I get the following error:
USMC Data is the table name.Thanks in advance.
Link to comment
Share on other sites
2 answers to this question
Recommended Posts