 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / March 2008Button to Export Data to Excel?
Hello, I have a form with filtered records. What VBA code/ option can I create a command button to export the filtered records to MS Excel? I tried the button wizard, but did not see any option to export data. Thanks You can do something similar to this: http://www.access-programmers.co.uk/forums/showthread.php?t=145112&highlight=cop yfromrecordset Just assign the form's current recordset instead of creating a recordset object as the sample does. Bob Larson Access World Forums Super Moderator Utter Access VIP >Hello, > >I have a form with filtered records. What VBA code/ option can I create a >command button to export the filtered records to MS Excel? I tried the button >wizard, but did not see any option to export data. Thanks Hi Cam, I know on a report you can do it from the toolbar that dumps it into excel. Try this link: http://office.microsoft.com/en-us/access/HP051866541033.aspx > Hello, > > I have a form with filtered records. What VBA code/ option can I create a > command button to export the filtered records to MS Excel? I tried the button > wizard, but did not see any option to export data. Thanks To export data to Excel, you use the TransferSpreadsheet action or method. It only accepts the names of tables and queries, so it will not accept your form's recordset. The easiest way would be to create a query that mirrors your form's recordset and filtering and use the TransferSpreadsheet.  SignatureDave Hargis, Microsoft Access MVP > Hello, > > I have a form with filtered records. What VBA code/ option can I create a > command button to export the filtered records to MS Excel? I tried the button > wizard, but did not see any option to export data. Thanks Klatuu, I don't mean to export the data to Excel, but rather a command button to analyze the current data in form view to Excel just like the button in the toolbar "Analyze it with Microsoft Office Excel" function. > To export data to Excel, you use the TransferSpreadsheet action or method. > It only accepts the names of tables and queries, so it will not accept your [quoted text clipped - 8 lines] > > command button to export the filtered records to MS Excel? I tried the button > > wizard, but did not see any option to export data. Thanks Interesting, I was responding to: can I create a command button to export the filtered records to MS Excel Didn't anything about analysing, not even in your subject. Sorry, but I don't have an answer for you. I am not familiar with that. SignatureDave Hargis, Microsoft Access MVP > Klatuu, > [quoted text clipped - 14 lines] > > > command button to export the filtered records to MS Excel? I tried the button > > > wizard, but did not see any option to export data. Thanks Did you bother looking at the link that I posted? It would actually be very easy to modify that code to do what you want it to do. >Klatuu, > [quoted text clipped - 7 lines] >> > command button to export the filtered records to MS Excel? I tried the button >> > wizard, but did not see any option to export data. Thanks Signature-- Bob Larson Access World Forums Super Moderator Utter Access VIP Tutorials at http://www.btabdevelopment.com __________________________________ Access 2000, 2002, 2003, 2007 Yes, Bob, I read your post and read the site you posted. First, thanks for letting me know about Analyze with Excel. But, I would not recommend doing it that way. First, it is a poor implementation of Automation. There are serious problems with the posted solution. It does not quit the Xl application, so it will be left running. It explicitly uses CreateObject which, if the user already had an instance of Excel running could confuse Access and maybe close the user's instance and loose her work. And, there would be no column headers. And lastly, why go to all that trouble when a simple TransferSpreadsheet would work? I suggest you rewrite your code. SignatureDave Hargis, Microsoft Access MVP > Did you bother looking at the link that I posted? It would actually be very > easy to modify that code to do what you want it to do. [quoted text clipped - 10 lines] > >> > command button to export the filtered records to MS Excel? I tried the button > >> > wizard, but did not see any option to export data. Thanks >But, I would not recommend doing it that way. First, it is a poor >implementation of Automation. I and many others would disagree with you on that one. >There are serious problems with the posted >solution. It does not quit the Xl application, so it will be left running. If closed the application closes so the user would have to reopen the spreadsheet and that is not what they wanted. They wanted the same functionality as the Analyze with Excel button which sends it to Excel and opens it. >It explicitly uses CreateObject which, if the user already had an instance of >Excel running could confuse Access and maybe close the user's instance and >loose her work. In 10 years of using this like this I have NEVER had that problem. >And, there would be no column headers. Which can be implemented easily enough by iterating through the field names >And lastly, why go to all that trouble when a simple TransferSpreadsheet >would work? Depending on the complexity of the filtering that is being done on the form, it may take more work to build the query instead of just taking the recordset. >I suggest you rewrite your code. Well, I disagree... Signature-- Bob Larson Access World Forums Super Moderator Utter Access VIP Tutorials at http://www.btabdevelopment.com __________________________________ Access 2000, 2002, 2003, 2007 Cam: Exporting to Excel is the same thing as the "Analyze with Excel" >Klatuu, > [quoted text clipped - 7 lines] >> > command button to export the filtered records to MS Excel? I tried the button >> > wizard, but did not see any option to export data. Thanks |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.