Code:
Sub CheqUpDates()
On Error GoTo GetLaid ' Instruction to replace / modify VBA default error handler by hanging on to the arousal this code starting from the labelled label code area
Rem 1) ActiviaExcretionLink, AEL. Checking Object link mechanismus
'1a) Exposing of interfaces for active RunableTimed data axctivated link
Dim ActiviEL As String ' "Pointer" to a "Blue Print" (or Form, Questionnaire not yet filled in, a template etc.)"Pigeon Hole" in Memory, sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular “Value”, or (“Values” for Objects). There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. A String is a bit tricky. The Blue Print code line Paper in the Pigeon Hole will allow to note the string Length and an Initial start memory Location. This Location well have to change frequently as strings of different length are assigned. Instructions will tell how to do this. Theoretically a special value vbNullString is set to aid in quick checks.. But..http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html#post44116
Let ActiviEL = ThisWorkbook.Path & "\UpdatesOnVistaAspire4810TZG25thMarch.txt" 'Will be referrenced in code through an opened "route" to it
Dim LedgerFreiNummer As String: Let LedgerFreiNummer = "1" & "00" ' Not required in this code : https://www.excelforum.com/excel-general/1225401-value-of-true-1-or-1-vba-vs-worksheet.html
Dim AEL_Highway As Long: Let AEL_Highway = FreeFile("" & LedgerFreiNummer & "") ' Obtain from 2nd building phase (256-511) Ledger of available Highways, coercidentally to value 1_255 likely , bits of my 1 & 00
Rem 2) text file info
' '2a) Open File read first line check the sht - want Head
' Open ActiviEL For Input As AEL_Highway '
' Dim ShtHead As String
' Line Input #AEL_Highway, ShtHead ' Check substancialating for getting good Head
' If InStr(1, ShtHead, "HotFix", vbTextCompare) = 0 Then
' MsgBox prompt:="Got no HotFix IDin " & ShtHead
' Exit Sub
' Else
' Debug.Print ShtHead
' End If
' Close AEL_Highway ' Datei scheißen
'2b) "row" count in text file
Dim RecardRows As Long ' '_-' Long is very simple to handle, - final memory "size" type is known (123.456 and 000.001 have same "size" computer memory ) , and so a Address suggestion can be given for the next line when the variable is filled in. '( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. ) https://www.mrexcel.com/forum/excel-questions/803662-byte-backward-loop-4.html
Let RecardRows = 0
Dim strLine As String
Open ActiviEL For Input As AEL_Highway ' Activated embedded Link objectimocom Binary as to referencingmocomed aka AliAs AEL_Highway opened of now
Do Until EOF(AEL_Highway) 'Looping all lines in text file ' Solange bis Datei-Ende - EOF(AEL_Highway) will be set to true by the last a carriage liney mo not found a next line in Line Input #AEL_Highway, strLine
Line Input #AEL_Highway, strLine: Let RecardRows = RecardRows + 1 ' Inputed der liney mo a carriage returned after then record register count of it to that increase by the one done liney mo
Loop 'Do Until EOF(AEL_Highway) 'Looping all lines in text file
'Let RecardRows = RecardRows + 1 'would need to do this if I did not closeat '2a) and reopen in '2b)
Close AEL_Highway ' Datei scheißen - scheise drauf der Highway geschnmut - no longer activamoed AEL not activia mated mo
Rem 3) Prepare output Array for all text File data
Dim arrOut() As String: ReDim arrOut(1 To RecardRows) ' can declare to known size and type. We cannot use Dim arrOut(1 to RecardRows) as pre complie compile cannot do the RecardRows is not available: method ReDim is Runtime
Rem 4) Main loop for filling in Output Data =============================================
Open ActiviEL For Input As AEL_Highway
Dim RecardRow As Long ', strLine As String
For RecardRow = 1 To RecardRows '(Do Until EOF(AEL_Highway) 'Looping all lines in text file)
Line Input #AEL_Highway, strLine: Let arrOut(RecardRow) = strLine ' Zeile lesen - as before but this time place in element of output array
Next RecardRow ' ===== (Do Until EOF(AEL_Highway) 'Looping all lines in text file)===
Close AEL_Highway ' Datei schließen
Rem 5) search for specific strings
'5a) Bring in text or texts to be searched for, reduce multiple spaces to single spaces between if more than one given and, and split into array of those individual text strings https://powerspreadsheets.com/excel-vba-inputbox/ http://www.excelfox.com/forum/showthread.php/2227-VBA-Input-Pop-up-Boxes-Application-InputBox-Method-versus-VBA-InputBox-Function?p=10462#post10462
Dim strSrch As String '
Let strSrch = VBA.InputBox(prompt:="Type in all or part of text or texts to be searched for" & vbCrLf & "Seperate texts by at least one space", Title:="Input text to be searched for in text File lines", Default:="KB23 6872 35689", xpos:=100, ypos:=100)
Let strSrch = Evaluate("=TRIM(SUBSTITUTE(" & """" & strSrch & """" & ",CHAR(32)," & """" & " " & """" & "))") ' TRIM function trims the 7-bit ASCII space character (value 32). In the Unicode character set, there is an additional space character called the nonbreaking space character that has a decimal value of 160. This character is commonly used in Web pages as the HTML entity, . By itself, the TRIM function does not remove this nonbreaking space character. https://www.excelforum.com/excel-formulas-and-functions/1217202-is-there-a-function-similar-to-trim-but-that-only-removes-trailing-spaces-2.html
Dim SrchTxts() As String ' VBA strings function split to be used to get individual text into elements of an Array. The split function returns an array of string type elements
Let SrchTxts() = VBA.Split(strSrch, " ", -1, vbTextCompare) ' Split the ( strSrch , using space as delimiter , for unrestricted count , using text compare which is case insensitive )
For RecardRow = 2 To RecardRows 'At each record row
Dim Txtie As Long ' in default example this is 0 1 2
For Txtie = 0 To UBound(SrchTxts()) ' VBA Split retuns a 1 dimension array starting at indicie 0 For example we have indicies of 0 1 2 givig three elements in total of KB23 6872 35689
Dim strFnded As String
If InStr(1, arrOut(RecardRow), SrchTxts(Txtie), vbTextCompare) > 0 Then Let strFnded = strFnded & vbCrLf & arrOut(RecardRow) ' The returned postion along from the left ( starting from fist character , in the current row , looking for current text string , compare text which is case insensitive ) This will return 0 if not found and if found the postione along from the left in the row string where the search string part starts. So an found position will do for a find
Next Txtie
Next RecardRow
Rem 6) Display search results
Let strSrch = Replace(strSrch, " ", vbCrLf, 1, -1, vbBinaryCompare) 'replace in ( strSrch , space , with carriage return , start at and return from first character , no resriction on count , compare of exact computer memory so effectively case sensitive which is probably faster ) for convinent string list in output later
MsgBox prompt:="You looked for" & vbCrLf & strSrch & vbCrLf & vbCrLf & "Finded was" & strFnded
Debug.Print "You looked for" & vbCrLf & strSrch & vbCrLf & vbCrLf & "Finded was" & strFnded
Exit Sub ' Normal code ending
GetLaid: ' "Error handling code section http://www.excelfox.com/forum/showthread.php/2239-Resume-On-Error-GoTo-0-1-GoTo-Error-Handling-Statements-Runtime-VBA-Error-Handling-ORNeRe-GoRoT-N0Nula-1
MsgBox (Err.Description)
Close AEL_Highway ' Datei scheißen
End Sub
Reply With Quote
Bookmarks