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

No, still no luck.

May I can run one query for all year, export the query, then filter by months in excel.

I have a tab for each month and I have checked that they contain no spaces.

Maybe I should try the Jan & Feb combined call and see if that works?

Does the tab for Feb actually exist in that worksheet?  If not, it needs to be.  If so, you have to have the right name (no spaces in the worksheet name which is sometimes hard to tell without checking closely.

---

Bob Larson, Former Access MVP (2008-2010) http://www.btabdevelopment.com (free Access tools, tutorials, and samples)

I'm so sorry, but that still hasn't worked.

I changed the module code per above.

I added a new call for Feb:

Private Sub Image58_Click()

Call SendTQ2XLWbSheet("JP Completions Feb", "Feb", "O:\Public\Public Users Folders\Jack Powell\Jack Powell Target Sheet 2011.xlsm")

End Sub

It still copies nothing, and I get the same error as above.

Ah, now I get it.  Because I didn't make the function able to save there is no workbook created and saved it is only open.  So, use this function (substitute the old one with this new one):

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

    Set xlWSh = xlWBk.Worksheets(strSheetName)

    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

---

Bob Larson, Former Access MVP (2008-2010) http://www.btabdevelopment.com (free Access tools, tutorials, and samples)