How do I export a report to Excel with VBA? 1

[steve728]

How do I change the following to instead be exported to Excel with VBA? I'm using WinXP, Office 2003.

DoCmd.OpenReport "ISR Problems"

Steve728

 

[lameid]

I didn't find anything looking for it exactly except that there is a statement that will export...

docmd.RunCommand acCmdExport


The catch is that the object had better be active that you want to export. So better to open it immediately before.

DoCmd.OpenReport "ISR Problems"
docmd.RunCommand acCmdExport


The caveat here is that it will only prompt to save and select the file type.

On the otherhand you can export a query or table using docmd.transferspreadsheet.

 

[steve728]

Thanks! You get a star. Am I close to creating code to export a Access table to Excel with:

DoCmd.TransferDatabase acExport, "Microsoft Access", "ISR.mdb",acTable, "tblISRProblems"

Within the on-line help, I couldn't find what agrument to
use specifying Excel.???

Steve728

 

[lameid]

Transerspreadsheet not Transferdatabase...

The below is close, just fix "FilenameAndPath.xls".

docmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9,"tblISRProblems","FilenameAndPath.xls",True