I am working with Access 2000 and am trying to export query results to an 
existing Excel file.  The first time that I run the export it works fine, 
BUT if I then close the Excel file and try to run another export from 
Access, things get jammed.  The query runs, a dialogue box appears, and 
Excel starts to open but then quits.  The Excel toolbar appears but that 
is where it stops.  I can close the half opened Excel file and nothing 
else seems to be affected.  I can also avoid the problem if I close the 
Access db and reopen before trying to do a second export.  Is there any 
reason why the export would be working only as a one-shot deal?  Below I 
have included my code.

Thanks for your help!

Shannon

'transfers OpCost query results to Alts Format.xls
DoCmd.OpenQuery "QryOpCost"
DoCmd.TransferSpreadsheet acExport, 
acSpreadsheetTypeExcel8, "qryOpCost", "C:\My Documents\Realest 2002\Alts 
Format.xls", True
DoCmd.Close acQuery, "qryOpCost"
        
MsgBox "Process complete, you may now format the report in the Excel 
spreadsheet 'Alts Format.xls'."
'opens Alts Format.xls
Set ExcelWorksheet = Workbooks.Open("C:\My Documents\Realest 2002\Alts 
Format.xls", , , , , , True)
ExcelWorksheet.Application.Visible = True

Shannon,

Try changing you code to the following sample.  Also you do not have to open
the query before running the export command.

DoCmd.TransferSpreadsheet acExport, 8, "qryOpCost", "C:\My Documents\Realest
2002\Alts_Format.xls", False, ""


Good Luck,
Derrick Flores
San Antonio, TX

----- Original Message -----
From: "Shannon Rice" <rice_shannon@h...>
To: "Access" <access@p...>
Sent: Friday, January 17, 2003 8:15 PM
Subject: [access] Exporting to Excel


>
> I am working with Access 2000 and am trying to export query results to an
> existing Excel file.  The first time that I run the export it works fine,
> BUT if I then close the Excel file and try to run another export from
> Access, things get jammed.  The query runs, a dialogue box appears, and
> Excel starts to open but then quits.  The Excel toolbar appears but that
> is where it stops.  I can close the half opened Excel file and nothing
> else seems to be affected.  I can also avoid the problem if I close the
> Access db and reopen before trying to do a second export.  Is there any
> reason why the export would be working only as a one-shot deal?  Below I
> have included my code.
>
> Thanks for your help!
>
> Shannon
>
> 'transfers OpCost query results to Alts Format.xls
> DoCmd.OpenQuery "QryOpCost"
> DoCmd.TransferSpreadsheet acExport,
> acSpreadsheetTypeExcel8, "qryOpCost", "C:\My Documents\Realest 2002\Alts
> Format.xls", True
> DoCmd.Close acQuery, "qryOpCost"
>
> MsgBox "Process complete, you may now format the report in the Excel
> spreadsheet 'Alts Format.xls'."
> 'opens Alts Format.xls
> Set ExcelWorksheet = Workbooks.Open("C:\My Documents\Realest 2002\Alts
> Format.xls", , , , , , True)
> ExcelWorksheet.Application.Visible = True
>