Close specific Excel spreadsheet using VB Code in Access

[eighthman11]

Hello everyone: Using Access 2003; Excel 2003 and Window XP

Ok here it goes. I import an excel spreadsheet to an Access table
using a "DoCmd.TransferSpreadsheet acImport". This works whether the
excel spreadsheet is open or not which is great.

Here's the problem. After I import the spreadsheet I have code that
make a copy of the spreadsheet; names the copy with a timestamp and
places it in a folder called "Backup".

I then have code that deletes the old spreadsheet that was imported so
the user does not try to import it again. This all works great with
code in my Access application EXCEPT if the user has the original
excel spreadsheet open. Then I can not make a copy of it or delete it
with code.

What I need is some code that tells me if a specific workbook is
open. It will always have the same name. If it is open I need code
to close the specific workbook (not all open workbooks) before I do
the copy and the delete.

I have scanned previous subjects on this matter and to tell you the
truth the answers were a little above my skill level. Any help would
be appreciated. Thanks. Merry Christmas

 

[Mark Andrews]

As a guess, you could have code try and rename the excel file to determine
if it's open. If the rename succeeds then the file is closed, otherwise
it's open.

or something that just in general traps the error that occurs when it fails
because the file is open.

HTH,
Mark