Notes tests, string, manipulation of text files and string manipulations

[DocAElstein]

In support of this main forum Thread
https://excelfox.com/forum/showthrea...nto-excel-cell
_1) create new number and place in cell B1 according to last serial number in csv file,


Before
ExcelFile:
_____ Workbook: SerialNumbers.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C
1	Serial# :	TTR0000001
2		TTR0000002
3		TTR0000003
4

Worksheet: Sheet1

Text File:

TTR0000001
TTR0000002
TTR0000003

Run macro:

Sub NewSN()
' Rem 1 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 & "serial_number.csv"   '
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
'  Or  http://www.eileenslounge.com/viewtopic.php?p=295782&sid=f6dcab07c4d24e00e697fe4343dc7392#p295782
 Let TotalFile = Input(LOF(FileNum), FileNum)
Close #FileNum

Rem 2 determine a new number
'2a Current number
    If Right(TotalFile, 2) = vbCr & vbLf Then Let TotalFile = Left(TotalFile, Len(TotalFile) - 2)                                         ' Take off last line feed
Dim PosLstLineFeed As Long: Let PosLstLineFeed = InStrRev(TotalFile, vbCr & vbLf, -1, vbBinaryCompare)
Dim CrntNmbr As String: Let CrntNmbr = Mid(TotalFile, PosLstLineFeed + 2)
 Let CrntNmbr = Replace(CrntNmbr, "TTR", "", 1, -1, vbBinaryCompare)
'2b creat new number
 Let CrntNmbr = CrntNmbr + 1
 Let CrntNmbr = Format(CrntNmbr, "0000000")
 Let CrntNmbr = "TTR" & CrntNmbr

Rem 3 Put new number in Excel file
Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets.Item(1)
Dim LrB As Long: Let LrB = Ws1.Range("B" & Ws1.Rows.Count & "").End(xlUp).Row
 Let Ws1.Range("B" & LrB + 1 & "").Value = CrntNmbr
End Sub

After:

_____ Workbook: SerialNumbers.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C
1	Serial# :	TTR0000001
2		TTR0000002
3		TTR0000003
4		TTR0000004
5

Worksheet: Sheet1