• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 691
  • Last Modified:

Export details from an Access 2003.adp database to an Excel spreadsheet - using DoCmd.Transferspreadsheet

I have an Access 2003 .adp frontend database that links to a SQL 2003 backend database. I'm attempting to extract some statistics from the SQL 2003 tablles and export these statistics to an Excel 2003 spreadsheet which in turn are use as a data source for several graphs in a separate worksheet  within the spreadsheet.
I'm using the DoCmd.TransferSpreadsheet, eg
     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "vw1234_sel_ExportStatistics", _
                                                     "c:\MS\Excel\Statistics.xls", True, "A1:G2"
The vw1234_sel_ExportStatistics is a view on the SQL 2003 backend database but each time I execute the DoCmd.TransferSpreadsheet I keep getting an error message indicating the vw1234_sel_ExportStatistics cannot be found.
I have experimented with using a table on the SQL 2003 backend database holding the desired statistics and replacing the table name for the view name in the DoCmd and everying thing works ok .
Can anyone explain why the view won't work for the DoCmd.Spreadsheet while using a table name works correctly and is there a work around I can use as I really don't want to create a temporty table to hold the statistics I need to export  to the Excel spreadsheet.
  • 4
4 Solutions
Stephen DaughertyCommented:
Try using DoCmd.Output to. Specify the format (acFormatXLS) and the path to the file (including filename. Each time you output it will overwrite the previous file.
Stephen DaughertyCommented:
Sorry, that is DoCmd.OuputTo
Stephen DaughertyCommented:
DoCmd.OutputTo acOutputQuery, "YOUR_QUERY_NAME", acFormatXLS,"c:\MS\Excel\Statistics.xls"
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Hi Adlerm,

Excel has a function where you can bypass the need for Access altogether. In Excel choose "Data" -> "Get External Data" -> "Create New Database Query" and follow the wizard.

If you right click on the data when it has imported and choose "Query Properties" you can set it to auto refresh when you open the Excel file, amongst other things.

AdlermAuthor Commented:
Im not sure the DoCmd.OutputTo will achieve what I want. The excel spreadsheet I want to export my statistics to is madel up of several worksheet - which I need to keep. For example on one worksheet are various graphs which I must keep but which inturn are linked to the statistics in the worksheet I want to export to. I'm under the impression - and I may be wrong - that the DoCmd.OutputTo method deletes the entire original spreadsheet and creates a new spreadsheet which as I said above is not acceptable as I want to maintain my worksheet graphs.
While the method suggesred by MalicK may work it is not the preferred method I wish to adopt for a variety of reasons but I thank you for your suggestion.
I did read a number of responses in the EE knowledge base to an number of similar questions and one response clearly indicated that using a query (or view) in the DoCmd.TransferSpreadsheet was not available for an  .adp database. It suggested using a table instead of using a query (view). I did this and the statistics were exported to the spreadsheet as required.
Stephen DaughertyCommented:
Understood. For future references, it may be helpful to those offering assistance to have such inforamtion up front. Based on your initial posting, OutputTo seemed adequate. Anyway, Thanks for the C.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now