View Full Version : Shared file\macro & IP address
2012-01-31, 08:31 AM
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:
2012-02-01, 05:51 PM
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.
2012-02-03, 11:55 AM
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.
2012-02-04, 02:56 AM
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):
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)
WhatIsMyIP = "No Adapter Found!"
You should then be able to pull your IP by using the following line of 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.
2012-02-04, 02:59 AM
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.
Powered by vBulletin® Version 4.2.0 Copyright © 2013 vBulletin Solutions, Inc. All rights reserved.