Shared file\macro & IP address

ibrahimaa

New member
Joined
May 22, 2011
Messages
16
Reaction score
0
Points
0
I have windows vista and xls 2010. I will send to our users Xls file where a macro extracting data from another xls file that I have it on a server shared folder.

(1) What is the maximum number of users who can at the same time access the xls file that I have on the server shared folder? :lol:

(2) In the login script, I am using IPconfig to read the IP address of the user machine to direct him to the nearest server. How can I do this inside the xls macro?:eyebrows:

I appreciate your help. Thank you. :clap2:
 

lvalnegri

New member
Joined
Jan 31, 2012
Messages
11
Reaction score
0
Points
0
Location
London
about (2).

add these lines:

Dim objShell As Object
Set objShell = CreateObject("Wscript.Shell")
objShell.Run "%comspec% /c ipconfig > C:\results.ipc"
Set objShell = Nothing



and then read the file.
 

ibrahimaa

New member
Joined
May 22, 2011
Messages
16
Reaction score
0
Points
0
Thanks a lot for the valuable support. However, in my macro, I have the following simple code to extract the user id which I am using it in directing him to the related data.
User = Environ("USERNAME")
ActiveSheet.Cells(5, "D") = User
I would appreciate your assistance in using the same method to extract the IP address because sending to file and then read it from there is quite length & complicated. :confused2:Thank you.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
ibrahimaa,

There is no one-line way to get your IP address the way you are getting the username. So you're going to need more code than that. Here's what you can do though... copy the following code into your module (place it after an End Sub line somewhwere):

Code:
Public Function WhatIsMyIP() As String
'Return address of first enabled IP adapter    Dim IPConfigSet As Object
    Dim IPConfig As Object
    Dim i As Long
    Set IPConfigSet = GetObject("winmgmts:").ExecQuery("select IPAddress from Win32_NetworkAdapterConfiguration where IPEnabled=TRUE")
    For Each IPConfig In IPConfigSet
        If Not IsNull(IPConfig.IPAddress) Then
            For i = LBound(IPConfig.IPAddress) To UBound(IPConfig.IPAddress)
                WhatIsMyIP = IPConfig.IPAddress(i)
                Exit Function
            Next
        End If
    Next
    WhatIsMyIP = "No Adapter Found!"
End Function

You should then be able to pull your IP by using the following line of code:
Code:
IPAddress = WhatIsMyIP

Note that this will pull the very first IP address in your system. While this is TYPICALLY your LAN IP address, depending on the PC using it, it may be the wireless adapater, or even an IPv6 address which is totally different than the IPv4 address that you're probably looking for.

Give it a test and let me know how it works for you.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Oh, and as for the max number of users who can access the file in the shared folder at once...
  • For reading, I believe it's unlimited. (The second and subsequent users get a read only copy, unless you share the workbook...)
  • For writing, I would HIGHLY recommend that it be one. Enabling the Shared Workbook feature is asking for problems as it is well documented as unstable.
 
Top