?
Solved

query result open in Excel - MS Access

Posted on 2013-05-16
7
Medium Priority
?
396 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

771 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