How to close a currently open Excel file?

[keirnus]

Hello,

I am wondering if this is possible.

I already am able to detect if an Excel file is open or not.
But I don't know how to get its instantiation so I could close
or do quit-process on it.

To explain further, I have a button which creates an excel file
then save it by its pre-defined filename in a pre-defined path.
This excel file must remain open in order for the user to check
the results in it.

If the same button is clicked, error occurs because the file is
already open. That's why it needs to check if the file-to-be-created
is already open. If so, need to close that file so it could create
a new file with new results.

Can anyone teach me how to do this?

 

[georgedwilkinson]

Use the Excel automation object and issue the close command. I don't have the syntax handy right now.

 

[nIGHTmAYOR]

also why not cover the error message with a handler ?

first trap error message number by adding following code top and bottom of your button sub:

On error Goto Err_buttonnamehere_Click
 
Exit_buttonnamehere_Click
Exit Sub
 
Err_buttonnamehere_Click:
MsgBox err.number
resume Exit_buttonnamehere_Click

now when the error occures you have its referance number handy next you edit your sub to detect error occurance and handle it. ex:

On error Goto Err_buttonnamehere_Click
 
Exit_buttonnamehere_Click
Exit Sub
 
Err_buttonnamehere_Click:
If err.number = insert number aquired here Then
    MsgBox "Excel sheet already open, please close it for new updates to load",, "Updates available"
Else
    MsgBox Err.Description
End if
resume Exit_buttonnamehere_Click

 

[keirnus]

also why not cover the error message with a handler ?

first trap error message number by adding following code top and bottom of your button sub:

On error Goto Err_buttonnamehere_Click
 
Exit_buttonnamehere_Click
Exit Sub
 
Err_buttonnamehere_Click:
MsgBox err.number
resume Exit_buttonnamehere_Click

now when the error occures you have its referance number handy next you edit your sub to detect error occurance and handle it. ex:

On error Goto Err_buttonnamehere_Click
 
Exit_buttonnamehere_Click
Exit Sub
 
Err_buttonnamehere_Click:
If err.number = insert number aquired here Then
    MsgBox "Excel sheet already open, please close it for new updates to load",, "Updates available"
Else
    MsgBox Err.Description
End if
resume Exit_buttonnamehere_Click

Thanks georgedwilkinson and nIGHTmAYOR for the reply.

What I already did was the one nIGHTmAYOR adviced.
That's my solution for the mean time.

Got no replies from the client yet since the release.
(except the "new feature works fine as expected")

I was just thinking that it would be better to have no popup msg
for the warning/notice.

I noticed a certain behavior in MS Excel hyperlinks.
Clicking a hyperlink that leads to another excel will close
that already-open excel file and open a new one.
It would be great if I could do that too. =)

 

[nIGHTmAYOR]

You are trying to overwrite not reopen ,windows usually lock opened files (not unless it was one of those buffer/hash technique projects which are not our case) so it wouldnt be possible to overwrite which unless windows release the lock by closeing which

 

[keirnus]

You are trying to overwrite not reopen ,windows usually lock opened files (not unless it was one of those buffer/hash technique projects which are not our case) so it wouldnt be possible to overwrite which unless windows release the lock by closeing which

I can release the lock done by the windows.
...only if I'm a Windows Product developer.

Guess I'm gonna stick to my last implementation.