Exporting to an Excel spreadsheet that is already open 1

[KornGeek]

I am using code to export data from my database into Excel using DoCmd.TransferSpreadsheet. Everything has been working wonderfully.

However, today somebody attempted to do the export not realizing that he already had the target spreadsheet open. The result is that Access and Excel both start using approximately 50% of the processing power and stop responding. There seems to be no end to this. If I use Task Manager to kill Excel, cpu usage for Access jumps to roughly 100%, but it still does not recover.

Is there any way to determine if a spreadsheet is already open before attempting to export to it?

 

[PaulBricker]

Take a look at this function.

http://www.xcelfiles.com/IsFileOpen.html

Paul

 

[KornGeek]

Paul,

That looks like exactly what I need. Thanks!

 

[KornGeek]

Paul,

That was exactly what I needed. I was able to implement it successfully and it's perfect. I really appreciate the point in the right direction.

As an interesting side note, I noticed in the Access help files for the TransferSpreadsheet action that it says it the spreadsheet is already open for edit, it will wait until the spreadsheet is available with no timeout. Is it just me, or does this sound like a bad idea?

 

[PaulBricker]

What you say about the time-out doesn't make a lot of sense to me either, but when I read the help file (for 2003 which is what I'm using) it seemed to indicate that that action happened when trying to 'link' to a worksheet that is opened in Edit mode. In other words, it wouldn't complete the link until the worksheet was taken out of Edit mode but it wouldn't time out either. I guess I can see both as being useful.
I only do a couple routines that send data to Excel and it's just for the Business office type people to be able to read easier.

Paul