Avatar of jamiepryer
jamiepryerFlag for United Kingdom of Great Britain and Northern Ireland asked on

VB Script to search multiple txt files for results from an excel list

Hi,
I have a list of 24 different txt files (size from 4kb - 366kb)
I also have an excel file which is a list of 6628 lines, from cells A1:A6628

I want to search each of the excel files to see if value in the excel file is listed in them.
If so, i want to display the txtfile name next to it, in cell Bx

The other thing, which should hopefully make the script run faster, is that if a result is found in 1 txt document, i dont need it to seach that result again in any of the future txt documents, so hopefully as the script moves on, the list to seach on is smaller and so runs faster?

Can someone point me in the right direction, as i have no idea.

If its easier, i dont mind running this script on each of the text files 1 by 1, to make the code less complex, just using a simple sFileName = "C:\Data\datadump\test.txt"  or something.
VB Script

Avatar of undefined
Last Comment
RobSampson

8/22/2022 - Mon
ASKER
jamiepryer

got this so far, but not sure if its actually any good (maybe its very slow?)
plus, i cant work out how to loop back to the BOF, when it moves onto the next adgroup


Sub test2()
Dim TextLine As String
 
Open "C:\Data\datadump\test.txt" For Input As #1
 
Dim ADgroup As Long
 
    For ADgroup = Range("A65536").End(xlUp).Row To 2 Step -1
                    
            Do While Not EOF(1)
            
                    Line Input #1, TextLine 'Text of this line will be stored in variable TextLine
                      
                        If TextLine = Range("A" & ADgroup).Value Then
                            Range("B" & ADgroup).Value = "Found"
                            'Next ADgroup
                        End If
                     
                     
            Loop
            'how do i now go back to the BOF?
        Next ADgroup
    
Close #1
 
End Sub

Open in new window

ASKER
jamiepryer

this seems to do the job, but not sure if its the quickest/best way to do things...
Dim FolderName As String
Private Type BROWSEINFO ' used by the function GetFolderName
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszTitle As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
End Type
 
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
    Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long
Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
    Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long
    
    Function GetFolderName(Msg As String) As String
 
' returns the name of the folder selected by the user from the popup box
 
Dim bInfo As BROWSEINFO, path As String, r As Long
Dim X As Long, pos As Integer
    bInfo.pidlRoot = 0& ' Root folder = personal (My Documents)
            'Other options for this setting, just change the number for bInfo.pidlRoot, dont lose the & tho!
            '0=My Computer
            '1=IE
            '2=Programs
            '3=ControlPanel
            '4=Printers
            '5=Personal (My Documents)
            '6=Favorites
            '7=Startup
            '8=Recent
            '9=SendTo
            '10=RecycleBin
            '11=StartMenu
       
    If IsMissing(Msg) Then
        bInfo.lpszTitle = "Select a folder."
        ' the dialog title
    Else
        bInfo.lpszTitle = Msg ' the dialog title
    End If
    bInfo.ulFlags = &H1 ' Type of directory to return
    X = SHBrowseForFolder(bInfo) ' display the dialog
    ' Parse the result
    path = Space$(512)
    r = SHGetPathFromIDList(ByVal X, ByVal path)
    If r Then
        pos = InStr(path, Chr$(0))
        GetFolderName = Left(path, pos - 1)
    Else
        GetFolderName = ""
    End If
End Function
    
Private Sub TestGetFolderName()
 
'function to ask the user what folder they want to run this script on.
'Gives the user the option to confirm, cancel, change and end the script.
 
 FolderName = GetFolderName("Please select the folder where your Excel files are saved")
   If FolderName = "" Then
       MsgBox ("You didn't select a folder, operation cancelled"), vbExclamation, ""
       End
    Else
    YesNo = MsgBox("Are you SURE you want to run this scrip on ALL xls files in this folder: " & FolderName & " this CANNOT be undone", vbYesNoCancel + vbExclamation, "Caution")
          Select Case YesNo
           Case vbYes
           FolderName = FolderName
           Case vbNo
           Application.run "TestGetFolderName"
           Case vbCancel
           MsgBox ("Operation cancelled"), vbCritical, ""
           End
           End Select
 
    End If
End Sub
 
Sub test3()
Dim TextLine As String
Dim StartOver As Boolean
 
Application.ScreenUpdating = False
Application.run ("TestGetFolderName")
MyPath = FolderName
ChDir MyPath
TheFile = Dir("*.txt")
Do While TheFile <> ""
 
            Open MyPath & "\" & TheFile For Input As #1
            
            Dim ADgroup As Long
             
                For ADgroup = Range("A65536").End(xlUp).Row To 2 Step -1
                     
                        Do While Not EOF(1)
                        
                                Line Input #1, TextLine 'Text of this line will be stored in variable TextLine
                                  
                                    If TextLine = Range("A" & ADgroup).Value Then
                                        Range("B" & ADgroup).Value = "Found"
                                       Seek 1, 999999999
                                    End If
                        Loop
                      
                       Seek 1, 1
                                    
                        'how do i now go back to the BOF?
                    Next ADgroup
                  
            Close #1
TheFile = Dir
Loop '
Application.ScreenUpdating = True
MsgBox "Completed"
 
End Sub

Open in new window

RobSampson

Hi, I would guess that replacing this:
'how do i now go back to the BOF?

with this
Close #1

would help you, because it would close the file, then for the next record, you'd be re-opening the file.

Regards,

Rob.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
jamiepryer

Hi,
that didnt seem to work.
ive got the following which does now work, however its clearly too slow to do it
im seaching each line in my excel sheet, against each line in the txt doc
i need to do a find in the txt doc instead really.

Sub test2()
Dim TextLine As String
Dim StartOver As Boolean
 
'this will loop though all users in columnA and check to see if they are in the txt file
Open "C:\Data\datadump\test\test.txt" For Input As #1
 
Dim ADgroup As Long
 
    For ADgroup = Range("A65536").End(xlUp).Row To 2 Step -1
         
            Do While Not EOF(1)
            
                    Line Input #1, TextLine 'Text of this line will be stored in variable TextLine
                      
                        If TextLine >= Range("A" & ADgroup).Value Then
                            Range("B" & ADgroup).Value = "Found"
                            'EOF (1)
                            Seek 1, 99999999
                        End If
            Loop
            Seek 1, 1
        Next ADgroup  
Close #1
MsgBox "done"
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
RobSampson

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question