Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 498
  • Last Modified:

MS Access output to excel template

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
kenabbott
Asked:
kenabbott
1 Solution
 
Patrick MatthewsCommented:
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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now