Solved

MS Access output to excel template

Posted on 2010-09-17
2
466 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
Comment Utility
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

744 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

8 Experts available now in Live!

Get 1:1 Help Now