VPN Forum access and IP addresse Tests

[DocAElstein]

Automating fixes and investigations in XP

Bringing results into Excel File
It is convenient to bring the text results into an excel column.
Manually we do this by simply pasting into a single cell: Because the text string has already got a degree of formatting into lines via the vbCr & vbLf s , this is recognized by Excl, since excel also uses the convention of vbCr & vbLf as the line separator or, as Excel sees it, as a horizontal cell wall border.
So we simply need to add a few code lines to put the text string into the clipboard, and then paste into a convenient cell in Excel.
( A few probllems were encountered when adding some extra information. These are the subject of some other forum posts, for example: https://www.eileenslounge.com/viewto...p?f=18&t=33834 )

Sub cmdconsoleContentsToExcel() '  http://www.excelfox.com/forum/showthread.php/2384-VPN-Forum-access-and-IP-addresse-Tests?p=11569&viewfull=1#post11569
 Shell "cmd.exe /c ""ipconfig /all > """ & ThisWorkbook.Path & "\ipconfig__all.txt"""""
' Get the entire text file as a string
Dim FileNum As Long: Let FileNum = FreeFile(1) '
Dim PathAndFileName As String, strIPcon As String
 Let PathAndFileName = ThisWorkbook.Path & "\ipconfig__all.txt"
 ' Let PathAndFileName = ThisWorkbook.Path & "\test2B.txt"  '  Al
  Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundemental type data input...
    strIPcon = VBA.Strings.Space$(LOF(FileNum)) '....and wot recives it hs to be a string of exactly the right length
    Get #FileNum, , strIPcon
  Close #FileNum
' Tidy the string
 Let strIPcon = Replace(strIPcon, vbCr & vbCr, vbCr, 1, 1, vbBinaryCompare)
 Let strIPcon = Replace(strIPcon, vbTab, "   ", 1, 1, vbBinaryCompare)
' add any extra info to string
  Let strIPcon = vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & """" & Format(Now, "DD MMM YYYY") & " " & vbLf & " " & Format(Now, "hh mm ss") & """" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & strIPcon  ' vbLf is recognised as a new line within an Excel"
' String content check
' Call WtchaGot(strIPcon)
' put the text in the clipboard
Dim objDataObject As Object: Set objDataObject = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
objDataObject.SetText strIPcon: objDataObject.PutInClipboard

' Excel Worksheet
Dim Ws As Worksheet: Set Ws = ActiveSheet
Dim Clm As Range, NxtClm As Long
 Set Clm = Ws.Cells.Find(What:="*", After:=Ws.Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
    If Clm Is Nothing Then
     Let NxtClm = 1
    Else
     Let NxtClm = Clm.Column + 1
    End If
' Put in next free column in Active sheet
 Ws.Paste Destination:=Ws.Cells.Item(1, NxtClm)
 Ws.Columns.AutoFit: Ws.Rows.AutoFit


End Sub

The next coding has a section to add the results obtained in the command window for the command route print

Sub ipconfigall_routeprint() '
Rem 1 ipconfig /all
 Shell "cmd.exe /c ""ipconfig /all > """ & ThisWorkbook.Path & "\ipconfig__all.txt"""""
' Get the entire text file as a string
Dim FileNum As Long: Let FileNum = FreeFile(1) '
Dim PathAndFileName As String, strIPcon As String
 Let PathAndFileName = ThisWorkbook.Path & "\ipconfig__all.txt"
 ' Let PathAndFileName = ThisWorkbook.Path & "\test2B.txt"  '  Al
  Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundemental type data input...
    strIPcon = VBA.Strings.Space$(LOF(FileNum)) '....and wot recives it hs to be a string of exactly the right length
    Get #FileNum, , strIPcon
  Close #FileNum
' Tidy the string
 Let strIPcon = Replace(strIPcon, vbCr & vbCr, vbCr, 1, 1, vbBinaryCompare)
 Let strIPcon = Replace(strIPcon, vbTab, "   ", 1, 1, vbBinaryCompare)
' add any extra info to string
  Let strIPcon = vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & """" & Format(Now, "DD MMM YYYY") & " " & vbLf & " " & Format(Now, "hh mm ss") & """" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & strIPcon  ' vbLf is recognised as a new line within an Excel"
' String content check
' Call WtchaGot(strIPcon)
' put the text in the clipboard
Dim objDataObject As Object: Set objDataObject = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
objDataObject.SetText strIPcon: objDataObject.PutInClipboard

' Excel Worksheet
Dim Ws As Worksheet: Set Ws = ActiveSheet
Dim Clm As Range, NxtClm As Long
 Set Clm = Ws.Cells.Find(What:="*", After:=Ws.Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
    If Clm Is Nothing Then
     Let NxtClm = 1
    Else
     Let NxtClm = Clm.Column + 1
    End If
' Put in next free column in Active sheet
 Ws.Paste Destination:=Ws.Cells.Item(1, NxtClm)
' Ws.Columns.AutoFit: Ws.Rows.AutoFit

Rem 2 route print
 Shell "cmd.exe /c ""route print > """ & ThisWorkbook.Path & "\route_print.txt"""""
' Get the entire text file as a string
 Let FileNum = FreeFile(1) '
Dim strrouteprint As String
 Let PathAndFileName = ThisWorkbook.Path & "\route_print.txt"
 ' Let PathAndFileName = ThisWorkbook.Path & "\test2B.txt"  '  Al
  Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundemental type data input...
    strrouteprint = VBA.Strings.Space$(LOF(FileNum)) '....and wot recives it hs to be a string of exactly the right length
    Get #FileNum, , strrouteprint
  Close #FileNum
' Tidy the string
 Let strrouteprint = Replace(strrouteprint, vbCr & vbCr, vbCr, 1, 1, vbBinaryCompare)
 Let strrouteprint = Replace(strrouteprint, vbTab, "   ", 1, 1, vbBinaryCompare)
' put the text in the clipboard
objDataObject.SetText strrouteprint: objDataObject.PutInClipboard
' Excel Worksheet
Dim Lr As Long: Let Lr = Ws.Cells(Ws.Rows.Count, NxtClm).End(xlUp).Row
' Put in next free column in Active sheet
 Ws.Paste Destination:=Ws.Cells.Item(Lr + 30, NxtClm)
 Ws.Columns.AutoFit: Ws.Rows.AutoFit

End Sub

Typically , ipconfig/ all and route print are done together to get information concerning a connection.
A code version here, http://www.excelfox.com/forum/showth...ll=1#post11834 , combines the two macros and the two sets of info is pasted out to the same column, with the and route print info under the ipconfig/ all info.
In addition the coding calls some Functions which give some extra information , such IP address, computer name. Details to those Functions is given here : http://www.excelfox.com/forum/showth...ll=1#post11672




Ref
http://eileenslounge.com/viewtopic.p...=31395#p243002