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?

 

[Gilang]

There is indeed a writing syntax like this:

Private Declare Function PlaySound Lib "winmm.dll" _
Alias "sndPlaySoundA" _
(ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long

Sub TutMelody()
On Error Resume Next
Application.ScreenUpdating = False
Dim sWAVFile As String
sWAVFile = "C:\Windows\Media\Quirky\Windows Navigation Start.wav"
On Error Resume Next
PlaySound sWAVFile, 0
If ERR.Number Then ERR.Clear
Application.ScreenUpdating = True
On Error GoTo 0
End Sub


Does that affect your syntax?

 

[Alex Blakenburg]

I just checked and the errNum = Err should work and not go to uppercase
I don't think I can solve this.

The only thing you can try for the catastrophic error you had and the Err changing to ERR is check if your VBA settings Tools > Reference are the same as mine below. (This is purely on the basis that I am not getting either of those issues.

In your code you have Hosting and that is not a file path and definitely won't work.
I am also not sure what would work on a OneDrive file.

 

[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 use the function to define Hosting so that as shown in the message box like the image, later I will connect it with my OneDrive

 

[Gilang]

I just checked and the errNum = Err should work and not go to uppercase
I don't think I can solve this.

The only thing you can try for the catastrophic error you had and the Err changing to ERR is check if your VBA settings Tools > Reference are the same as mine below. (This is purely on the basis that I am not getting either of those issues.

In your code you have Hosting and that is not a file path and definitely won't work.
I am also not sure what would work on a OneDrive file.

View attachment 33703

I set up a tool like this

 

[Alex Blakenburg]

I set up a tool like this

Are you still getting the Catastrophic Error message ?
If so can you confirm that you have copied the code into a Standard Module and not into either a sheet or Workbook module ?

 

[Gilang]

Are you still getting the Catastrophic Error message ?
If so can you confirm that you have copied the code into a Standard Module and not into either a sheet or Workbook module ?

View attachment 33741

No error message is displayed, but Err is always 0.
I have written in the standard module.

 

[Alex Blakenburg]

Have you run it with the file Data.xlsx open ?
ie open the file in Excel then run the macro.

 

[Gilang]

I have opened it, and run the macro, but the value is still 0, I don't know what happened to my project, but I have done it in another way
I hope it is corrected for the better

Public Sub createPJ()
Dim hst As String
hst = Userform1.LHost.caption
With Application
.ScreenUpdating = False
.DisplayAlerts = False
Dim NewBook As Workbook
    Set NewBook = Workbooks.Add
    With NewBook
        .SaveAs Filename:=Host & "\PJ.text"
        .Close
    End With
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub

'CEK FILE------------------------------------------------------------
Sub ChkData()
Dim FlName As String

FlName = Host & "\PJ.text"

Ulangi:
If Dir(FlName) <> "" Then
    Psn = MsgBox("Busy...," & vbNewLine & vbNewLine & _
            "try again...?", vbInformation + vbYesNo, "BUSY")
            If Psn = vbYes Then
                GoTo Ulangi
            Else
                GoTo Lanjut
            End If
Lanjut:
    Exit Sub
Else
    createPJ
              msgbox "Your data can be copied to the destination data",,"WBK FREE"
    hapusPJ
End If
end sub


Sub HapusPJ()
Dim hst As String
hst = Userform1.LHost.caption
On Error GoTo MsgErr
Dim wbName As String
    wbName = Host & "\PJ.text"
    Kill wbName
Exit Sub
MsgErr:
MsgBox wbName & " tidak ada", , ""
End Sub

 

[Alex Blakenburg]

I hope that works for you.

 

[RoryA]

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

All that means is that you have, or had, something called ERR in your code - a routine, a variable, a module etc.