Export to Excel

[Guest]

I am no Access expert but I oversee a database that is having an issue with
exporting to excel. This feature worked when using Access 2000, but when
there was an upgrade to Access 2003, it stopped working.

When I hit the print preview button in the toolbar, I view a PDF version of
all of the data under a specific ID. On the same screen where I hit print
preview, there is a toolbar to export to excel. When I click the export to
excel button, I get an error that says " 429 ActiveX component can't create
object." Does this jump out to anyone as an obvious issue or has someone
experienced this type of error when going from 2000 to 2003? I know I am not
giving a lot of details, but if there are more details that you would like to
know, please ask and I will provide. I am on a form page where I enter the
data when I do this.

Any help would be appreciated? Thanks

 

[Arvin Meyer [MVP]]

Try reregistering DAO:

Start >>> Run

regsvr32.exe "C:\program files\common files\microsoft shared\dao\dao360.dll

 

[Douglas J. Steele]

It's possible that that References collection got messed up when you
upgraded.

References problems can be caused by differences in either the location or
file version of certain files between the machine where the application was
developed, and where it's being run (or the file missing completely from the
target machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, go into the VB Editor. Select
Tools | References from the menu bar. Examine all of the selected
references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by compiling the application, under the Debug
menu), go back in and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html, or check my
December, 2003 "Access Answers" column in Pinnacle Publication's "Smart
Access" (you can download the column, and sample database, for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html)

 

[Guest]

Thanks for the reply. I followed both of the suggestions and now I get a "9
subscript out of range error". Re-registering the dll succeded and the
references checked were Visual basic application, MS Access 11.0 Object
Library, MS DAO 3.6 Library, OLE automation, MS Visual Basic for Applications
Ext 5.3, MS Excel 11.0 Object Library and MS Calendar Control 11.0. I
unchecked as many as I could and then re-checked them (but MS Calendar
Control 11.0 was not on the list any longer).

Does this give you any ideas? Is there something else that should be
checked for Excel?

Thanks

 

[Guest]

Thanks for the reply. I followed both of the suggestions and now I get a "9
subscript out of range error". Re-registering the dll succeded and the
references checked were Visual basic application, MS Access 11.0 Object
Library, MS DAO 3.6 Library, OLE automation, MS Visual Basic for Applications
Ext 5.3, MS Excel 11.0 Object Library and MS Calendar Control 11.0. I
unchecked as many as I could and then re-checked them (but MS Calendar
Control 11.0 was not on the list any longer).

Does this give you any ideas? Is there something else that should be
checked for Excel?

Thanks

 

[Arvin Meyer [MVP]]

You do not need a reference to Excel merely to export data to it. You only
need a reference if you use early binding to interact with Excel methods or
properties.

Try sending the table or query that is the recordsource of your report, to
Excel instead of the report itself. If that works, you can use the button,
or a macro or write code to do it automatically.

 

[Douglas J. Steele]

What's the line of code that's raising the error?

 

[Guest]

This is the code under the Dump to Excel function:

Public Function DumpToExcel(strPrintThis As String)
On Error GoTo DumpToExcel_Err

'***Start of original code.***
'DoCmd.OutputTo acOutputQuery, strPrintThis, acFormatXLS
'***End of original code.***

Dim objXLApp As Excel.Application
Dim objDVPTemplate As Excel.Workbook
Dim objXLSheet As Excel.Worksheet
Dim rst As Recordset
Dim intRow As Integer

Set objXLApp = New Excel.Application
Set objDVPTemplate = CreateObject(conDVPTemplateForED)
Set objXLSheet = objDVPTemplate.Worksheets("ED Testing")

objXLApp.Visible = True
objDVPTemplate.Windows(1).Visible = True

Set rst = Me!tblTests.Form.RecordsetClone
rst.MoveFirst 'to reset the bookmark to the beginning

objXLSheet.Cells(1, 9) = rst![DVP Number] 'DVP Number in header

intRow = 19 'this is the first row on which to put a test letter

Do Until rst.EOF
objXLSheet.Cells(intRow, 1) = "'" & rst![Test Letter] 'Column 1 is
TestID
objXLSheet.Cells(intRow, 2) = "'" & rst![Specifications and Rev
Levels] & _
" " & rst![Methods and Rev Levels]
objXLSheet.Cells(intRow, 3) = "'" & rst![Test Name] & " - " &
rst![Test Description]
objXLSheet.Cells(intRow, 4) = "'" & rst![Acceptance Criteria]

intRow = intRow + 1 'move down to the next row
rst.MoveNext
Loop

objXLSheet.Activate

Set objXLApp = Nothing
Set objDVPTemplate = Nothing
Set objXLSheet = Nothing

DumpToExcel_Exit:
Exit Function

DumpToExcel_Err:
MsgBox Err.Number & " " & Err.Description
Resume DumpToExcel_Exit
End Function

 

[Douglas J. Steele]

I'll ask a second time. What line of code is raising the error?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

This is the code under the Dump to Excel function:

Public Function DumpToExcel(strPrintThis As String)
On Error GoTo DumpToExcel_Err

'***Start of original code.***
'DoCmd.OutputTo acOutputQuery, strPrintThis, acFormatXLS
'***End of original code.***

Dim objXLApp As Excel.Application
Dim objDVPTemplate As Excel.Workbook
Dim objXLSheet As Excel.Worksheet
Dim rst As Recordset
Dim intRow As Integer

Set objXLApp = New Excel.Application
Set objDVPTemplate = CreateObject(conDVPTemplateForED)
Set objXLSheet = objDVPTemplate.Worksheets("ED Testing")

objXLApp.Visible = True
objDVPTemplate.Windows(1).Visible = True

Set rst = Me!tblTests.Form.RecordsetClone
rst.MoveFirst 'to reset the bookmark to the beginning

objXLSheet.Cells(1, 9) = rst![DVP Number] 'DVP Number in header

intRow = 19 'this is the first row on which to put a test letter

Do Until rst.EOF
objXLSheet.Cells(intRow, 1) = "'" & rst![Test Letter] 'Column 1 is
TestID
objXLSheet.Cells(intRow, 2) = "'" & rst![Specifications and Rev
Levels] & _
" " & rst![Methods and Rev Levels]
objXLSheet.Cells(intRow, 3) = "'" & rst![Test Name] & " - " &
rst![Test Description]
objXLSheet.Cells(intRow, 4) = "'" & rst![Acceptance Criteria]

intRow = intRow + 1 'move down to the next row
rst.MoveNext
Loop

objXLSheet.Activate

Set objXLApp = Nothing
Set objDVPTemplate = Nothing
Set objXLSheet = Nothing

DumpToExcel_Exit:
Exit Function

DumpToExcel_Err:
MsgBox Err.Number & " " & Err.Description
Resume DumpToExcel_Exit
End Function
--
TG

I am no Access expert but I oversee a database that is having an issue
with
exporting to excel. This feature worked when using Access 2000, but when
there was an upgrade to Access 2003, it stopped working.

When I hit the print preview button in the toolbar, I view a PDF version
of
all of the data under a specific ID. On the same screen where I hit
print
preview, there is a toolbar to export to excel. When I click the export
to
excel button, I get an error that says " 429 ActiveX component can't
create
object." Does this jump out to anyone as an obvious issue or has someone
experienced this type of error when going from 2000 to 2003? I know I am
not
giving a lot of details, but if there are more details that you would
like to
know, please ask and I will provide. I am on a form page where I enter
the
data when I do this.

Any help would be appreciated? Thanks