Solved

MS Access output to excel template

Posted on 2010-09-17
2
467 Views
Last Modified: 2013-11-27
Hi

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

Ken

0
Comment
Question by:kenabbott
2 Comments
 
LVL 92

Accepted Solution

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

    rs.Close

    Set rs = CurrentDb.OpenRecordset("Query2")

    Set xlWs = xlWb.Worksheets(2)

    xlWs.Cells(2, 1).CopyFromRecordset rs

    rs.Close

    Set rs = CurrentDb.OpenRecordset("Query3")

    Set xlWs = xlWb.Worksheets(3)

    xlWs.Cells(2, 1).CopyFromRecordset rs

    rs.Close



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

    ' for application version.  see for more info:

    ' http://www.dailydoseofexcel.com/archives/2006/10/29/saveas-in-excel-2007/

    

    If Val(xlApp.Version) < 12 Then

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

    Else

        ' 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

    xlApp.Quit

    Set xlApp = Nothing

    Set rs = Nothing

 

    MsgBox "Done"

 

End Sub

Open in new window

0

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