Results 1 to 5 of 5

Thread: Shared file\macro & IP address

  1. #1

    Shared file\macro & IP address



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?

    (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?

    I appreciate your help. Thank you.

  2. #2
    Seeker lvalnegri's Avatar
    Join Date
    Jan 2012
    Location
    London
    Posts
    11
    Articles
    0
    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.

  3. #3
    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. Thank you.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,250
    Articles
    57
    Blog Entries
    14
    Excel Version
    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, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,250
    Articles
    57
    Blog Entries
    14
    Excel Version
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •