show ms box if the xls file is opened...
i have a .xls file into a dir server, with all free permission on this dir, inested the tipical ms message "file is alreday opend from..." when a user open a xls file if other user have opened the same, is possible:
to show a mshbox:
Attention this file is in use from & "name of user have opoened the file for first" click on msg box close the .xls file...
i use this function to know wich user have opened the .xls file:
Sub USER_NAME()
Dim wshNet
ThisWorkbook.Activate
Sheets("RATE").Select
Set wshNet = CreateObject("WScript.Network")
wshGetUserName = wshNet.UserName
Set wshNet = Nothing
Range("A1") = ""
Range("A1") = UCase(wshGetUserName)
End Sub
Re: show ms box if the xls file is opened...
I don't think you want to close a file that someone has been working on. It would cause them to lose their work!
Re: show ms box if the xls file is opened...
I think luca90 just want to show the msgbox, the same way that Excel does,
and if the file is opened by another user then show the msgbox with the name of that user and close. He don't want to close the file opened by the other user, he wants to close the file in the local machine, finally it would be closing the Excel App I think.
Excel also gives the posibility of opening a new copy of the workbook.
Re: show ms box if the xls file is opened...
Quote:
Originally Posted by jcis
I think luca90 just want to show the msgbox, the same way that Excel does,
and if the file is opened by another user then show the msgbox with the name of that user and close. He don't want to close the file opened by the other user, he wants to close the file in the local machine, finally it would be closing the Excel App I think.
Excel also gives the posibility of opening a new copy of the workbook.
Yes.... perfect!
how to do this?
Re: show ms box if the xls file is opened...
Not much in the forums about this, the closer I could get with some searches:
Look here post #3, by Robdog:
Another thread
Re: show ms box if the xls file is opened...
Maybe you could use GetUserName API and comapare with the names you already have, if there is an user using the Workbook, and his Username is diferent than the active username you could show that username in the Msgbox as you want, and close the workbook.
GetUserName Example, Im not sure if this is in the same format that the usernames you already have..
VB Code:
Option Explicit
Private Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Private Function GetActiveUserName() as String
Dim UserName As String, nSize As Long
Call GetUserName(vbNullString, nSize)
UserName = Space(nSize)
Call GetUserName(UserName, nSize)
GetActiveUserName = UserName
End Function
Re: show ms box if the xls file is opened...
Quote:
Originally Posted by jcis
Maybe you could use GetUserName API and comapare with the names you already have, if there is an user using the Workbook, and his Username is diferent than the active username you could show that username in the Msgbox as you want, and close the workbook.
GetUserName Example, Im not sure if this is in the same format that the usernames you already have..
VB Code:
Option Explicit
Private Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Private Function GetActiveUserName() as String
Dim UserName As String, nSize As Long
Call GetUserName(vbNullString, nSize)
UserName = Space(nSize)
Call GetUserName(UserName, nSize)
GetActiveUserName = UserName
End Function
not for me...;-(
Example please, tks.
Re: show ms box if the xls file is opened...
what? GetUserName doesn't work?
Re: show ms box if the xls file is opened...
Quote:
Originally Posted by jcis
what? GetUserName doesn't work?
...no work fine
But not have a knoldedgmet to make the vba code:-(
Re: show ms box if the xls file is opened...
I really don't know much about VBA. Maybe someone who knows, see this post now. By the way, there is a VBA forum too.
Re: show ms box if the xls file is opened...
I dont quite get the problem as Excel displays a dialog prompt if you attempt to open a file that is already opened by another user. It gives you the Notify or Open as Read-Only options. It also identifies the user that has it opened first (exclusively).