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

I need to create a button on a form to export the query results to excel. 

Where do I go in access to create this module and have it linked to the button?

I've been using a macro with "export with formatting", but I don't want it to overwrite the whole file.

Thanks for this very helpful information.  I appreciate your patience with someone green behind the ears!

I have tried to use this in my application.  The issue I have found is the code doesn't move the focus to the required sheet name when trying to copy the data from Access to Excel.  If I open the excel workbook and move the focus the the required sheet name, save, exit then execute the code in Access it works.   Looking at the code you posted it should select the sheet name provided in the variable, but it doesn't.  Can you help I need to figure out how to make this work as I have 9 different queries that need to be exported to the same workbook to nine different sheets.  If I can make this work it will save me a ton of time.

I have tried to use this in my application.  The issue I have found is the code doesn't move the focus to the required sheet name when trying to copy the data from Access to Excel.  If I open the excel workbook and move the focus the the required sheet name, save, exit then execute the code in Access it works.   Looking at the code you posted it should select the sheet name provided in the variable, but it doesn't.  Can you help I need to figure out how to make this work as I have 9 different queries that need to be exported to the same workbook to nine different sheets.  If I can make this work it will save me a ton of time.

Which code are you using?  This was a long thread and so long ago it would be easier if you posted what you are using and then we can make some suggestions.

Below is the code i'm using.  I tried modifying the code I found here to work.  I was able to get it to select the specified worksheet name, but then it errors.  I placed comments in the code at the line I edited and the errors received before and after the edit.  By inserting a . select at the end of the **Set xlWSh = xlWBk.Worksheets(strSheetName)**the specified worksheet is selected, but nothing gets copied and it errors out.

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 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)

    Set ApXL = CreateObject("Excel.Application")

    Set xlWBk = ApXL.Workbooks.Open(strPath)

    ApXL.Visible = True

    'Without the .select in this statement the program will not select the specified sheet and gives    'an error 1004 Select method of Range class failed    'with it included the program selects the correct sheet in the workbook but gives an error    '424 Object Required and doesn't copy any of the data    Set xlWSh = xlWBk.Worksheets(strSheetName).Select   

    xlWSh.Range("A1").select

    If rst.RecordCount = 0 Then

    MsgBox "There are no records to export"

    Exit Function

End If

    For Each fld In rst.Fields

        ApXL.ActiveCell = fld.Name

        ApXL.ActiveCell.Offset(0, 1).select

    Next

    rst.MoveFirst

    xlWSh.Range("A2").CopyFromRecordset rst

    xlWSh.Range("1:1").select

    ' 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 ApXL.Selection.Font

        .Name = "Arial"

        .Size = 12

        .Strikethrough = False

        .Superscript = False

        .Subscript = False

        .OutlineFont = False

        .Shadow = False

    End With

    ApXL.Selection.Font.Bold = True

    With ApXL.Selection

        .HorizontalAlignment = xlCenter

        .VerticalAlignment = xlBottom

        .WrapText = False

        .Orientation = 0

        .AddIndent = False

        .IndentLevel = 0

        .ShrinkToFit = False

        .MergeCells = False

    End With

    ' selects all of the cells

    ApXL.ActiveSheet.Cells.select

    ' does the "autofit" for all columns

    ApXL.ActiveSheet.Cells.EntireColumn.AutoFit

    ' selects the first cell to unselect all cells

    xlWSh.Range("A1").select

    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

    Exit Function

End Function

In general, it's better to avoid selecting ranges in Excel VBA code. Try this version.

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) = fld.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