Make Excel sheet

[Ian]

I have an Access97 database and I am trying to create an Excel sheet from a
report within this database. I have the following code on the On Click event
of a button on a form:

-----------------------------------------------------

On Error GoTo ErrReport

DoCmd.OutputTo acOutputReport, "SpreadSheetCli entRep",
"microsoftexcel (*.xls)", "", True, ""
Exit_OpenCustRe p_Click:

Exit Sub

ErrReport:

Select Case err

Case 2501 'the open report action was cancelled

Resume Next

Case Else

MsgBox Error$

End Select
Resume Exit_OpenCustRe p_Click

-------------------------------------------------------------------

When I click the button using Access 97 it works perfectly and produces the
spread sheet, however I also need to use the DB in Access 2000 or XP. When I
convert the database to Access 2000 I get the error message:

"The formats that enable you to output data as a MS Excel file were missing
from the Windows registry"

If I open the same 2000 DB with Access XP and click the button I get the
error message:

"The Format in which you are attempting to output the current object is not
available"

My Access 2000 References are (Version that does NOT work):

VB for Apps

Access 9.0 Object Library

DAO 3.6

MS Visual Basic for applications Extensibility 5.3

My Access 97 References are (Version that does work):

VB for Apps

Access 8.0 Object Library

DAO 3.51

Any help would be appreciated.

[Squirrel]

Hi Ian,

I've written code to output to Excel in an Access 2000 database.
Included this statement:
Public gobjExcel As Excel.Applicati on

which is recognized via reference to Microsoft Excel 10.0 Object Library.

Maybe try adding this reference and see if that improves things for you.

Or have you considered this function (I copied this example from the help
files)
DoCmd.TransferS preadsheet acImport, 3, _
"Employees","C: \Lotus\Newemps. wk3", True, "A1:G12"

HTH -Linda
"Ian" <ia********@ntl world.com> wrote in message
news:kQ******** *******@newsfe3-gui.ntli.net...

I have an Access97 database and I am trying to create an Excel sheet from a report within this database. I have the following code on the On Click event of a button on a form:

-----------------------------------------------------

On Error GoTo ErrReport

DoCmd.OutputTo acOutputReport, "SpreadSheetCli entRep",
"microsoftexcel (*.xls)", "", True, ""
Exit_OpenCustRe p_Click:

Exit Sub

ErrReport:

Select Case err

Case 2501 'the open report action was cancelled

Resume Next

Case Else

MsgBox Error$

End Select
Resume Exit_OpenCustRe p_Click

-------------------------------------------------------------------

When I click the button using Access 97 it works perfectly and produces the spread sheet, however I also need to use the DB in Access 2000 or XP. When I convert the database to Access 2000 I get the error message:

"The formats that enable you to output data as a MS Excel file were missing from the Windows registry"

If I open the same 2000 DB with Access XP and click the button I get the
error message:

"The Format in which you are attempting to output the current object is not available"

My Access 2000 References are (Version that does NOT work):

VB for Apps

Access 9.0 Object Library

DAO 3.6

MS Visual Basic for applications Extensibility 5.3

My Access 97 References are (Version that does work):

VB for Apps

Access 8.0 Object Library

DAO 3.51

Any help would be appreciated.

[MacDermott]

Try using
acFormatXLS
instead of
"microsoftexcel (*.xls)

"Ian" <ia********@ntl world.com> wrote in message
news:kQ******** *******@newsfe3-gui.ntli.net...

I have an Access97 database and I am trying to create an Excel sheet from a report within this database. I have the following code on the On Click event of a button on a form:

-----------------------------------------------------

On Error GoTo ErrReport

DoCmd.OutputTo acOutputReport, "SpreadSheetCli entRep",
"microsoftexcel (*.xls)", "", True, ""
Exit_OpenCustRe p_Click:

Exit Sub

ErrReport:

Select Case err

Case 2501 'the open report action was cancelled

Resume Next

Case Else

MsgBox Error$

End Select
Resume Exit_OpenCustRe p_Click

-------------------------------------------------------------------

When I click the button using Access 97 it works perfectly and produces the spread sheet, however I also need to use the DB in Access 2000 or XP. When I convert the database to Access 2000 I get the error message:

"The formats that enable you to output data as a MS Excel file were missing from the Windows registry"

If I open the same 2000 DB with Access XP and click the button I get the
error message:

"The Format in which you are attempting to output the current object is not available"

My Access 2000 References are (Version that does NOT work):

VB for Apps

Access 9.0 Object Library

DAO 3.6

MS Visual Basic for applications Extensibility 5.3

My Access 97 References are (Version that does work):

VB for Apps

Access 8.0 Object Library

DAO 3.51

Any help would be appreciated.