Button to Export Data to Excel?

  • Thread starter Thread starter Cam
  • Start date Start date
C

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
 
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.
 
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.
 
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.
 
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
Click to expand...
[quoted text clipped - 8 lines]
Click to expand...

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

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
Click to expand...
[quoted text clipped - 8 lines]
Click to expand...
 
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


Bob Larson via AccessMonster.com said:
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
Click to expand...
[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
Click to expand...
Click to expand...

--
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
Access 2000, 2002, 2003, 2007
Click to expand...
 
But, I would not recommend doing it that way. First, it is a poor
implementation of Automation.
Click to expand...
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.
Click to expand...

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.
Click to expand...
In 10 years of using this like this I have NEVER had that problem.



And, there would be no column headers.
Click to expand...
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?
Click to expand...
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.
Click to expand...
Well, I disagree...

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