Check if workbook is opened by another user

[Peter Andersen]

Hallo All,
I have a problem with an older macro that used to work. I want to check if a specific workbook is opened by another user. I have searched and found many treads, but non that solved my problem. So what am I not seeing in my code?

The problem is that I get “Run time error 52 bad file name or number” when the macro reaches the following line code:

Open FullFileName For Input Lock Read As #f

The thing is, when I run the macro without calling the Function, the workbook is opened. So the path and name must be right or?

I don’t know if the enclosed code piece is enough for any one of to see what the problem is. Then please let me know.

the variable Ark is the name of the workbook.

Sub Copy workbook ()
Do stuff
placering = Application.ActiveWorkbook.Path ( the workbook I am looking for is in the same folder as the active workbook)
y = fileIsOpen(placering & "\" & "*" & ark & "*" & ".*")

More stuff
End sub

Function fileIsOpen(FullFileName As String) As Boolean
Dim f As Integer
f = FreeFile
on error resume next
Open FullFileName For Input Lock Read As #f
Close #f
If Err.Number <> 0 Then
FileAlreadyOpen = True
Err.Clear
Else
FileAlreadyOpen = False
End If
On Error GoTo 0
End function

Regards Peter

 

[Marc L]

Hi,​

​

just a very bad file name : can't have "*" in ! …​

​

Before calling function you must verify if the file exists like If Dir("FullPathFileName.Extension") > "" Then …​

​

Regards.​

 

[Peter Andersen]

Hi Marc,

I can open the file using the path and filename shown, but when i use the path and file name in the fileIsopen function I get the error. I only have part of the filename and the path. So how do I check if the file is open?

Regards Peter

 

[NARAYANK991]

Hi Peter ,

Can you check these links ?

1. http://stackoverflow.com/questions/...n-a-file-if-i-only-know-part-of-the-file-name

2. http://www.ozgrid.com/forum/showthread.php?t=13616

Narayan

 

[oldchippy]

Hi Peter,

Does this help in anyway?

http://spreadsheetpage.com/index.php/oddity/who_last_opened_that_file/

 

[Marc L]

I can open the file using the path and filename shown, but when i use the path and file name in the fileIsopen function I get the error. I only have part of the filename and the path. So how do I check if the file is open?

Surely not 'cause it's not a valid filename as the message #52 warned you​

and as you wrote part of the filename !​

So part of a filename is not a valid filename to open a file, that's all folk !​

Just check the true filename …​

 

[Peter Andersen]

Hi again,

I used NARAYANK991 first link and solved my problem, so thanks for that.

What I didn’t realise was that I needed to get the exact filename and path before I used my check function. But that I can open a file using, just part of the file name plus wildcards.

However I have a more robust macro now, using the exact filename.

Thanks for your help.

Regards
Peter