VPN Forum access and IP addresse Tests

**DocAElstein** · 09-16-2015, 03:25 PM

**DocAElstein** · 09-16-2015, 06:44 PM

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

Code:

  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.

Code:

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:

Code:

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

**DocAElstein** · 09-16-2015, 06:47 PM

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 )

Code:

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

Code:

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

**DocAElstein** · 09-16-2015, 09:00 PM

Other useful command prompt commands when experimenting with VPN are ipconfig /release and ipconfig /renew

Here is are a pair of macros to give a similar Excel column output for the commands are ipconfig /release and ipconfig /renew

Code:


'
Sub ipconfig_release() '
Rem 1 ipconfig /all
 Shell "cmd.exe /c ""ipconfig /release > """ & ThisWorkbook.Path & "\ipconfig_release.txt"""""
' Get the entire text file as a string
Dim FileNum As Long: Let FileNum = FreeFile(1) '      ' The "highway/ street/ link" to be built to transport the text will be given a number. It must be unique. So we use for convenience, the Freefile function: it returns an integer that represents the next file number that the Open statement can use.  The optional argument for the range number is a variant that is used to specify a range from which the next free file number is returned. Enter a value of data type 0 (default) to return a file number in the range 1 - 255 inclusive. Enter 1 to return a file number in the range 256 - 511.   https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/freefile-function  . Note also : Use file numbers in the range 1-255, inclusive, for files not accessible to other applications. Use file numbers in the range 256-511 for files accessible from other applications
Dim PathAndFileName As String, strIPcon As String
 Let PathAndFileName = ThisWorkbook.Path & "\ipconfig_release.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
Dim PublicIP As String: Call PubicIP(PublicIP)
  Let strIPcon = "ipconfig /release  " & vbCr & vbLf & ComputerName & vbCr & vbLf & GetIpAddrTable & vbCr & vbLf & PublicIP & vbCr & vbLf & vbCr & vbLf & """" & Format(Now, "DD MMM YYYY") & " " & vbLf & " " & Format(Now, "hh mm ss") & """" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & 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 = 2
    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
 ActiveWindow.Panes(2).Activate
 Ws.Cells.Item(1, NxtClm).Select

End Sub

Code:


'
Sub ipconfig_renew() '
Rem 1 ipconfig /all
 Shell "cmd.exe /c ""ipconfig /renew > """ & ThisWorkbook.Path & "\ipconfig_renew.txt"""""
' Get the entire text file as a string
Dim FileNum As Long: Let FileNum = FreeFile(1) '        ' The "highway/ street/ link" to be built to transport the text will be given a number. It must be unique. So we use for convenience, the Freefile function: it returns an integer that represents the next file number that the Open statement can use.  The optional argument for the range number is a variant that is used to specify a range from which the next free file number is returned. Enter a value of data type 0 (default) to return a file number in the range 1 - 255 inclusive. Enter 1 to return a file number in the range 256 - 511.   https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/freefile-function  . Note also : Use file numbers in the range 1-255, inclusive, for files not accessible to other applications. Use file numbers in the range 256-511 for files accessible from other applications
Dim PathAndFileName As String, strIPcon As String
 Let PathAndFileName = ThisWorkbook.Path & "\ipconfig_renew.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
Dim PublicIP As String: Call PubicIP(PublicIP)
  Let strIPcon = "ipconfig /renew  " & vbCr & vbLf & ComputerName & vbCr & vbLf & GetIpAddrTable & vbCr & vbLf & PublicIP & vbCr & vbLf & vbCr & vbLf & """" & Format(Now, "DD MMM YYYY") & " " & vbLf & " " & Format(Now, "hh mm ss") & """" & vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & 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 = 2
    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
 ActiveWindow.Panes(2).Activate
 Ws.Cells.Item(1, NxtClm).Select

End Sub

**DocAElstein** · 05-29-2016, 08:07 PM

Automating command prompt combinations

There are two main reasons for the automating of command prompts and associated coding which we have been discussing. One is the obvious time advantage compared with doing the commands manually.

A second important advantage is in consistency of results, since the specific time at which some commands were done were seen to effect the results.

It is easy to Call any combinations of the Functions , along with wait periods in between. Simple macros of this form can be written , Calling on the Functions which we have already developed.

Code:

Option Explicit
'     '      https://www.myonlinetraininghub.com/pausing-or-delaying-vba-using-wait-sleep-or-a-loop
    #If VBA7 Then ' Excel 2010 or later
     Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)
    #Else ' Excel 2007 or earlier
     Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
    #End If
'                                                                             '_


'  Calls
Sub Calls2()
 Call ipconfig_release
 Call Sleep(500)
 Call ipconfigall_routeprint("  after rel in single rel ren pair")
 Call ipconfig_renew
 Call Sleep(500)
 Call ipconfigall_routeprint("  after ren of single rel ren pair")
End Sub

We can further use either buttons or event coding to trigger conveniently various coding combinations.

The uploaded file is an initial file version which I am currently using to do some experimenting with XP and SoftEther.

I will probably develop this further and edit this post accordingly.

"Test ipconfig XP.xls" https://app.box.com/s/hk3cl2sp9nmmyigcun71w54vn38xi6zq

**DocAElstein** · 06-07-2016, 10:31 PM

Problems with Automated Command prompts with VBA

Problems uncounted in the execution of such coding is often difficult to debug, since often the code lines do not error. Often when problems occur, the commends are simply not done, but the VBA coding continues further.

One problem already noted is the presence of spaces in path strings. Care has to be taken in the correct syntax in the use of quotes, " , to overcome this problem
https://stackoverflow.com/questions/...46481#59346481
http://www.eileenslounge.com/viewtop...261670#p261670

Another strange problem I once experienced , was that the command prompt coding lines were not working on one particular XP computer. I finally tracked the problem down to a point _ . _ in the computer name. Once I removed this point _ . _ all was well.
( I could not repeat the exüeriment, as any attempts to change the name to include a point _ . _ , were not allowed….. This particulate computer I did not have from new . I don't know how a point was made in the computer name)

Ref
Change Computer name in XP : https://www.watchingthenet.com/how-t...-or-vista.html
http://www.excelfox.com/forum/showth...ll=1#post11572

**DocAElstein** · 06-07-2016, 10:32 PM

**DocAElstein** · 06-07-2016, 11:52 PM

' To Test Function, Type some arbitrary values in range A1:E10, step through code in F8 Debug Mode in VB Editor, and examine Worksheet, Immediate Window ( Ctrl+G when in VB Editor ), hover over variables in the VB Editor Window with mouse cursor, set watches on variables ( Highlight any occurrence of a variable in the VB Editor and Hit Shift+F9 ) , etc.. and then you should expected the required Output to be pasted out starting Top Left at cell M17

Main Test Code ( Required Function given a couple of Posts down )

Code:

' Delete One Row From A 2D Excel Range Area
' To Test Function, Type some arbitrary values in range A1:E10, step through code in F8 Debug Mode in VB Editor, and examine Worksheet, Immediate Window ( Ctrl+G when in VB Editor ), hover over variables in the VB Editor Window with mouse cursor, set watches on variables ( Highlight  any occurrence of a variable in the VB Editor and Hit Shift+F9 ) , etc.. and then you should expected the required Output to be pasted out starting Top Left at cell M17

Sub Alan()
Dim sp() As Variant
    'Dim DataArr() As Variant: Let DataArr() = Range("A1:E10").Value
 Let sp() = FuR_Alan(Range("A1:E10"), 5)
 'Let sp() = FuRSHg(Range("A1:E10"), 5)
 'Let sp() = FuRSHgDotT(Range("A1:E10"), 5)
 'Let sp() = FuRSHgShtHd(Range("A1:E10"), 5)
 Range("M17").Resize(UBound(sp(), 1), UBound(sp(), 2)).ClearContents
 Let Range("M17").Resize(UBound(sp(), 1), UBound(sp(), 2)) = sp()
End Sub

_............

For no particular reason I am considering this as my Input "Area"

Using Excel 2007 32 bit

_Row\^Col	A	B	C	D	E
1	0	10	20	30	40
2	2	12	22	32	42
3	4	14	24	34	44
4	6	16	26	36	46
5	8	18	28	38	48
6	10	20	30	40	50
7	12	22	32	42	52
8	14	24	34	44	54
9	16	26	36	46	56
10	18	28	38	48	58
11

Sheet: NPueyoGyanArraySlicing

_.......

Expected Output shown in next Post

**DocAElstein** · 06-26-2016, 10:27 PM

-----Original-Nachricht-----
Betreff: [hide.me Support] Re: [Technical] Website support request
Datum: 2019-09-14T17:29:06+0200
Von: "Nathan (hide.me Support)"
An: "Doc.AElstein@t-online.de"

##- Please type your reply above this line -##

Your request has been updated. Reply to this email.
Nathan (hide.me Support)
Sep 14, 15:29 UTC
Hello Alan,
I have activated temporary Premium membership on your account for the next 3 days and you can check your membership status in the Members area here: https://member.hide.me/en/
You only need to Logout from your VPN client and log in again with your TheDocIsHere username (not to be mistaken with your email) and your hide.me password to start using your Premium account.
Regarding connection issues, if you are having difficulties connecting please restart your computer and try using a different protocol. Start your VPN client, don't connect just yet, go to Options in the upper right corner (cog wheel icon) click on Protocol tab and please change the protocol to SSTP.

It seems fallback protocol activated as our VPN app wasn't able to connect using automatic protocol and if you're receiving a message to install the TAP driver, don't worry as that driver is needed for connecting via OpenVPN and SoftEther VPN protocols.
Since those VPN protocols are not present in Windows by default, they need to be installed separately;
Have no worries as this driver is safe and will only install a virtual network adapter used for SoftEther or OpenVPN protocol (the same way as Windows does)
Our VPN client is configured to use OpenVPN as the default Fall back protocol (in case the configured VPN protocol cannot connect).
The second screenshot is a kill switch notification. When you enable this option and the VPN connection drops, your internet connection will disconnect and you will not be exposed.
it will disable your Internet connection in case your VPN connection drops and the client isn't able to reconnect to our VPN servers. It will alert you of this with a pop-up across the screen to re-enable your connection.
________________________________________
Regarding SoftEther issues please try this and make these adjustments and click on the Advanced Settings in the lower right corner from the screenshot in Step 5 of the SoftEther setup guide ( Advanced Setting of Communication)
and from the next screen enable these features as in the screenshot below:
a) Increase the Number of TCP Connections to 8
b) check the box/enable next to "Use Half-Duplex Mode
c) check the box/enable next to Disable UDP acceleration

TCP based protocols such as SSTP and SoftEther might work better for you.
________________________________________
You can also setup your connection using OpenVPN protocol and this setup guide here: https://hide.me/en/vpnsetup/windowsvista/openvpn/
You can find configuration files needed in your Members area here, https://member.hide.me/en/server-status simply choose location you wish to connect to and click on "+ More Details" and download OpenVPN configuration file for your device.
Premium membership is now active for the next 3 days, please try it when you will have time and let me know if it is working better.
Sincerely,
Nathan
Customer Relations Manager
eVenture Limited
Visit our community and share your experience:
https://community.hide.me

FOLLOW US:

Legal | Privacy Policy | Member zone
© 2012-2019 eVenture Limited, Malaysia. All Rights Reserved.

[7O22ZO-VWL0]

„HideMe Entire in Word from Email copy 4.docx" https://app.box.com/s/jkz7nfznit3661jxdh272u0cq0btdo31
„HideMe Entire in Word from Email copy 4.doc" https://app.box.com/s/oemxr2rn5459wadwhjtu1wpcfifjbq7a
• 09-29-2019, 03:12 PM
DocAElstein

**DocAElstein** · 05-13-2018, 02:28 PM

To support solution to this Thread:
http://www.excelfox.com/forum/showth...and-send-email

Test data supplied by Thainguyen for this Thread :
http://www.excelfox.com/forum/showth...and-send-email

Code:

Using Excel 2007 32 bit
_Row\^Col
A
B
C
D
E
F
G
H
I
J
K
N

1
Equipment PM












2
Machine EQ.ID
Manufacture
Model
Description
Serial Number
Weekly
 Date of Service
Weekly
 Next Service 
Monthly
Date of Service
Monthly 
Next  Service
Quarterly
Date of Service
Quarterly 
Next Service
Softwear 

3













4
1
JUKI
GKG GL
GL SCREEN PRINTER
A123
06.04.2018
13.04.2018
15.03.2018
12.04.2018
N/A
N/A


5
2
JUKI
KE-1070L
SMT Placement Machine
A124
11.04.2018
18.04.2018
28.03.2018
25.04.2018
N/A
N/A


6
9
ACE Production
KISS-101B
Selective  Wave Solder
A125
06.04.2018
13.04.2018
15.03.2018
12.04.2018
N/A
N/A


7
59
Heller
1826 MK5
Reflow Oven
A126
N/A
N/A
16.03.2018
13.04.2018
N/A
N/A


8
62
Exit Sign -- N/A -- Exit Lights
N/A
N/A
A127
N/A
N/A
N/A
N/A
N/A
N/A


9
69
South-Tek System
N2-Gen 35ST
Nitrogen Generator
A128
10.04.2018
17.04.2018
N/A
N/A
09.03.2018
06.04.2018


10
75
ACE Production
KISS-102
Selective  Wave Solder
A129
16.04.2018
23.04.2018
N/A
N/A
N/A
N/A


11
101
FKN system
N100 Nibbler
Dispensing
A130
N/A
N/A
N/A
N/A
04.04.2018
02.05.2018


12
109
Mycronic
MY200sx
SMT Machine
A131
N/A
N/A
N/A
N/A
N/A
N/A


13
112
X-TEK
XTV-160
X-Ray System
A132
N/A
N/A
N/A
N/A
N/A
N/A


14
113
MIRTEC
MV-6 OMNI
AOI
A133
N/A
N/A
N/A
N/A
N/A
N/A


15
116
JUKI
KE-2060RL
SMT Placement Machine
A134
N/A
N/A
N/A
N/A
N/A
N/A


16
127
ELGI
EG22-150
Air Compressor
A135
N/A
N/A
N/A
N/A
N/A
N/A


17
128
Juki
KE-2050
SMT
A136
N/A
N/A
N/A
N/A
N/A
N/A


18
137
Juki
K3
Screen printer
A137
06.04.2018
13.04.2018
N/A
N/A
N/A
N/A


19
141
Heller
1826 MK5
Reflow Oven
A138
N/A
N/A
N/A
N/A
N/A
N/A


20
142
NISSAN
MCU-112A331.V
Forklift
A139
N/A
N/A
N/A
N/A
15.02.2018
15.03.2018


21
142
NISSAN/yearly oil change and lube
MCU-112A331.V
Forklift
A140
N/A
N/A
N/A
N/A
N/A
N/A


22










28.01.1900


23














Worksheet: Equipment PM

Thread: VPN Forum access and IP addresse Tests

Thread Tools

Search Thread

Display

Hybrid View

VPN Forum access and IP addresse Tests

Problems with Automated Command prompts with VBA

Delete One Row From A 2D Excel Range Area

Test data supplied by Thainguyen

Similar Threads

Table Tests. And Thread Copy Tests No Reply needed

Table Tests. And Thread Copy Tests No Reply needed

New Forum Style

Forum performances

Is that possible to establish between forum page and visual script

Bookmarks

Bookmarks

Posting Permissions

_Row\^Col	A	B	C	D	E
1	0	10	20	30	40
2	2	12	22	32	42
3	4	14	24	34	44
4	6	16	26	36	46
5	8	18	28	38	48
6	10	20	30	40	50
7	12	22	32	42	52
8	14	24	34	44	54
9	16	26	36	46	56
10	18	28	38	48	58
11

_Row\^Col	A	B	C	D	E	F	G	H	I	J	K	N
1	Equipment PM
2	Machine EQ.ID	Manufacture	Model	Description	Serial Number	Weekly Date of Service	Weekly Next Service	Monthly Date of Service	Monthly Next Service	Quarterly Date of Service	Quarterly Next Service	Softwear
3
4	1	JUKI	GKG GL	GL SCREEN PRINTER	A123	06.04.2018	13.04.2018	15.03.2018	12.04.2018	N/A	N/A
5	2	JUKI	KE-1070L	SMT Placement Machine	A124	11.04.2018	18.04.2018	28.03.2018	25.04.2018	N/A	N/A
6	9	ACE Production	KISS-101B	Selective Wave Solder	A125	06.04.2018	13.04.2018	15.03.2018	12.04.2018	N/A	N/A
7	59	Heller	1826 MK5	Reflow Oven	A126	N/A	N/A	16.03.2018	13.04.2018	N/A	N/A
8	62	Exit Sign -- N/A -- Exit Lights	N/A	N/A	A127	N/A	N/A	N/A	N/A	N/A	N/A
9	69	South-Tek System	N2-Gen 35ST	Nitrogen Generator	A128	10.04.2018	17.04.2018	N/A	N/A	09.03.2018	06.04.2018
10	75	ACE Production	KISS-102	Selective Wave Solder	A129	16.04.2018	23.04.2018	N/A	N/A	N/A	N/A
11	101	FKN system	N100 Nibbler	Dispensing	A130	N/A	N/A	N/A	N/A	04.04.2018	02.05.2018
12	109	Mycronic	MY200sx	SMT Machine	A131	N/A	N/A	N/A	N/A	N/A	N/A
13	112	X-TEK	XTV-160	X-Ray System	A132	N/A	N/A	N/A	N/A	N/A	N/A
14	113	MIRTEC	MV-6 OMNI	AOI	A133	N/A	N/A	N/A	N/A	N/A	N/A
15	116	JUKI	KE-2060RL	SMT Placement Machine	A134	N/A	N/A	N/A	N/A	N/A	N/A
16	127	ELGI	EG22-150	Air Compressor	A135	N/A	N/A	N/A	N/A	N/A	N/A
17	128	Juki	KE-2050	SMT	A136	N/A	N/A	N/A	N/A	N/A	N/A
18	137	Juki	K3	Screen printer	A137	06.04.2018	13.04.2018	N/A	N/A	N/A	N/A
19	141	Heller	1826 MK5	Reflow Oven	A138	N/A	N/A	N/A	N/A	N/A	N/A
20	142	NISSAN	MCU-112A331.V	Forklift	A139	N/A	N/A	N/A	N/A	15.02.2018	15.03.2018
21	142	NISSAN/yearly oil change and lube	MCU-112A331.V	Forklift	A140	N/A	N/A	N/A	N/A	N/A	N/A
22											28.01.1900
23

_Row\^Col	A	B	C	D	E
1	0	10	20	30	40
2	2	12	22	32	42
3	4	14	24	34	44
4	6	16	26	36	46
5	8	18	28	38	48
6	10	20	30	40	50
7	12	22	32	42	52
8	14	24	34	44	54
9	16	26	36	46	56
10	18	28	38	48	58
11

_Row\^Col	A	B	C	D	E
1	0	10	20	30	40
2	2	12	22	32	42
3	4	14	24	34	44
4	6	16	26	36	46
5	8	18	28	38	48
6	10	20	30	40	50
7	12	22	32	42	52
8	14	24	34	44	54
9	16	26	36	46	56
10	18	28	38	48	58
11