MS Access output to excel template

Posted on 2010-09-17
Last Modified: 2013-11-27

I need to achieve the following

1. Automatically output a query from Access
2. Insert data into Excel spreadsheet based on a template
3. Each part of the data to be inserted into specific cells in the spreadsheet
4. There a will be a number of queries the data from each to go into the same spreadsheet (the procedure to do this will do this all in one go)

many thanks


Question by:kenabbott
LVL 92

Accepted Solution

Patrick Matthews earned 500 total points
ID: 33700568
kenabbott,You're not being very specific, so it is hard to know what you are after.Here is an example that writes the output of three different queries to different worksheets in an already-created Excel template (file type xlt, xltx, or xltm).Patrick
Sub ExportToExcel()


    ' uses late binding for Excel


    Dim rs AS DAO.Recordset

    Dim xlApp As Object

    Dim xlWb As Object

    Dim xlWs As Object

    Dim Counter As Long


    Const TemplatePath As String = "c:\folder\subfolder\MyTemplate.xlt"

    Const SaveToPath As String = "c:\Results\Report_"

    Const QueryName As String = "NameOfQuery"


    ' instantiate Excel object


    Set xlApp = CreateObject("Excel.Application")

    xlApp.DisplayAlerts = False

    Set xlWb = xlApp.Workbooks.Add(TemplatePath)

    Set rs = CurrentDb.OpenRecordset("Query1")

    Set xlWs = xlWb.Worksheets(1)

    xlWs.Cells(2, 1).CopyFromRecordset rs


    Set rs = CurrentDb.OpenRecordset("Query2")

    Set xlWs = xlWb.Worksheets(2)

    xlWs.Cells(2, 1).CopyFromRecordset rs


    Set rs = CurrentDb.OpenRecordset("Query3")

    Set xlWs = xlWb.Worksheets(3)

    xlWs.Cells(2, 1).CopyFromRecordset rs


    ' Excel 2007/2010 requires the file format to be specified, so check

    ' for application version.  see for more info:



    If Val(xlApp.Version) < 12 Then

        xlWb.SaveAs SaveToPath & Format(Now, "yyyymmdd") & ".xls"


        ' to use XLSX format:

        xlWb.SaveAs SaveToPath & Format(Now, "yyyymmdd") & ".xlsx", 51

        ' or to use XLSM format:

        'xlWb.SaveAs SaveToPath & Format(Now, "yyyymmdd") & ".xlsm", 52

        ' or to use XLSB format:

        'xlWb.SaveAs SaveToPath & Format(Now, "yyyymmdd") & ".xlsb", 50

        ' or to use good old XLS format:

        'xlWb.SaveAs SaveToPath & Format(Now, "yyyymmdd") & ".xls", 56

    End If


    xlWb.Close False

    xlApp.DisplayAlerts = True

    Set xlWs = Nothing

    Set xlWb = Nothing


    Set xlApp = Nothing

    Set rs = Nothing


    MsgBox "Done"


End Sub

Open in new window


Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now