query result open in Excel - MS Access

Dear Experts,
Can you please give me some idea to open a query result in Excel format from my access form - on a button click?
Thanks in advance
gtmathewDallasAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try something like this


private sub Btn_click()

docmd.transferspreadsheet acexport,, "queryName", "C:\folderName\ExcelTest.xls",true


end sub
0
 
gtmathewDallasAuthor Commented:
Yes it is helpful but is there any way to open the data in excel other than saving?
0
 
Rey Obrero (Capricorn1)Commented:
yes, but you need to use recordset of the query.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
gtmathewDallasAuthor Commented:
Ok, can you please show me one example with one or two fields? I have 25 fields there in the query result.
Thanks,
0
 
Rey Obrero (Capricorn1)Commented:
dim xlObj as object, Sheet as object
dim rs as dao.recordset, iCol as integer

    Set xlObj = CreateObject("Excel.Application")
    xlObj.Workbooks.Add
    xlObj.Visible = True
    Set rs = CurrentDb.OpenRecordset("QueryName")
    Set Sheet = xlObj.activeworkbook.workSheets("Sheet1")
        For iCol = 0 To rs.Fields.Count - 1
            Sheet.cells(1, iCol + 1).Value = rs.Fields(iCol).Name
        Next
    Sheet.Range("A2").CopyFromRecordset rs  'copy the data
0
 
gtmathewDallasAuthor Commented:
but it will not open the excel sheet right?
0
 
gtmathewDallasAuthor Commented:
Sorry - Yes It is :) Thanks a lot..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.