Enabling Export to Excel on Ribbon

[AOsment]

I'm using Access 2007 and trying to customize a ribbon. I've looked up help on creating the USysRibbon table and even cut and pasted exact code into this table. The tab will show for ExportExcel but it is not enabled. I even created a brand new database with one table and one report. Is there an option somewhere else that has to be set to allow the user to export to excel? I added ExporttoWord and it works fine. Here's the xml code:
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="true">
<tabs> <tab idMso="TabHomeAccess" visible="false"/>
<tab id="dbCustomTab" label="AAA" visible="true">
<group id="DS" label="DS">
<control idMso="PrintPreviewClose" label="Close" enabled="true" size ="large"/>
<control idMso="ExportExcel" label="Export to Excel" enabled="true" size ="large"/>
</group>
</tab>
</tabs>
</ribbon>
</customUI>

 

[NigelShaw]

Hi,

i tried your Xml and exported to excel just fine, this was from a form though. im not sure if you were exporting from a report. consequently, the close button was not enabled.



NS

 

[AOsment]

I am opening the report from a form. I've even tried to move the line of code for export to excel before the close and it is still not enabled.

 

[NigelShaw]

Hi,

according to the help files in Access, you cannot export reports to excel. Only tables, forms & queries. you can however explore DoCmd.TransferSpreadsheet. this will export the data based in a query to excel so look there. as for the ribbon, i would then do the following-

in the Xml, replace you current button with this line-
<button id="ExpExc" label="Export to Excel" imageMso="ExportExcel" onAction="Ribbon.ManualExportToExcel" size="large"/>

then, create an access macro named "Ribbon" and click the macro names button.
Name the macro ManualExportToExcel, set the action to RunCode & set the function to RunManualExportToExcel

create a function called basManualExport

add the following-
Public Function RunManualExportToExcel()

'set your query definition here

Docmd.TransferSpreadsheet etc

End function


when the button in the ribbon is clicked, it will run the access macro that will run the function to export the report.

NS

 

[AOsment]

Thanks for the help. We have a hundred or so applications written in Access that have custom toolbars to export to word and excel. We will be making lots of changes converting to 2007.