VPN Forum access and IP addresse Tests

[DocAElstein]

Notes in support of these Threads
https://stackoverflow.com/questions/...46481#59346481
http://www.eileenslounge.com/viewtop...=33775#p261670
http://www.eileenslounge.com/viewtop...=33775#p261670
http://www.excelfox.com/forum/showth...ll=1#post11608
https://tinyurl.com/rwt2kx8 , https://tinyurl.com/yhrwdsdb

( notes which I might use in a Blog at a later date )

Automating fixes and investigations in XP

Information as text string, programmatically obtaining
In addition to time saving, advantages of automating the fixes discussed allow for more consistent results, since it was apparent from initial investigations that the timing at which commands such as ipconfig /release and ipconfig/ renew where typed manually seemed to have an effect.

It turns out that automating off theses commands via VBA is very simple, in principle a single code line does the main action http://www.eileenslounge.com/viewtop...=33775#p261628
of doing the command, and additionally storing the contents displayed as a result of the command, into a text file

Further manipulation of the text file information
Using VBA lends itself to bringing the data into a convenient form into Excel such as in columns for ease of comparing.
When doing this manually, some untidy behavior was sometimes seen, such as extra unnecessary blank lines and information spilling into adjoining columns. So some initial investigation into the actual build up of the text file is useful.
The complete text in a text file, or a column in Excel , is actually just a single long text string. The different "lines" of text as we perceive it is made possible by the inclusion "invisible characters" of the so called "line feed" and / or "carriage return" form. If we analyze typical strings in detail then this will help us in deciding how to manipulate them in order to get consistent / tidy results
We need to first produce a simple long text string, which we can then analyze, for example using this coding: http://www.excelfox.com/forum/showth...ts-of-a-string
The following macro example, will give us the required complete string for ipconfig /all in variable strIPcon

  Sub cmdconsoleContentsToString()
    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"
  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
' Analyse string
Call WtchaGot(strIn:=strIPcon)
End Sub 

( Sub WtchaGot(ByVal strIn As String) can be found here : http://www.excelfox.com/forum/showth...ll=1#post10946

The results from Sub WtchaGot( ) can be used in analyzing the text format given out by the command prompt code live.

Alternatively, if you already have the results in a text file, then you use the same basic coding without the initial commend prompt code line. You will need to know the file name and location of the text file.

Sub StringContentsFromTextfile()
' 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 & "\test1B"
  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
' Analyse string
Call WtchaGot(strIn:=strIPcon)
End Sub

Some Examples:
When trying to compare some similar measurements for Vista and XP operating systems, two annoying characteristics were noticed
_ In XP there seemed to be extra lines, XP Extra Blank Lines.JPG : https://imgur.com/flm1pjQ

Looking at the first part of a typical Vista output from Sub WtchaG( ) , we see this First Part of Typical Vista Output ipconfig all.JPG , https://imgur.com/Zxkyxkq

vbCr & vbLf & "Windows" & "-" & "IP" & "-" & "Konfiguration" & vbCr & vbLf & vbCr & vbLf & " " & " " & " " & "Hostname" & " " & "…………..
Comparing that with a similarly generated text in XP we see this First Part of Typical XP Output ipconfig all.JPG : https://imgur.com/nXtTeQH

vbCr & vbCr & vbLf & "Windows" & "-" & "IP" & "-" & "Konfiguration" & vbCr & vbCr & vbLf & vbCr & vbCr & vbLf & " " & " " & " " & " " & " " & " " & " " & " " & "Hostname" & "."…………….
Clearly we see in the XP output a doubling of vbCr where we might more typically see a single vbCr
We can use a simple VBA code line to easily replace double occurrences of vbCr with a single vbCr

_ Spilling into neighboring columns when pasted into Excel
Another annoyance shows up as contents spilling into neighboring columns when pasted into Excel : XP Spilling into neighboring columns.JPG : https://imgur.com/8aWq5uj
Examining parts of the offending string, we see that there are some occurrences of vbTab, XP Spilling into neighboring columns due to vbTab.JPG :
…………" & " " & " " & "192" & "." & "168" & "." & "2" & "." & "105" & vbTab & " " & " " & "25" & vbCr & vbLf………..
In sinple text, vbTab is used as a sort of ordered positioning separation by empty spaces. Unfortunately , VBA uses them to indicate a cell vertical wall.
To overcome this problem we can use a simple VBA code line to easily replace occurrences of vbTab with a few empty spaces.


A final code to tidy the text given, by a command such as ipconfig /all , would have this form:

Sub cmdconsoleContentsToStringTidy() '  http://www.excelfox.com/forum/showthread.php/2384-VPN-Forum-access-and-IP-addresse-Tests?p=11568&viewfull=1#post11568
 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"
  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)
End Sub

Ref
http://eileenslounge.com/viewtopic.p...=33775#p261628