Solved: transferspreadsheet method

[philfer]

I am using the following to export query results from Access to Excel

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Hello", "C:\data\phil.xls"

However it says it cant find the objext phil.xls

When I replace this with a new book i.e. Book4.xls which is closed it works OK and creates a new sheet with the same name as the query and drops in the data.

When I put the code in Book4 and point it at phil.xls as long as phil.xls is closed it works.

Why is this happening.

Also rather than having it create a new sheet is there any way I can make it paste the data into an existing sheet in phil.xls each time I run the code

Any help would be appreciated

Thanks

[rory]

Are you running the code from Phil.xls?
You don't get that much control using TransferSpreadsheet - you would need to automate Excel from Access or use ADO (or DAO) from Excel to get the data and drop it into a worksheet.

[ALe]

It isn't clear wheter you run your code from access or excel...anyway
here an article about all the methods to transfer data from access to excel so that you can see how use ADO and DAO.

http://www.zmey.1977.ru/Access_To_Excel.htm

You could use the copyfromrecordset method to transfer data while file is open. I haven't tried but it should work.

Regards

[philfer]

Thanks for that link it was very helpful

My code is in Excel so if there is a similar link to help from an Excel point of view that would be great

[ALe]

code can be executed from excel as well as from access. Just make a reference in excel to the access library to use access objects and methods.