Shared workbook

[Gilang]

I have a workbook that is turned into a data center, while the data processing excel file resides on a different computer. After each data is processed, all results are copied to the central workbook.
Can I find out that a datacenter workbook is still open on another computer with a macro message?
if possible can anyone help me with the syntax?

 

[Alex Blakenburg]

You can try this:
You just need to replace the FileToOpen value with your full filename.

Sub FileOpenMsg()
    Dim FileToOpen As String
    
    FileToOpen = "C:\temp\file_to_open.xlsx"
    
    If IsFileOpen(FileToOpen) Then
        MsgBox "File is open"
    Else
        MsgBox "File is free"
    End If

End Sub

 
Function IsFileOpen(fName As String) As Boolean
    Dim ff       As Integer
    Dim errNum  As Integer
 
    On Error Resume Next
    ff = FreeFile
    Open fName For Input Lock Read As #ff
    Close ff
    errNum = Err
    On Error GoTo 0
    IsFileOpen = (errNum <> 0)
End Function

 

[Gilang]

Terima kasih atas solusinya, saya akan mencoba menerapkannya nanti karena untuk saat ini saya jauh dari komputer.

 

[Fluff]

@Gilang
Please post to this board in English only. If you prefer to use your own language then you need to post in the Questions in Other Languages section

 

[Gilang]

Thanks for the solution, I will try to implement it later as for now I am away from the computer.
Sorry for my reply yesterday via cellphone so that it is automatically translated into Indonesian before sending.

 

[Gilang]

You can try this:
You just need to replace the FileToOpen value with your full filename.

Sub FileOpenMsg()
    Dim FileToOpen As String
  
    FileToOpen = "C:\temp\file_to_open.xlsx"
  
    If IsFileOpen(FileToOpen) Then
        MsgBox "File is open"
    Else
        MsgBox "File is free"
    End If

End Sub


Function IsFileOpen(fName As String) As Boolean
    Dim ff       As Integer
    Dim errNum  As Integer

    On Error Resume Next
    ff = FreeFile
    Open fName For Input Lock Read As #ff
    Close ff
    errNum = Err
    On Error GoTo 0
    IsFileOpen = (errNum <> 0)
End Function

I've tried it, the macro runs but an error message box appears.
I am including the screenshort.
Is there anything lacking in that syntax?
Please find a solution

 

[Alex Blakenburg]

I've tried it, the macro runs but an error message box appears.
I am including the screenshort.
Is there anything lacking in that syntax?
Please find a solution

The macro needs the full path and file name of the file you are trying to check.
Unless you have defined what Hosting means (eg Hosting = "FilePath\Filename"), the macro has no idea what this is.

Based on the screen shot "File Location", I think you are going to find checking the file is not going to be a simple matter.
You indicated you wanted to check a file at the data center, however the File Location given is a OneDrive file and the address is the file on someone's C drive.
This is a copy of the server file on an individual's computer. I don't think you will be able to access that nor will it tell you if the server copy is open.

If you can get the full server FilePath & FIleName you could try that but accessing OneDrive & Sharepoint files is particularly challenging.
If its a standard Network stored file then the function should work as long as you have to full name.

 

[Gilang]

The macro needs the full path and file name of the file you are trying to check.
Unless you have defined what Hosting means (eg Hosting = "FilePath\Filename"), the macro has no idea what this is.

Based on the screen shot "File Location", I think you are going to find checking the file is not going to be a simple matter.
You indicated you wanted to check a file at the data center, however the File Location given is a OneDrive file and the address is the file on someone's C drive.
This is a copy of the server file on an individual's computer. I don't think you will be able to access that nor will it tell you if the server copy is open.

If you can get the full server FilePath & FIleName you could try that but accessing OneDrive & Sharepoint files is particularly challenging.
If its a standard Network stored file then the function should work as long as you have to full name.

I have a problem changing the Err syntax but it immediately changes to ERR, please give me a solution

 

[Alex Blakenburg]

I think you are after:
errNum = err.number

 

[Gilang]

I think you are after:
errNum = err.number

What do you mean :
errNum = Err.Number in other syntax?
Can you help me to fix it?
I really need your help