help with error please

fry

Active Member
Joined
Apr 25, 2007
Messages
411
Hi All

I've had the following code in a workbook for ages and all of a sudden it pops up with an error when I open it...

Code:
'Log time, user details & IP address
  EndRow = Sheets("0 Log").Range("A4000").End(xlUp).Row + 1
  Sheets("0 Log").Range("A" & EndRow).Value = Environ("USERNAME")
  Sheets("0 Log").Range("B" & EndRow).Value = Application.UserName
  Sheets("0 Log").Range("C" & EndRow).Value = Now()

    Dim wsh As Object
    Dim RegEx As Object, RegM As Object
    Dim FSO As Object, fil As Object
    Dim ts As Object, txtAll As String, TempFil As String
    Set wsh = CreateObject("WScript.Shell")
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set RegEx = CreateObject("vbscript.regexp")
    TempFil = "C:\myip.txt"
     ' Save ipconfig info to temporary file
wsh.Run "%comspec% /c ipconfig > " & TempFil, 0, True
    With RegEx
        .Pattern = "(\d{1,3}\.){3}\d{1,3}"
        .Global = False
    End With
    Set fil = FSO.GetFile(TempFil)
     ' Access temporary file
    Set ts = fil.OpenAsTextStream(1)
    txtAll = ts.ReadAll
    Set RegM = RegEx.Execute(txtAll)
     ' Return IP address to Activesheet cell A1 by parsing text
    'ActiveSheet.Range("A1").Value = RegM(0)
    Sheets("0 Log").Range("D" & EndRow).Value = RegM(0)
    'ActiveSheet.Range("A1").EntireColumn.AutoFit
    ts.Close
     ' Remove temp file
    Kill TempFil
     
    Set ts = Nothing
    Set wsh = Nothing
    Set fil = Nothing
    Set FSO = Nothing
    Set RegM = Nothing
    Set RegEx = Nothing

The error it pops up with is...

Run-time error '-2(fffffffe)':
Method 'Run' of object 'IWshShell3' failed

and upon clicking 'Debug', it highlights the following...

Code:
wsh.Run "%comspec% /c ipconfig > " & TempFil, 0, True

I'm stuck!!

Any help would be appreciated, thanks in advance :)
 
Hi Ruddles

Just tried the replacement code and it works perfectly!!!

Can't understand why but can you enlighten me???

Out of curiosity would the code you have to get IP etc be more efficient??

Many thanks for solving this problem though, I owe you a beer!! :)
 
Upvote 0

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Just tried the replacement code and it works perfectly!!!

Can't understand why but can you enlighten me???
I can't, actually. Your original code worked perfectly for me at work and at home.
Out of curiosity would the code you have to get IP etc be more efficient??
The code I posted isn't perfect for a couple of reasons:-
  • It shells out to a DOS command and gets the data from a text file. If the DOS command fails, the process really ought to be able to cope with that. I can't see that it does.
  • The code waits for the DOS file to 'appear' before it tried to read it but even that is too fast, and I had to code a two-second delay to allow the file to close properly. I hope two seconds is enough! I hate introducing unecessary delays in code.
I have code which gets the IP address from the Windows Registry. It's got to be quicker because there's no need to introduce an artifical delays but on the down side there is a lot of code involved. I shall dig it out in a minute and post it in this thread.
Many thanks for solving this problem though, I owe you a beer!!
If you're anywhere near Carnforth I might take you up on that! :)
 
Upvote 0
I have code which gets the IP address from the Windows Registry.
The ageing grey matter failed me on this occasion: the code doesn't access the Registry, it uses the Winsock DLL.

Sling the whole of this code into a new general code module:-
Code:
Option Explicit
 
' ******** Code Start ********
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed, except as part of an application.
' You are free to use it in any application, provided the copyright notice is left unchanged.
' Code Courtesy of Dev Ashish
'
Private Const MAX_WSADescription = 256
Private Const MAX_WSASYSStatus = 128
Private Const AF_INET = 2
Private Type WSADATA
    wversion As Integer
    wHighVersion As Integer
    szDescription(MAX_WSADescription) As Byte
    szSystemStatus(MAX_WSASYSStatus) As Byte
    wMaxSockets As Long
    wMaxUDPDG As Long
    dwVendorInfo As Long
End Type
 
Private Type HOSTENT
    hName As Long
    hAliases As Long
    hAddrType As Integer
    hLength As Integer
    hAddrList As Long
End Type
 
' returns the standard host name for the local machine
Private Declare Function apiGetHostName _
    Lib "wsock32" Alias "gethostname" _
    (ByVal name As String, _
    ByVal nameLen As Long) _
    As Long
 
' retrieves host information corresponding to a host name
' from a host database
Private Declare Function apiGetHostByName _
    Lib "wsock32" Alias "gethostbyname" _
    (ByVal hostname As String) _
    As Long
 
' retrieves the host information corresponding to a network address
Private Declare Function apiGetHostByAddress _
    Lib "wsock32" Alias "gethostbyaddr" _
    (addr As Long, _
    ByVal dwLen As Long, _
    ByVal dwType As Long) _
    As Long
 
' moves memory either forward or backward, aligned or unaligned,
' in 4-byte blocks, followed by any remaining bytes
Private Declare Sub sapiCopyMem _
    Lib "kernel32" Alias "RtlMoveMemory" _
    (Destination As Any, _
    Source As Any, _
    ByVal Length As Long)
 
' converts a string containing an (Ipv4) Internet Protocol
' dotted address into a proper address for the IN_ADDR structure
Private Declare Function apiInetAddress _
    Lib "wsock32" Alias "inet_addr" _
    (ByVal cp As String) _
    As Long
' function initiates use of Ws2_32.dll by a process
Private Declare Function apiWSAStartup _
    Lib "wsock32" Alias "WSAStartup" _
    (ByVal wVersionRequired As Integer, _
    lpWsaData As WSADATA) _
    As Long
 
Private Declare Function apilstrlen _
    Lib "kernel32" Alias "lstrlen" _
    (ByVal lpString As Long) _
    As Long
 
Private Declare Function apilstrlenW _
    Lib "kernel32" Alias "lstrlenW" _
    (ByVal lpString As Long) _
    As Long
' function terminates use of the Ws2_32.dll
Private Declare Function apiWSACleanup _
    Lib "wsock32" Alias "WSACleanup" _
    () As Long
 
Function fGetHostIPAddresses(strHostName As String) As Collection
'
' Resolves the English HostName and returns
' a collection with all the IPs bound to the card
'
On Error GoTo ErrHandler
Dim lngRet As Long
Dim lpHostEnt As HOSTENT
Dim strOut As String
Dim colOut As Collection
Dim lngIPAddr As Long
Dim abytIPs() As Byte
Dim i As Integer
    Set colOut = New Collection
 
    If fInitializeSockets() Then
        strOut = String$(255, vbNullChar)
        lngRet = apiGetHostByName(strHostName)
        If lngRet Then
 
            Call sapiCopyMem( _
                    lpHostEnt, _
                    ByVal lngRet, _
                    Len(lpHostEnt))
 
            Call sapiCopyMem( _
                    lngIPAddr, _
                    ByVal lpHostEnt.hAddrList, _
                    Len(lngIPAddr))
 
            Do While (lngIPAddr)
                With lpHostEnt
                    ReDim abytIPs(0 To .hLength - 1)
                    strOut = vbNullString
                    Call sapiCopyMem( _
                        abytIPs(0), _
                        ByVal lngIPAddr, _
                        .hLength)
                    For i = 0 To .hLength - 1
                        strOut = strOut & abytIPs(i) & "."
                    Next
                    strOut = Left$(strOut, Len(strOut) - 1)
                    .hAddrList = .hAddrList + Len(.hAddrList)
                    Call sapiCopyMem( _
                            lngIPAddr, _
                            ByVal lpHostEnt.hAddrList, _
                            Len(lngIPAddr))
                    If Len(Trim$(strOut)) Then colOut.Add strOut
                End With
            Loop
        End If
    End If
    Set fGetHostIPAddresses = colOut
ExitHere:
    Call apiWSACleanup
    Set colOut = Nothing
    Exit Function
ErrHandler:
    With Err
        MsgBox "Error: " & .Number & vbCrLf & .Description, _
            vbOKOnly Or vbCritical, _
            .Source
    End With
    Resume ExitHere
 
End Function
 
Function fGetHostName(strIPAddress As String) As String
'
' Looks up a given IP address and returns the
' machine name it's bound to
'
On Error GoTo ErrHandler
Dim lngRet As Long
Dim lpAddress As Long
Dim strOut As String
Dim lpHostEnt As HOSTENT
 
    If fInitializeSockets() Then
        lpAddress = apiInetAddress(strIPAddress)
        lngRet = apiGetHostByAddress(lpAddress, 4, AF_INET)
        If lngRet Then
            Call sapiCopyMem( _
                lpHostEnt, _
                ByVal lngRet, _
                Len(lpHostEnt))
            fGetHostName = fStrFromPtr(lpHostEnt.hName, False)
        End If
    End If
ExitHere:
    Call apiWSACleanup
    Exit Function
ErrHandler:
    With Err
        MsgBox "Error: " & .Number & vbCrLf & .Description, _
            vbOKOnly Or vbCritical, _
            .Source
    End With
    Resume ExitHere
 
End Function
 
Private Function fInitializeSockets() As Boolean
Dim lpWsaData As WSADATA
Dim wVersionRequired As Integer
 
    wVersionRequired = fMakeWord(2, 2)
    fInitializeSockets = ( _
        apiWSAStartup(wVersionRequired, lpWsaData) = 0)
 
End Function
 
Private Function fMakeWord( _
                            ByVal low As Integer, _
                            ByVal hi As Integer) _
                            As Integer
Dim intOut As Integer
    Call sapiCopyMem( _
        ByVal VarPtr(intOut) + 1, _
        ByVal VarPtr(hi), _
        1)
    Call sapiCopyMem( _
        ByVal VarPtr(intOut), _
        ByVal VarPtr(low), _
        1)
    fMakeWord = intOut
 
End Function
 
Private Function fStrFromPtr( _
                                    pBuf As Long, _
                                    Optional blnIsUnicode As Boolean) _
                                    As String
Dim lngLen As Long
Dim abytBuf() As Byte
 
    If blnIsUnicode Then
        lngLen = apilstrlenW(pBuf) * 2
    Else
        lngLen = apilstrlen(pBuf)
    End If
    ' if it's not a ZLS
    If lngLen Then
        ReDim abytBuf(lngLen)
        ' return the buffer
        If blnIsUnicode Then
            'blnIsUnicode is True not tested
            Call sapiCopyMem(abytBuf(0), ByVal pBuf, lngLen)
            fStrFromPtr = abytBuf
        Else
            ReDim Preserve abytBuf(UBound(abytBuf) - 1)
            Call sapiCopyMem(abytBuf(0), ByVal pBuf, lngLen)
            fStrFromPtr = StrConv(abytBuf, vbUnicode)
        End If
    End If
 
End Function
 
' ******** Code End ********

Here's my sample code to get the IP address:-
Code:
Option Explicit
 
Sub JustDoIt()
 
  MsgBox fGetHostIPAddresses(Environ("computername")).Item(1)
 
End Sub
 
Sub GetFirstAddress()
 
  Dim MyMachine As String
  Dim MyIP As String
 
  MyMachine = Environ("computername")
 
  fGetHostIPAddresses(MyMachine).Count
 
  If fGetHostIPAddresses(MyMachine).Count = 0 Then
    MsgBox "There is no IP address associated with " & Environ("computername") & Space(10), _
           vbOKOnly + vbExclamation
  Else
    MyIP = fGetHostIPAddresses(MyMachine).Item(1)
    If fGetHostIPAddresses(MyMachine).Count = 1 Then
      MsgBox Environ("computername") & " = " & MyIP & Space(10), vbOKOnly + vbInformation
    Else
      MsgBox Environ("computername") & " = " & MyIP & Space(10) & vbCrLf & vbCrLf _
           & fGetHostIPAddresses(MyMachine).Count - 1 _
           & " additional IP address(es) associated with this machine" & Space(10), _
           vbOKOnly + vbInformation
    End If
  End If
 
End Sub
 
Sub GetAllAddresses()
 
  Dim MyMachine As String
  Dim MyIPs As Collection
  Dim oIP As Variant
  Dim sMessage As String
 
  MyMachine = Environ("computername")
 
  Set MyIPs = fGetHostIPAddresses(MyMachine)
 
  sMessage = "All IP addresses on " & MyMachine & Space(10) & vbCrLf & vbCrLf
  For Each oIP In MyIPs
    sMessage = sMessage & Space(5) & oIP & vbCrLf
  Next oIP
 
  MsgBox sMessage, vbOKOnly + vbInformation
 
End Sub
Since a machine can have more than one IP address associated with it, the fGetHostIPAddresses function actually returns a collection of addresses: my subroutine GetFirstAddress just gets the first address from the collection, even if it's not the one that's actually in use. (See footnote.)

JustDoIt is the shorted bit of code you actually need: you would probably just assign the value to a variable instead of MsgBoxing it out to the screen.

GetAllAddresses loops through the collection of IP addresses returned by fGetHostIPAddresses and builds a message containing all of them, even if there's only one.

Footnote: I think I'm correct in stating that the only way to find the IP address your machine is presenting to the outside world is to go to a Web page which can extract your IP address from the page request header via a PHP script or similar. But the code I've posted above should do the job to all intents and purposes.
 
Upvote 0
Many Thanks Ruddles, I'm not sure whether my ageing grey matter can cope (I'm usually the oldest in the room)!! :)
 
Upvote 0
Never mind my sample code - that's just a few examples of the way you'd call fGetHostIPAddresses. For the moment just paste the first block of code into a standard code module and then type ?fGetHostIPAddresses(Environ("computername")).Item(1) in to the VBA Immediate window followed by Enter.
 
Upvote 0

Forum statistics

Threads
1,225,156
Messages
6,183,246
Members
453,152
Latest member
ChrisMd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top