Transfer Spreadsheet

[mulefeathers]

I am using the following code to export a access table to a spreadsheet.

Function Macro11()
On Error GoTo Macro11_Err
    DoCmd.OpenQuery "spreadsheet1", acViewNormal, acEdit
    DoCmd.TransferSpreadsheet acExport, 8, "qryprodsheet", "C:\Documents and Settings\dr18965\My Documents\shortage.xls", False, ""

Macro11_Exit:
    Exit Function
Macro11_Err:
    MsgBox Error$
    Resume Macro11_Exit
End Function

It opens a make table query then send that table to the spreadsheet. Problem is that it creates a new sheet in the work book instead of just adding the data. Is there a way to force it to use only one sheet? I did this in access 2003 but have since lost that code. This is being done in access 2010.

[June7]

Review:
http://social.msdn.microsoft.com/For...3-a891fe1ae43b