DoCmd.TransferSpreadsheet to Unsaved Open Excel Workbook?

[gypsyjoe11]

The end user opens up an Excel sheet from a website and then presses a button on a form to bring the data into my Access 2003 table. Then when they press another button a query is run and output to another Excel sheet. I want to save the user from having to save the workbook before my code will work.

I thought of using DoCmd.TransferSpreadsheet but the only examples are of it working with a file already saved on the hard drive. I want to read and write to an open Excel application and not have to save to a file first.

I ended up writting my own code to do this using objects and it works great. My question is does anyone know of a way to do this using DoCmd?

thanks in advance for your help,

Joe

 

[SimonB1978]

Hi,

I think that when you open a document (.doc, .xls, etc) from a website a copy of it is saved in a "temporary" location.

With that location you could probably used docmd, as long as the file name is always the same...

But honestly, if you are to write to the Excel file, you're most likely going to save it, so why not save it first?

Simon B.

 

[gypsyjoe11]

Thanks for the advice Simon,

I'll check into that temporary file. When it is output to Excel, each user has their own filename they want to use. Also some of the options are for a quick view and they wouldn't want to save those. Transfering the Excel sheet to Access never needs to be saved.

It was interesting finding out how to read/write from an open Excel sheet because all the examples were for an already saved file.

thanks again,

Joe