Button to Export Data to Excel?

[Cam]

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

 

[Bob Larson via AccessMonster.com]

You can do something similar to this:
http://www.access-programmers.co.uk/forums/showthread.php?t=145112&highlight=copyfromrecordset

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

 

[Stockwell43]

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

 

[Klatuu]

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.

 

[Cam]

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.

 

[Klatuu]

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.

 

[Bob Larson via AccessMonster.com]

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,

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]

--
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
Access 2000, 2002, 2003, 2007

 

[Bob Larson via AccessMonster.com]

Cam:

Exporting to Excel is the same thing as the "Analyze with Excel"

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]

 

[Klatuu]

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.
--
Dave 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.

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

--
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
Access 2000, 2002, 2003, 2007

 

[Bob Larson via AccessMonster.com]

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...

--
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
Access 2000, 2002, 2003, 2007