Get username w/ API

[Zack Barresse]

Okay, I have been playing around with some of Ivan F. Moala's code from his website (full code found here). The code is designed to see if a file is open and who has it open. This works over a network - which is where I'm using it (Workgroup, NOT a Domain).

The problem I'm having, as everything except this is working fine, is getting the UserName brought back of whoever does have it open, as only one of the file is allowed to be opened at any one time.



Variables declared:
[vba]
Public Declare Function lOpen _
Lib "kernel32" _
Alias "_lopen" ( _
ByVal lpPathName As String, _
ByVal iReadWrite As Long) _
As Long
Public Declare Function lClose _
Lib "kernel32" _
Alias "_lclose" ( _
ByVal hFile As Long) _
As Long

Public Const OF_SHARE_COMPAT = &H0
Public Const OF_SHARE_DENY_NONE = &H40
Public Const OF_SHARE_DENY_READ = &H30
Public Const OF_SHARE_DENY_WRITE = &H20
Public Const OF_SHARE_EXCLUSIVE = &H10
[/vba]



Code in question:
[vba]
Public Function LastUser(strPath As String) As String
Dim strXl As String
Dim strFlag1 As String, strflag2 As String
Dim i As Integer, j As Integer
Dim hdlFile As Long
Dim lNameLen As Byte
strFlag1 = Chr(0) & Chr(0)
strflag2 = Chr(32) & Chr(32)
hdlFile = FreeFile
Open strPath For Binary As #hdlFile
strXl = Space(LOF(hdlFile))
Get 1, , strXl
Close #hdlFile
j = InStr(1, strXl, strflag2)
#If Not VBA6 Then
'// Xl97
For i = j - 1 To 1 Step -1
If Mid(strXl, i, 1) = Chr(0) Then Exit For
Next
i = i + 1
#Else
'// Xl2000+
i = InStrRev(strXl, strFlag1, j) + Len(strFlag1)
#End If
'// IFM
lNameLen = Asc(Mid(strXl, i - 3, 1))
LastUser = Mid(strXl, i, lNameLen)
End Function
[/vba]

Please note that this is not the entire code, but the only part that is not working. I can post all of the code if you really like (it is on the link posted up top also). Thanks for taking the time to read this!

[Ken Puls]

Hi Firefytr,

Just out of curiosity, aren't you supposed to post a little more details of where the code is erroring out? Maybe your Excel version? :rofl

Seriosuly though, reading Ivan's code generally takes me though a 4 step process...


I'm stuck between 2 and 3 right now...

I did notice from Ivan's post that he refers back to the ExtremeVB post where some of the code comes from... http://www.visualbasicforum.com/showthread.php?t=160978
In his original version, the IsFileOpen and LastUser functions were separated, and he's consolidated them in the post that is on his site. I wonder if you may want to split it up to pin down the issue a little further.

I am curious to see an answer on this as well. And why are there # signs in If/Then/Else statement?

Cheers,

[Zack Barresse]

Hi,

Thanks Ken. As far as versions go, I'm testing on 2002 and will also be on 2003 (but haven't got that far yet). And the code doesn't actually error out anywhere. It runs very smooth right now. It just brings back a null value everytime ( "" ). I did add a condition to check the name for a null, if so, assign it to "UNKNOWN". This looks better (it pops up in a message box) then just a blank.

I must have done something wrong in copying his code or reading his site because I couldn't find a post number for that post at XtremeVB. Thanks for that! As the file is for my work (replacing the last function I had on my latest add-in to use over a network) I'll wait to test this until Monday or Tuesday, depending on time. But I will let you know how it goes.

And as far as I know (which I know, isn't much), the # signs in front are for VB.

[Ivan F Moala]

Hi Zack, when I first did this code it was to Test for any file that was Open.
Over time it changed to check for XL files and who has it open. If you are testing for any other file other then an Excel file you won't get the User name, although you will be able to get a result to tell you if it is Open.

If this is not the case and you are testing for an Excel file then try the VB method = Non API ..... or perhaps post what you have ?

[Zack Barresse]

Thanks Ivan! I am checking for an Excel file. A singular file in fact where I've *hardcoded* the Path and Name. I will try the Non API method come tomorrow (file is at work). Thanks for looking at this.

[Zack Barresse]

Hello again.

I tried the Non-API version and it didn't seem to work. It would say it was open no matter what. This is using version 2002 on Win XP (SP2). I'm going to fiddle with it some more.

[mark007]

There is a similar piece of code on my site at:

http://www.markrowlinson.co.uk/articles.php?id=9

It derives from the very same thread but doesn't use any API's. Could be identical to Ivan's really! Just thought I'd post the link to see if it sheds any light on your problem....

[eliasarbex]

Gentlemen,

I've tried mark's and ivan's code, but any of them worked. Both returned a sequence of weird symbols.

My Excel version is 2010.

Regards,

[Rob342]

Zack
Read you post, found a bit of code that might work or could incorporate with yours. nb not tested though
[vba]
Private Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" _
(ByVal IpBuffer as string, _
ByRef nSize As Long) As Long
'get user logon id
Function UserName() As String
Dim sBuffer As String * 255
Dim IStringLength As Long
IStringLength=Len(sBuffer)
'Call API function
GetUserName sBuffer, IStringLength

If IStringLength > 0 Then
Username=Left$(sBuffer, IStringLength -1)
End If

End Function
[/vba]

[Paul_Hossler]

The #If etc. are for conditional compliles


[vba]
#If Not VBA6 Then
'// Xl97
For i = j - 1 To 1 Step -1
If Mid(strXl, i, 1) = Chr(0) Then Exit For
Next
i = i + 1
#Else
'// Xl2000+
i = InStrRev(strXl, strFlag1, j) + Len(strFlag1)
#End If
[/vba]


From Help (2010, but should be the same

You can use conditional compilation to run blocks of code selectively, for example, debugging statements comparing the speed of different approaches to the same programming task, or localizing an application for different languages.


You declare a conditional compiler constant in code with the #Const directive, and you denote blocks of code to be conditionally compiled with the #If...Then...#Else directive. The following example runs debug code or production code, based on the value of the conDebug
variable.


' Declare public compilation constant in Declarations section.#Const conDebug = 1Sub SelectiveExecution() #If conDebug = 1 Then . ' Run code with debugging statements. . . #Else . ' Run normal code. . . #End IfEnd Sub

My guess is that VBA6 is not 'defined' and the Else code is for another version)

XLD always comes through: http://www.vbaexpress.com/forum/showthread.php?t=28252

Paul

[eliasarbex]

Paul,

thanx for the quick answer. I changed 'Project Properties' and code to be as you posted but the answer now is: "".

If you could check the code below I would apreciate.


[VBA]
Sub teste()
a = LastUser("\\SERVIDOR\ARQUIVOS\SEQUENCIAS_MAQUINAS\SISTEMAS\CONTROLE_OFS.xls m")
End Sub

Public Function LastUser(strPath As String) As String
Dim strXl As String
Dim strFlag1 As String, strflag2 As String
Dim i As Variant, j As Variant

Dim hdlFile As Long
Dim lNameLen As Byte
strFlag1 = Chr(0) & Chr(0)
strflag2 = Chr(32) & Chr(32)
hdlFile = FreeFile
Open strPath For Binary As #hdlFile
strXl = Space(LOF(hdlFile))
Get 1, , strXl
Close #hdlFile

j = InStr(1, strXl, strflag2)

#If Not vba6 Then
For i = j - 1 To 1 Step -1
If Mid(strXl, i, 1) = Chr(0) Then Exit For
Next
i = i + 1
#Else
i = InStrRev(strXl, strFlag1, j) + Len(strFlag1)
#End If

lNameLen = Asc(Mid(strXl, i - 3, 1))
LastUser = Mid(strXl, i, lNameLen)
End Function
[/VBA]

Thanx in advance.

Elias

[Bob Phillips]

VBA6 should be defined, it is built-in.

[eliasarbex]

XLD,

I defined as shown in the picture attached by Paul Hossler. Is there other way to do that?

I tried to remove #s from If ...Else...EndIf but the answer is a sequence of weird symbols again.

Thanx.

[Bob Phillips]

Don't remove the #s, that is the conditional compilation directives, you need them.

[Paul_Hossler]

Zack -- if you're using 2007 / 2010 I think the XLSX file compression is preventing the user name from being displayed in clear text

Also, the text you're looking for might just be used for Excel Shared Workbooks???

I wonder it there's a Windows fiel system API that provides that?

Paul

[eliasarbex]

Paul,

the workbooks are not shared. I tried to find an API, but the search is unsuccessful until now.

I read that the username can be found in the file started by "~$" that is created at the same time that someone opens a file, located in the same folder the file is saved. But I can´t extrat the username from there. Does someone know this information?

Regards,

Elias Arbex