Run-time Error '3011' When Exporting Table To Excel

pwigington
11-10-2004, 07:18 AM
Hello,

I am currently creating an Access database to warehouse security information for our associates. I have created a form which allows users to create custom reports based on fields from the EmployeeInfo table. However, some users requested that implement an option to export the report info to Excel. Now, I can easily export the Access report info to Excel, but the Access Report does not pass the label captions to Excel and I end up with column names like "Text1" and "Text2" rather than "FirstName" and "LastName" as shown on the report. Rather than export the report, I thought it would be easier to create a new table and then export that table to Excel. Below is my code:

Create Table:

'Prepare New Table

Dim MyConn As ADODB.Connection
Dim strQuery
Set MyConn = New ADODB.Connection
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\AMS\EmployeeInfo.mdb;" & _
"jet oledb:system database=" & _
"c:\AMS\Secured.mdw;User ID=xphillip;Password=aaron215"
MyConn.Open
MyConn.Execute strCreate
MyConn.Close
MyConn.Open
strQuery = "Insert Into NewTab SELECT " & strData & " FROM EmployeeInfo"
MyConn.Execute strQuery
MyConn.Close


Export Table to Excel:

DoCmd.OutputTo acOutputTable, "NewTab", "*.xls", "C:\Report.xls"
ShellExecute Me.hwnd, vbNullString, "C:\Report.xls", vbNullString, "C:\", SW_ShowMaximized


When I execute this code with a single button click, I receive a Run-Time '3011' Error that states that the Jet DB Engine cannot find the object "NewTab". If I separate this code into two buttons (one to create the table and then one to export to Excel and open the Excel file), it works just fine. Does anyone have any idea what is causing this error? It seems that Access is not registering the creation of the new table until after the Sub has run.

MKoslof
11-13-2004, 09:07 AM
If you want to take a defined query or table in Access and export it to an Excel spreadsheet you can do something like this:

http://www.visualbasicforum.com/showthread.php?t=185218

If formatting and labels are not an issue (just dump the data into Excel) you can use a query such as INSERT INTO or SELECT * INTO and use the Jet 4.0 Provider to send data between the two plaforms(Excel and Access).

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum