Solved

query result open in Excel - MS Access

Posted on 2013-05-16
7
373 Views
Last Modified: 2013-05-16
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
Comment
Question by:gtmathewDallas
  • 4
  • 3
7 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39173018
try something like this


private sub Btn_click()

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


end sub
0
 

Author Comment

by:gtmathewDallas
ID: 39173033
Yes it is helpful but is there any way to open the data in excel other than saving?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39173042
yes, but you need to use recordset of the query.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:gtmathewDallas
ID: 39173054
Ok, can you please show me one example with one or two fields? I have 25 fields there in the query result.
Thanks,
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39173061
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
 

Author Comment

by:gtmathewDallas
ID: 39173082
but it will not open the excel sheet right?
0
 

Author Comment

by:gtmathewDallas
ID: 39173093
Sorry - Yes It is :) Thanks a lot..
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

785 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