How can I Export Query Results from MS access Into New Excel Template

Question

I want to write a macro on a cmd button on my switchboard which exports all of the results of a certain query and outputs it into a new template in MS Excel

Answer 1

Your best bet is to write a function in a module to handle this.  Here is some code I often use to dump a recordset (query) into excel.

You need to set a reference to Excel and DAO in your modules for this to work correctly.

Public Function excelexport()

    Dim excelrst As DAO.Recordset

    Dim excelsheet As Excel.Application

    'Set Recordset equal to the query you want to output

    Set excelrst = CurrentDb.OpenRecordset("query1")

    

    Set excelsheet = New Excel.Application

'Open the Xcel file you are using as a template

    excelsheet.Workbooks.Open "c:/test.xls"

    With excelsheet.ActiveWorkbook

'Copy query to recordset at range A1

        .ActiveSheet.Range("a1").CopyFromRecords... excelrst

    

'Save updated Excel file to new file

        .SaveAs "c:/test2.xls"

    End With

'Close Excel

    excelsheet.Quit

        

End Function

Hope this helps.

Answer 2

Hi,

Excel can save SQL queries using Data > Get External Data > New Database Query.

You'll be creating a connection via ODBC to the Access MDB file.

How this works is a bit different in each version of Excel, so from here I'd suggest you check excel's help for more info,

I've written a "how-to" do this for Mac Excel, and put the link in the Sources. It's similar on Windows Excel, but the details are different.

-Jim Gordon

http://360.yahoo.com/profile-i7JMeio7cqv...