Code:
' https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA/page8#post16491
Sub testieIt() ' https://excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA?p=16491&viewfull=1#post16491
Call MergeScriptFiles("Temp7BeforeIPhostsInsert.ps1", "blockIPhostsRawAll250.ps1", 201, , "Temp8.ps1")
End Sub
' ByVal TxtM As String, ByVal TxtInst As String, ByVal StRw As Long, Optional ByVal Rws As Long, Optional ByVal FlNmeOut As String
Public Function MergeScriptFiles(ByVal TxtM As String, ByVal TxtInst As String, ByVal StRw As Long, Optional ByVal Rws As Long, Optional ByVal FlNmeOut As String)
Rem 1 main file
' 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 & TxtM ' 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
Close #FileNum 'Debug.Print TotalFile
Let TotalFile = Replace(TotalFile, vbCr & vbLf, vbLf, 1, -1, vbBinaryCompare)
Let TotalFile = Replace(TotalFile, vbLf, vbCr & vbLf, 1, -1, vbBinaryCompare)
' make a 1 D array of the Main text file
Dim arrRwsM() As String: Let arrRwsM() = Split(TotalFile, vbCr & vbLf, -1, vbBinaryCompare)
Rem 2 file to insert
Let PathAndFileName = ThisWorkbook.Path & Application.PathSeparator & TxtInst ' 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
Close #FileNum 'Debug.Print TotalFile
Let TotalFile = Replace(TotalFile, vbCr & vbLf, vbLf, 1, -1, vbBinaryCompare)
Let TotalFile = Replace(TotalFile, vbLf, vbCr & vbLf, 1, -1, vbBinaryCompare)
' make a 1 D array of the file to insert
Dim arrRws() As String: Let arrRws() = Split(TotalFile, vbCr & vbLf, -1, vbBinaryCompare)
Rem 3 tack on lines from file to merge
If Rws = 0 Then Let Rws = UBound(arrRws()) + 1 ' This will cause all lines to be merged if no number of lines given
Dim LnNbr As Long
For LnNbr = StRw To Rws + StRw - 1 ' This - 1 is the usual getting end row from start row and count of rows issue - its always the start row + ( Rows count - 1 )
Let arrRwsM(LnNbr - 1) = arrRws(LnNbr - StRw) & arrRwsM(LnNbr - 1) ' The - 1 here is because the one dimensional arrays start at 0, so the lines we are intersted in are 1 element back from where we might have expected them
Next LnNbr ' LnNbr - StRw takes us from 0 to 1 less than our maximumn row number - that is exactly the 0 to (1 less than our maximumn row number)
' 3b That seems to have done it, now we just need to remake the merged text file,
' Make text file, alll we need to do is make the single long string including the line breaks that
Let TotalFile = Join(arrRwsM(), vbCr & vbLf)
Rem 4 Output file
Dim FileNum2 As Long: Let FileNum2 = FreeFile(0) ' https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/freefile-function
Dim PathAndFileName2 As String
If FlNmeOut = "" Then Let FlNmeOut = Left(TxtM, InStrRev(TxtM, ".") - 1) & "Merge" & Format(Now(), "dd,mmmyyyy") & ".ps1"
Let PathAndFileName2 = ThisWorkbook.Path & "\" & FlNmeOut ' ' CHANGE TO SUIT ' Will be made if not there
Open PathAndFileName2 For Output As #FileNum2
Print #FileNum2, TotalFile ' write out entire text file
Close #FileNum2
End Function
Reply With Quote
Bookmarks