5 Attachment(s)
Search for text in txt File using VBA, display rows where text found
Code in support of these Threads:
http://www.excelfox.com/forum/showth...0582#post10582
What code does in General:
This code will search for specific text in a text file
What code does in Specifically:
The code assumes that you have a simple text file looking something like this:
TextRowsInTextFile.jpg : https://imgur.com/upBY709
Attachment 2031
HotFixID
{EF8CD7FC-438D-49E3-A2C7-201052D9F2EF}
{8D2CDFAB-0079-43CC-A289-2F7A67F0A4DE}
{98D8F490-1F42-4F29-A59B-BF96D23A11BA}
{B730F010-3FCF-4E80-8A5A-C1DBEC0CF55A}
{B73E5AF4-40C6-4EA9-8F57-CFA70CC72BD6}
{BF11577A-6876-45AA-86C9-2BA4CFB8B019}
{E359D786-B101-4545-B8AB-8652323CF3CA}
{F4139440-5426-4C6F-909B-F71CEB1071B1}
{B2FAD7E1-67F9-435D-98BD-A77DBF4E1381}
Here is the example text file used in this explanation and currently hard coded into the code : “UpdatesOnVistaAspire4810TZG25thMarch.txt” : https://app.box.com/s/z90o8yj7iz0188yci34mu7gahe2tfhce
You can input , when prompted, a text string or text strings to look for. For more than one text string you should separate them by at least one space, like
__ B23 ___6872 35689
( The code below has those actual strings hard coded as the default search values )
Input Box Functioning.jpg : https://imgur.com/o9wlnhK https://imgur.com/JtnTDmy
Attachment 2030 Attachment 2034
The code will look for those text strings in all text file lines except the first.
( there is also a section to check the content of the first line, but it is 'commented out in the code below )
The code searches for those lines which contain any of those strings. In this demo example, one thing that I would be looking for is the rows in the text file containing B23 in them, so that would be the middle few in this screenshot .. B23 TextRowsInTextFile.JPG : https://imgur.com/JHRqJJc
Attachment 2032
The final result of the codes is to give you a string message which has a list of the text strings that you were looking for, and a list of the full text in any rows which contained that. The string is displayed in a message box. In addition if you are in the VB Editor Window and hit Ctrl+g , the you will see the results also in the immediate window. This latter has the advantage that you can copy the data to the clipboard by highlighting it and hitting Ctrl+c , ( or alternatively select the text and select the option to copy available via right mouse click ) : YouLookedForFindedWas.JPG: https://imgur.com/tyW4HSJ
Attachment 2033
Here is the code. It should be pasted into any File which is in the same Folder as the text file you want to search through. Currently the code is hard coded to search the file with name
“UpdatesOnVistaAspire4810TZG25thMarch.txt”
So you will need to change that to suit your text file name.
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
Some typical results in next post
5 Attachment(s)
Finding and deleteing .exd files
Appendix notes in support of these Threads:
http://www.excelfox.com/forum/showth...en-insert-them
http://www.excelfox.com/forum/showth...ommand-Buttons
Trying to find .exd files and delete them.
The results of most of what I have read or results of asking people suggests that they are usually findable if you look for a temp or temp somewhere in a file path
These appear a bit difficult to find sometimes . You can try:
_ manually navigating
_ a windows explorer search for *.exd
StarDotexeExplorerSearch.JPG : https://imgur.com/hfbC93Z
Attachment 2037
_ a search in a small bar using %temp% or %Temp%
You can get the small bar from either hitting WindowsKey+r or by selecting the Windows symbol
PerCenttempPerCentsearch.JPG : https://imgur.com/LypHLGY
Attachment 2038
PerCenttempPerCentsearch2.jpg : https://imgur.com/DZvycco
Attachment 2039
It seems a bit inconsistent which search finds what, but usually it is said that you find important places looking something like these:
C:\Users\username\AppData\Local\Temp\Excel8.0
C:\Users\username\AppData\Local\Temp\VBE
I found sometimes .exd files here also
C:\Users\username\Application Data\Microsoft\Forms
Some other typical places I found
C:\Dokumente und Einstellungen\Administrator\Application Data\Microsoft\Forms
C:\Dokumente und Einstellungen\Administrator\AppData\LocalLow
_.____._____________________
This is one of my typical attempts to get a ActiveX control button to_... either
_ insert into worksheet
or , if already three
_ work
_... by deleting .exd files
What I typically tried:
I looked here C:\Users\Elston\AppData\Local\Temp\Excel8.0
I found this:
C Users Elston AppData Local Temp Excel8.0.jpg https://imgur.com/doXstmr
Attachment 2040
I deleted that MSForms.exd File ( Excel had to be closed to do that ) : It had no effect. ( By the way, MSForms.exd gets made again every time I hit the button, or it appears to get made as soon as I open any file that either has or has ever had a control embedded in a worksheet)
In C:\Users\Elston\AppData\Local\Temp\VBE I found these:
C Users Elston AppData Local Temp VBE.jpg https://imgur.com/wjaZpXp
Attachment 2041
So…for these files I did:…
MSComctlLib.exd : I deleted this , - no effect
MSForms.exd : This could not be deleted with Excel 2003 open, it can when it is closed. But that had no effect
RefEdit.exd : This could not be deleted with Excel 2003 open, it can when it is closed. But no that had effect
( By the way, MSForms.exd and RefEdit.exd get made again every time I hit the button, or it appears to get made as soon as I open any file that either has, or has ever had, a control embedded in a worksheet )
In C:\Users\Elston\Application Data\Microsoft\Forms
I found these:
( https://imgur.com/Lv2kyhk )
So……for these files I did:…
RefEdit.exd This could not be deleted with Excel 2003 open, it can when it is closed. But that had no effect
mscomctllib.exd I deleted this: That had no effect
SHDocVw.exd I deleted this: That had no effect
( By the way, MSForms.exd gets made again every time I hit the button, or it appears to get made as soon as I open any file that either has or has ever had a control embedded in a worksheet)
_.._______________-
So for me none of that helped to get me a working ActiveX control Button in a worksheet.
:(
Master File After First Consolidation
Using Excel 2007 32 bit
S No |
Item |
Price |
Qty |
Total |
Distributed |
Task1 |
Task2 |
Task3 |
Task4 |
Completed |
Consolidated |
Comments |
Team Member |
Checked |
1 |
ABC01 |
$ 55.00 |
22 |
$ 1,210.00 |
02.Apr.2018 |
RT1 |
RT2 |
RT3 |
RT4 |
02.04.2018 |
02.Apr.2018 |
|
Raghu |
|
2 |
ABC02 |
$ 13.66 |
7 |
$ 95.62 |
02.Apr.2018 |
JT1 |
JT2 |
JT3 |
JT4 |
02.Apr.2018 |
02.Apr.2018 |
|
John |
|
3 |
ABC03 |
$ 12.99 |
5 |
$ 64.95 |
02.Apr.2018 |
GT1 |
GT2 |
GT3 |
GT4 |
02.Apr.2018 |
02.Apr.2018 |
|
Greg |
|
4 |
ABC04 |
$ 8.51 |
12 |
$ 102.12 |
02.Apr.2018 |
MT1 |
MT2 |
MT3 |
MT4 |
02.Apr.2018 |
02.Apr.2018 |
|
Margaret |
|
5 |
ABC05 |
$ 7.22 |
62 |
$ 447.64 |
02.Apr.2018 |
RT1 |
RT2 |
RT3 |
RT4 |
02.04.2018 |
02.Apr.2018 |
|
Raghu |
|
6 |
ABC06 |
$ 3.99 |
35 |
$ 139.65 |
02.Apr.2018 |
JT1 |
JT2 |
JT3 |
JT4 |
02.Apr.2018 |
02.Apr.2018 |
|
John |
|
7 |
ABC07 |
$ 333.45 |
99 |
$ 33,011.55 |
02.Apr.2018 |
GT1 |
GT2 |
GT3 |
GT4 |
02.Apr.2018 |
02.Apr.2018 |
|
Greg |
|
8 |
ABC08 |
$ 11.99 |
1 |
$ 11.99 |
02.Apr.2018 |
MT1 |
MT2 |
MT3 |
MT4 |
02.Apr.2018 |
02.Apr.2018 |
|
Margaret |
|
9 |
ABC09 |
$ 741.99 |
101 |
$ 74,940.99 |
02.Apr.2018 |
RT1 |
RT2 |
RT3 |
RT4 |
02.04.2018 |
02.Apr.2018 |
|
Raghu |
|
10 |
ABC10 |
$ 55.00 |
22 |
$ 1,210.00 |
02.Apr.2018 |
JT1 |
JT2 |
JT3 |
JT4 |
02.Apr.2018 |
02.Apr.2018 |
|
John |
|
11 |
ABC11 |
$ 13.66 |
7 |
$ 95.62 |
02.Apr.2018 |
GT1 |
GT2 |
GT3 |
GT4 |
02.Apr.2018 |
02.Apr.2018 |
|
Greg |
|
12 |
ABC12 |
$ 12.99 |
5 |
$ 64.95 |
02.Apr.2018 |
MT1 |
MT2 |
MT3 |
MT4 |
02.Apr.2018 |
02.Apr.2018 |
|
Margaret |
|
13 |
ABC13 |
$ 8.51 |
12 |
$ 102.12 |
02.Apr.2018 |
RT1 |
RT2 |
RT3 |
RT4 |
02.04.2018 |
02.Apr.2018 |
|
Raghu |
|
14 |
ABC14 |
$ 7.22 |
62 |
$ 447.64 |
02.Apr.2018 |
JT1 |
JT2 |
JT3 |
JT4 |
02.Apr.2018 |
02.Apr.2018 |
|
John |
|
15 |
ABC15 |
$ 3.99 |
35 |
$ 139.65 |
02.Apr.2018 |
GT1 |
GT2 |
GT3 |
GT4 |
02.Apr.2018 |
02.Apr.2018 |
|
Greg |
|
16 |
ABC16 |
$ 333.45 |
99 |
$ 33,011.55 |
02.Apr.2018 |
MT1 |
MT2 |
MT3 |
MT4 |
02.Apr.2018 |
02.Apr.2018 |
|
Margaret |
|
17 |
ABC17 |
$ 11.99 |
1 |
$ 11.99 |
02.Apr.2018 |
RT1 |
RT2 |
RT3 |
RT4 |
02.04.2018 |
02.Apr.2018 |
|
Raghu |
|
18 |
ABC18 |
$ 741.99 |
101 |
$ 74,940.99 |
02.Apr.2018 |
JT1 |
JT2 |
JT3 |
JT4 |
02.Apr.2018 |
02.Apr.2018 |
|
John |
|
19 |
ABC19 |
$ 55.00 |
22 |
$ 1,210.00 |
02.Apr.2018 |
GT1 |
GT2 |
GT3 |
GT4 |
02.Apr.2018 |
02.Apr.2018 |
|
Greg |
|
20 |
ABC20 |
$ 13.66 |
7 |
$ 95.62 |
02.Apr.2018 |
MT1 |
MT2 |
MT3 |
MT4 |
02.Apr.2018 |
02.Apr.2018 |
|
Margaret |
|
21 |
ABC21 |
$ 12.99 |
5 |
$ 64.95 |
02.Apr.2018 |
|
|
|
|
|
|
|
Raghu |
|
22 |
ABC22 |
$ 8.51 |
12 |
$ 102.12 |
02.Apr.2018 |
JT1 |
JT2 |
JT3 |
JT4 |
02.Apr.2018 |
02.Apr.2018 |
|
John |
|
23 |
ABC23 |
$ 7.22 |
62 |
$ 447.64 |
02.Apr.2018 |
|
|
|
|
|
|
|
Greg |
|
24 |
ABC24 |
$ 3.99 |
35 |
$ 139.65 |
02.Apr.2018 |
MT1 |
MT2 |
MT3 |
MT4 |
02.Apr.2018 |
02.Apr.2018 |
|
Margaret |
|
25 |
ABC25 |
$ 333.45 |
99 |
$ 33,011.55 |
02.Apr.2018 |
|
|
|
|
|
|
|
Raghu |
|
26 |
ABC26 |
$ 11.99 |
1 |
$ 11.99 |
02.Apr.2018 |
|
|
|
|
|
|
|
John |
|
27 |
ABC27 |
$ 741.99 |
101 |
$ 74,940.99 |
02.Apr.2018 |
|
|
|
|
|
|
|
Greg |
|
28 |
ABC28 |
$ 55.00 |
22 |
$ 1,210.00 |
02.Apr.2018 |
MT1 |
MT2 |
MT3 |
MT4 |
02.Apr.2018 |
02.Apr.2018 |
|
Margaret |
|
29 |
ABC29 |
$ 13.66 |
7 |
$ 95.62 |
02.Apr.2018 |
|
|
|
|
|
|
|
Raghu |
|
30 |
ABC30 |
$ 12.99 |
5 |
$ 64.95 |
02.Apr.2018 |
|
|
|
|
|
|
|
John |
|
31 |
ABC31 |
$ 8.51 |
12 |
$ 102.12 |
02.Apr.2018 |
|
|
|
|
|
|
|
Greg |
|
32 |
ABC32 |
$ 7.22 |
62 |
$ 447.64 |
02.Apr.2018 |
|
|
|
|
|
|
|
Margaret |
|
33 |
ABC33 |
$ 3.99 |
35 |
$ 139.65 |
02.Apr.2018 |
|
|
|
|
|
|
|
Raghu |
|
34 |
ABC34 |
$ 333.45 |
99 |
$ 33,011.55 |
02.Apr.2018 |
|
|
|
|
|
|
|
John |
|
35 |
ABC35 |
$ 11.99 |
1 |
$ 11.99 |
02.Apr.2018 |
|
|
|
|
|
|
|
Greg |
|
36 |
ABC36 |
$ 741.99 |
101 |
$ 74,940.99 |
02.Apr.2018 |
|
|
|
|
|
|
|
Margaret |
|
37 |
ABC37 |
$ 55.00 |
22 |
$ 1,210.00 |
02.Apr.2018 |
|
|
|
|
|
|
|
Raghu |
|
38 |
ABC38 |
$ 13.66 |
7 |
$ 95.62 |
02.Apr.2018 |
|
|
|
|
|
|
|
John |
|
39 |
ABC39 |
$ 12.99 |
5 |
$ 64.95 |
02.Apr.2018 |
|
|
|
|
|
|
|
Greg |
|
40 |
ABC40 |
$ 8.51 |
12 |
$ 102.12 |
02.Apr.2018 |
|
|
|
|
|
|
|
Margaret |
|
41 |
ABC41 |
$ 7.22 |
62 |
$ 447.64 |
02.Apr.2018 |
|
|
|
|
|
|
|
Raghu |
|
42 |
ABC42 |
$ 3.99 |
35 |
$ 139.65 |
02.Apr.2018 |
|
|
|
|
|
|
|
John |
|
43 |
ABC43 |
$ 333.45 |
99 |
$ 33,011.55 |
02.Apr.2018 |
|
|
|
|
|
|
|
Greg |
|
44 |
ABC44 |
$ 11.99 |
1 |
$ 11.99 |
02.Apr.2018 |
|
|
|
|
|
|
|
Margaret |
|
45 |
ABC45 |
$ 741.99 |
101 |
$ 74,940.99 |
02.Apr.2018 |
|
|
|
|
|
|
|
Raghu |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
