• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 403
  • Last Modified:

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
0
gtmathewDallas
Asked:
gtmathewDallas
  • 4
  • 3
1 Solution
 
Rey Obrero (Capricorn1)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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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