exporting data to excel files

[paulmcdonnell]

HI guys i'm trying to use the following code to export a query to an excel spreadsheet.

Problems is I keep getting the following error:

"variable uses automation line not supported by visual basic"

for the code line:

Set objXL = New Excel.Application


Why is this, my references are correct as far as I know, I'm using access 97 and exporting to excel 2000

Hope you can help

cheers
paul

Dim rs As Recordset
Dim intMaxCol As Integer
Dim intMaxRow As Integer
Dim objXL As Excel.Application
Dim objWkb As Workbook
Dim objSht As Worksheet
Set rs = CurrentDb.OpenRecordset(rsource)
intMaxCol = rs.Fields.count
If rs.RecordCount > 0 Then
rs.MoveLast: rs.MoveFirst
intMaxRow = rs.RecordCount
Set objXL = New Excel.Application
With objXL
.Visible = True
Set objWkb = .Workbooks.Add
Set objSht = objWkb.Worksheets(1)
With objSht
.Range(.Cells(1, 1), .Cells(intMaxRow, _
intMaxCol)).CopyFromRecordset rs
End With
End With
End If
End Sub

 

[Pat Hartman]

I can't comment on the code you posted since I haven't exported using this method. Is there some reason that the TransferSpreadsheet Method would not work for your?

 

[The_Doc_Man]

The very first thing that comes to mind is that you have to have some pretty ugly references.

Access 97 and any member of Office 2000 use different DAO libraries. So this might be one of the dreaded "reference incompatibilities" that crop up now and then.

Having mentioned that, why not try something like

set objXL = CreateObject("Excel.Application")

instead of using the New keyword? (Yes, New implies a "create" - but if it gives you trouble, just come out and TELL it to create a new object.)

 

[Chris RR]

I'm using Access 2000, so this may not help, but do you have a reference to the Microsoft Excel 9.0 object library?