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

Hi,

I need to write a macro for access, that will export the contents of a query to a specific tab on an excel spreadsheet, whilst overwriting the exiting contents. I will need the code to work on various queries/spreadsheets independantly.

Bob Larson responded to me on a previous thread, and gave me advice, but when I try to replicate the code for another query/spreadsheet, the macro fails.

I wondered if anyone can help me?

Thank you.

Hi Bob,

Using the code you gave me, I can export one query to one excel spreadsheet.

I need to do this on several different queries/spreadsheets, but all independantly.

I have tried just copying the code again, and then setting up a new call, but this does not work.

Any ideas on how I can do this?

Many thanks.

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.

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