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

You don't copy the module again.  As Scott said, you call it from wherever you want.  So, if you want multiple queries in one click of a button you would use:

Private Sub Image57_Click()

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

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

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

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

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

End Sub

and so on.  Do you get it now?

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

This is what Bob gave me, and it works, but only for one query to spreadsheet. When I copy both parts, and amend the call, it says the module is ambiguous.

CALL AS FOLLOWS:

Private Sub Image57_Click()

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

End Sub

MODULE AS FOLLOWS:

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

    Exit Function

err_handler:

    DoCmd.SetWarnings True

    MsgBox Err.Description, vbExclamation, Err.Number

    Exit Function

End Function

If you post your code and where it went wrong we can try to help.

It was this thread:

http://social.answers.microsoft.com/Forums/en-US/addbuz/thread/023fca07-997f-4d0d-8ccc-96c238a7b4ba

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

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.

For each Query/Spreadsheet combination you need to execute a new call statement with the varying data. As Bob said, the function is a generic one that you pass parameters to. So each time you call the function (which you only need one of) you pass it a different set of parameters so it works with a different query.

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.wordpress.com Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

You can't do this in a macro. You need to use VBA coding with Office Automation to open your Excel sheet and place the data into it. If you post your code and where it went wrong we can try to help.

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.wordpress.com Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries