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

I'm in a VBA module. I've probably got it all wrong but here goes:

Public Function SendTQ2XLWbSheet("Jack Powell Completions" As String, "Completions" As String, "O:\Public\Public Users Folders\Jack Powell\Jack Powell Target Sheet 2011.xlsm" As String)

    Dim rst As DAO.Recordset

    Dim ApXL As Object

    Dim xlWBk As Object

    Dim xlWSh As Object

    Dim fld As Field

    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("Jack Powell Completions")

    Set ApXL = CreateObject("Excel.Application")

    Set xlWBk = ApXL.Workbooks.Open("O:\Public\Public Users Folders\Jack Powell\Jack Powell Target Sheet 2011.xlsm")

    ApXL.Visible = True

    Set xlWSh = xlWBk.Worksheets("Completions")

    xlWSh.Range("A1").Select

    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

End Sub

No, you would copy the entire code (UNCHANGED) into a standard module (not form or report module) and name the module something like basExcelExport (just needs to be different from the procedure name).

Then you would call it in your code or macro using

SendTQ2XLWbSheet "Jack Powell Completions", "NameOfTheWorksheetHere", "PathAndNameOfTheWorkBookHere"

so if the name of the worksheet was MySheet and the path and file was C:\Temp\JackPComp.xlsx" you would use

SendTQ2XLWbSheet "Jack Powell Completions", "MySheet", "C:\TempJackPComp.xlsx"

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

Could you give me an example of full code?

Thanks.

You can try my code here:

http://www.btabdevelopment.com/ts/tq2xlspecwspath

But if the data size can be different (rows and/or columns) then you will want to clear the sheet first.

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

Try specifying the name of the sheet followed by $ (e.g. Sheet1$) in the Range argument of TransferSpreadsheet.