Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MS Access output to excel template

Posted on 2010-09-17
2
Medium Priority
?
489 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

610 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