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

I've tried putting them in so far, and I am getting an error as follows:

Compile error:      Expected: Identifier

Help!!!

I've tried putting them in so far, and I am getting an error as follows:

Compile error:      Expected: Identifier

Help!!!

Are you trying to call it from a macro or VBA code?  Also, post what you have currently.  If in VBA post the code you are using to call it.  If you are calling it from a function post what you have and how you have the arguments if using the RunCode macro action.

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

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

Okay, first off.  I TOLD you NOT TO MODIFY THE FUNCTION you got from my website AT ALL. Do NOT CHANGE ANYTHING.  Just copy and paste it into a standard module.

You then CALL the function like this from a DIFFERENT EVENT or macro:

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

Again DO NOT MESS WITH THE FUNCTION AT ALL.  It is a generic function written to handle anything you throw at it, but you must CALL the function and PASS the parameters, you do not modify the function.

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

I'm sorry, I misundestood you. It now works, however is there anyway of stopping it coming up with this error at the end of the process?

3021 : No current record

Thank you.