Notes tests, text files, manipulation of text files in Excel and with Excel VBA CSV stuff

[DocAElstein]

Insert some lines without losing what’s there / Retain Comments / Shift what’s there to the right

A fairly simple problem
A common requirement for me is that I want to put some raw code lines into an existing script or VBA code, but I don’t want to change the line numbers referring to where they do in the existing code file.
This might occur if I want to replace some coding, but would like to keep the existing coding for future reference, possibly commented out to the right
What I might do typically for just a few lines is to manually put a comment at the start left of lines to be replaced, and then shift those now comments a few spaces to the right, then start typing the replacement lines from the left, which then pushed those comments to the left
Pictorially this is what I mean for the simple case of wanting to replace just one line

Sub Original()
Dim Msg As String
 Let Msg = "Hello"
 MsgBox Prompt:="Hello shit World"
End Sub
Sub ModifiedByInsertingNewCodeLine()
Dim Msg As String
 Let Msg = "Hello"
 Debug.Print "Hello shit World" ' MsgBox Prompt:="Hello shit World"
End Sub

That’s easy for a few lines, but for a lot of lines it’s a tedious pain in the arse

.ps1 PowerShell text like code lines
Currently have some large .ps1 PowerShell files that I am inserting a lot of lines in and want to do it in the way I am talking about
In this post I will check out the format of the files first, in case there are any unusual “invisible” characters in them, since I have seen that in some PowerShell related text files ( https://excelfox.com/forum/showthrea...ll=1#post15357 https://excelfox.com/forum/showthrea...age3#post15357 )
Here is a File of 250 code line, that I want to put in,
Share ‘blockIPhostsRawAll250.ps1’ https://app.box.com/s/7019x59uvvxt7osvb0tojr0z4g7bfdgk ,
and here is just the first 4 lines for testing here,
Share ‘blockIPhosts4Lines.ps1’ https://app.box.com/s/37pjl9jfk9wue413xewgcjg1cdwk9dwb .
Here is a File I want to put that in from about line 200,
Share ‘Temp7BeforeIPhostsInsert.ps1’ https://app.box.com/s/fttlmwny6y4s5ub1q66kvqbrw2ppxdwz
, and here is just the first 4 lines of that for testing here,
Share ‘Temp7BeforeIPhostsInsertFirst4Lines.ps1’ https://app.box.com/s/bimxv550fpkoqndt8rz421s9vmcsorif .

Simple macro …_

'Here is a File of 250 code line, that I want to put in,
'Share ‘blockIPhostsRawAll250.ps1’ https://app.box.com/s/7019x59uvvxt7osvb0tojr0z4g7bfdgk ,
'and here is just the first 4 lines for testing here,
'Share ‘blockIPhosts4Lines.ps1’ https://app.box.com/s/37pjl9jfk9wue413xewgcjg1cdwk9dwb .
'Here is a File I want to put that in from about line 200,
'Share ‘Temp7BeforeIPhostsInsert.ps1’ https://app.box.com/s/fttlmwny6y4s5ub1q66kvqbrw2ppxdwz
', and here is just the first 4 lines of that for testing here,
'Share ‘Temp7BeforeIPhostsInsertFirst4Lines.ps1’ https://app.box.com/s/bimxv550fpkoqndt8rz421s9vmcsorif .
'
'
Sub LookInFirstBitOfTemp4andIPhosts() ' https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=16366&viewfull=1#post16366
' Get the text file as a long single string
Dim FileNum As Long: Let FileNum = FreeFile(1) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName As String, TotalFile As String
 Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & "test4lines.txt" ' CHANGE TO SUIT From vixer zyxw1234 : http://www.eileenslounge.com/viewtopic.php?f=30&t=34629 DF.txt https://app.box.com/s/gw941dh9v8sqhvzin3lo9rfc67fjsbic
Open PathAndFileName For Binary As #FileNum 'Open Route to data. Binary is a fundamental type data input...
 Let TotalFile = Space(LOF(FileNum)) '....and wot receives it has to be a string of exactly the right length
Get #FileNum, , TotalFile 'Debug.Print TotalFile
 'Let TotalFile = Replace(TotalFile, Chr(0), "", 1, -1, vbBinaryCompare) ' https://i.postimg.cc/t43HCQr9/Rather-a-lot-of-Chr-0-s.jpg
 'Let TotalFile = Replace(TotalFile, Chr(255) & Chr(254) & vbCr & vbLf, "", 1, 1, vbBinaryCompare)
Close #FileNum
 Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(TotalFile)
End Sub

Here is a view in the text editor of those first 4 lines, followed by what my WtchaGot_Unic_NotMuchIfYaChoppedItOff( function gives me

 Function BlochIPhosts {param([int]$Testie)
 $Check = $Testie
Write-Host "Adding telemetry domains to hosts file" # Write-Output "Adding telemetry domains to hosts file"
$hosts_file = "$env:systemroot\System32\drivers\etc\hosts"


"Function BlochIPhosts " & Chr(123) & "param" & "(" & Chr(91) & "int" & Chr(93) & "$" & "Testie" & ")" & vbCr & vbLf & " " & "$" & "Check " & "=" & " " & "$" & "Testie" & vbCr & vbLf & "Write" & "-" & "Host " & """" & "Adding telemetry domains to hosts file" & """" & " " & "#" & " Write" & "-" & "Output " & """" & "Adding telemetry domains to hosts file" & """" & vbCr & vbLf & "$" & "hosts" & "_" & "file " & "=" & " " & """" & "$" & "env" & ":" & "systemroot" & "\" & "System32" & "\" & "drivers" & "\" & "etc" & "\" & "hosts" & """"



Add-Type -AssemblyName System.Windows.Forms
[System.Windows.Forms.Application]::EnableVisualStyles() #
Remove-Variable * -ErrorAction SilentlyContinue # is needed or else a removed variable is still there when fucking about with variables. by default variables are persistant.
$ErrorActionPreference = 'SilentlyContinue'


"Add" & "-" & "Type " & "-" & "AssemblyName System" & "." & "Windows" & "." & "Forms" & vbLf & Chr(91) & "System" & "." & "Windows" & "." & "Forms" & "." & "Application" & Chr(93) & ":" & ":" & "EnableVisualStyles" & "(" & ")" & " " & "#" & vbLf & "Remove" & "-" & "Variable " & Chr(42) & " " & "-" & "ErrorAction SilentlyContinue " & "#" & " is needed or else a removed variable is still there when fucking about with variables" & "." & " by default variables are persistant" & "." & vbLf & "$" & "ErrorActionPreference " & "=" & " " & "'" & "SilentlyContinue" & "'"

Here is a screenshot of the two files in Notepad text editor


So, no major problems or surprises, ( or so I thought – see next post )
It looks like a different line separator is used sometimes. But we have seen this before.
I have a slight personal tendency to prefer the two character line separator, vbCr & vbLf , and also the first file was the main one in which the other will be inserted, so I will do a simple conversion to the file to be inserted to replace all vbLf with vbCr & vbLf


One small interesting note. I don’t have a last trailing line feed character or characters. That is not so usual, at least I have not experienced it yet so far in playing around with .csv and other text files