ACCESS: Macro to export query results to specific tab in Excel spreadsheet, whilst overwriting existing contents.

Try this after this line of code:

Set xlWSh = xlWBk.Worksheets(strSheetName)

put this:

xlWSh.Cells.Clear

I love this code.  It works perfect.  I do, however need help with one thing.  If the amount of data is less that the previous time this was run, I end up with leftover data, i.e., I run this and there are 25 records listed on my spreadsheet.  The next time I run it, there are only 20 records.  The spreadsheet is still going to show 25 records, because it only overwrote the first 20 and ignored everything else.  Is there a way of clearing the sheet first, then writing the data?  I realize I could manually clear the sheet, but that defies automation and relies on the several users to remember to clear data.  It also risks them clearing my conditional formatting instead of just data!

tia,

Bryan

That fixed the issue, works perfect now.  Thanks for you help.  Below is a copy of the working code.

Public Function SendTQ2XLWbSheet(strTQName As String, strSheetName As String, strFilePath As String)

' strTQName is the name of the table or query you want to send to Excel

' strSheetName is the name of the sheet you want to send it to

' strFilePath is the name and path of the file you want to send this data into.

    Dim rst As DAO.Recordset

    Dim ApXL As Object

    Dim xlWBk As Object

    Dim xlWSh As Object

    Dim fld As Field

    Dim blnSaveAs As Boolean

    Dim i As Long

    Dim strPath As String

    Const xlCenter As Long = -4108

    Const xlBottom As Long = -4107

    On Error GoTo err_handler

    strPath = strFilePath

    Set rst = CurrentDb.OpenRecordset(strTQName)

    If rst.RecordCount = 0 Then

        MsgBox "There are no records to export"

        Exit Function

    End If

    Set ApXL = CreateObject("Excel.Application")

    Set xlWBk = ApXL.Workbooks.Open(strPath)

    ApXL.Visible = True

    Set xlWSh = xlWBk.Worksheets(strSheetName)

    For i = 0 To rst.Fields.Count - 1

        xlWSh.Cells(1, i + 1) = rst.Fields(i).Name

    Next i

    rst.MoveFirst

    xlWSh.Range("A2").CopyFromRecordset rst

    ' This is included to show some of what you can do about formatting.

    ' You can comment out or delete

    ' any of this that you don't want to use in your own export.

    With xlWSh.Range("1:1").Font

        .Name = "Arial"

        .Size = 12

        .Strikethrough = False

        .Superscript = False

        .Subscript = False

        .OutlineFont = False

        .Shadow = False

        .Bold = True

    End With

    With xlWSh.Range("1:1")

        .HorizontalAlignment = xlCenter

        .VerticalAlignment = xlBottom

        .WrapText = False

        .Orientation = 0

        .AddIndent = False

        .IndentLevel = 0

        .ShrinkToFit = False

        .MergeCells = False

    End With

    ' does the "autofit" for all columns

    xlWSh.Cells.EntireColumn.AutoFit

    rst.Close

    Set rst = Nothing

    ApXL.DisplayAlerts = False

    If Dir(strFilePath) = "" Then

        xlWBk.SaveAs strFilePath

    Else

        xlWBk.Save

    End If

    xlWBk.Close

    ApXL.DisplayAlerts = True

    ApXL.Quit

    Set ApXL = Nothing

    Exit Function

err_handler:

    DoCmd.SetWarnings True

    MsgBox Err.Description, vbExclamation, Err.Number

End Function

Yes, I see now - my bad. Sorry!

Change

    For i = 0 To rst.Fields.Count - 1

        xlWSh.Cells(1, i + 1) = fld.Name

    Next i

to

    For i = 0 To rst.Fields.Count - 1

        xlWSh.Cells(1, i + 1) = rst.Fields(i).Name

    Next i

I can't get this work in debug, but I believe it is in one of the lines below.  As it opens the workbook to the specified sheet name then stops.

For i = 0 To rst.Fields.Count - 1

        xlWSh.Cells(1, i + 1) = fld.Name

    Next i

    rst.MoveFirst

    xlWSh.Range("A2").CopyFromRecordset rst