DoCmd.TransferSpreadsheet acExport

I am running the following script and it works.  The only thing is that it creates a new sheet in the excel workbook rather than over write the existing worksheet.  I need it to overwrite what is out there,,,
 
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, QryName, _
            "Z:\Documents\GL\Budget_09\Input_Sheets\Data\IS Budget Exp Sheets_2009.xls", True, QryName & "_payroll_roster"
           
Thank you
tom0523Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Patrick MatthewsConnect With a Mentor Commented:
I would try Excel automation:

Dim xlApp As Object, xlWb As Object, xlWs As Object
Dim rs As DAO.Recordset
Dim Counter As Long

Set rs = CurrentDb.OpenRecordset(QryName)
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open("Z:\Documents\GL\Budget_09\Input_Sheets\Data\IS Budget Exp Sheets_2009.xls")
Set xlWs = xlWb.Worksheets.Add
With xlWs
    For Counter = 1 to rs.Fields.Count
        .Cells(1, Counter) = rs.Fields(Counter - 1)
    Next
    .Cells(2, 1).CopyFromRecordset rs
End With

xlWb.Save
xlWb.Close
Set xlWs = Nothing
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing
rs.Close
Set rs = Nothing
0
All Courses

From novice to tech pro — start learning today.