help with error please

[fry]

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...

'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...

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

I'm stuck!!

Any help would be appreciated, thanks in advance

 

[Hermanito]

A missing reference maybe?
Not entirely sure you need to include those when using late binding like you do, but I can't think of another reason...

 

[Ruddles]

Your code works okay here.

Check you have an environment variable called COMSPEC. What's its value?

Perhaps IPCONFIG.EXE isn't in your PATH. What happens when you enter that command in full on a command line, i.e. something like:-

C:\WINDOWS\system32\cmd.exe /c ipconfig

or just:-

ipconfig

 

[fry]

Thanks guys, I'm not an expert and this code was something I got off this site so I'm not sure about the 'comspec' variable???

I've tried what you suggested and ipconfig must be in the path because it works.

Maybe the full routine would help re the 'comspec' question so here it is...

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

Private Sub Workbook_Open()

'Protect the workbook to start off with!!
ActiveWorkbook.Protect Password:="larssen6"

  '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








'Set the scrollarea for purchase orders
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
    If WS.Name <> "0 Data" And WS.Name <> "0 Log" Then
        WS.ScrollArea = "A1:M50"
    Else
        WS.ScrollArea = ""
    End If
Next WS

'Sort the sheets in the workbook in descending sequence NOT including hidden ones.
lShtLast = Sheets.Count
 
    For lCount = 2 To lShtLast
            For lCount2 = lCount To lShtLast
                If Sheets(lCount2).Visible = True And Sheets(lCount).Visible = True Then
                If CInt(Sheets(lCount2).Name) > CInt(Sheets(lCount).Name) Then
                    Sheets(lCount2).Move Before:=Sheets(lCount)
                End If
                End If
            Next lCount2
        Next lCount

'Select the 'New Purchase Order' sheet
Sheets(1).Select
Range("C11").Select

End Sub

Thanks

 

[Ruddles]

COMSPEC is an environment variable which tells the operating system where your command line is located. Enter cmd.exe in the Start > Run... box and click OK. A small window should open. This is your CLI or 'command line interface' where you can enter operating system commands directly.

Type set and hit Enter: these are your environment variables in alphabetical order. Is COMSPEC (or ComSpec) shown? (You might have to scroll back up.)

Alternatively type echo %comspec% and hit Enter.

What's the value of COMSPEC?

Try replacing your line of code with this slightly modified version:-

wsh.Run "[COLOR=red][B]""[/B][/COLOR]%comspec%[B][COLOR=red]""[/COLOR][/B] /c ipconfig > " & TempFil, 0, True

 

[fry]

Hi Ruddles

comspec=c:\windows\system32\cmd.exe

Will try the modified version now!

 

[fry]

Just tried it and get the same result

 

[fry]

Strangely if I open the workbook up on another computer I get a different error, 'cannot find file' and debugging highlights

Set fil = FSO.GetFile(TempFil)

Maybe there is an easier way to do this?? Does anyone knows of one??

Thanks!

 

[Ruddles]

I wouldn't use the root of the C: drive to store temporary files - it's messy and some corporate machines are secured to prevent you doing this.

Set the name of your temporary file as follows:-

   TempFil = environ("temp") & "\myip.txt"

That will place it in your Windows temp folder.

That doesn't solve your main problem though. As I said, your code works fine here.

Just out of curiosity, try replacing this line:-

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

with this block of code:-

If Dir(TempFil) <> "" Then Kill TempFil
Shell Environ("comspec") & " /c ipconfig > " & TempFil
Do Until Dir(TempFil) <> ""
  DoEvents
Loop
Application.Wait Now() + TimeSerial(0, 0, 2)

Does that work?

If not, I have some code at home which gets your IP address without using IPCONFIG at all.

 

[fry]

Many thanks Ruddles, I'll try that out but it might br tomorrow when I can get back to you as I have to leave work early today

Cheers

Pete