Excel Search

I have a text file (computers.txt) which has a list of computer names listed as one column. I have Excel worksheet that has a column named computers that also lists  computer names.

I need kind of a script that reads from the text file , the computer name and do a search on the excel spreadsheet if it is not found, it should give me a message that this computer does not exist in  the spread sheet.

Who is Participating?
dlmilleConnect With a Mentor Commented:
This app has a worksheet with a column of computer names, a  couple columns over is a placholder for putting computers from txt files that don't match the original list in the spreadsheet.  You are prompted for the .txt file, in anticipation that you might be reading more than one text file, in sequence.  Thus, you also have the option to clear the output list, or append, as you go.

here's the code:
Sub ReadTXTAndProcess()
Dim mySheet As Worksheet
Dim myCell As Range
Dim fName As String
Dim wholeLine As String
Dim varInput As Variant
Dim outCursor As Range
Dim i As Long, colonPos As Long
Dim fRange As Range, searchRng As Range
Dim xMsg As Long

    Set mySheet = ThisWorkbook.Sheets("Sheet1") 'setup output sheet
    Set outCursor = mySheet.Range("C2") 'where to start writing output
    Set searchRng = mySheet.Range("A:A") 'where to look for computers
    If mySheet.Cells(mySheet.Rows.Count, outCursor.Column).End(xlUp).Row > 1 Then
        xMsg = MsgBox("Clear Outputs, before processing?", vbYesNo, "Hit Yes to clear, No to append")
        If xMsg = vbYes Then
            mySheet.Range(outCursor, mySheet.Cells(mySheet.Rows.Count, outCursor.Column).End(xlUp)).ClearContents 'clear for next run
            Set outCursor = mySheet.Range("C2") 'where to start writing output
            Set outCursor = mySheet.Range("C" & mySheet.Rows.Count).End(xlUp).Offset(1, 0) 'where to start appending output
        End If
    End If
    fName = Application.GetOpenFilename(filefilter:="Text Files (*.txt), *.txt", MultiSelect:=False)
    If fName = "False" Then Exit Sub
    Open fName For Input As #1 'read the text file line by line
    While Not EOF(1) 'read each line until end of file
        Line Input #1, wholeLine 'puts each line into the string variable wholeLine
        'processing of wholeLine commences, here...

        'use FIND to see if the computer exists in column A of the worksheet
        Set fRange = searchRng.Find(what:=wholeLine, LookIn:=xlFormulas, lookat:=xlWhole, MatchCase:=False)
        If fRange Is Nothing Then 'not found, so advise in spreadsheet
            outCursor.Value = wholeLine
            Set outCursor = outCursor.Offset(1, 0)
        End If
    Close #1 'tidy up, closing the file

End Sub

Open in new window


jskfanAuthor Commented:
I am getting error: Expected end of statement
dlmilleConnect With a Mentor Commented:
Are you running this macro from the spreadsheet?

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

jskfanAuthor Commented:
No, I thought it was VBS code
dlmilleConnect With a Mentor Commented:
No.  Not sure I interpreted "I need some type of script" correctly.  Must this be run from VBS?

jskfanAuthor Commented:
Let me make it simpler.

I copy the column from excel worksheet to a text file (textfile.txt), and compare that with computers.txt file.

jskfanAuthor Commented:
since I need to compare just one column, that might be easier.
dlmilleConnect With a Mentor Commented:
it really doesn't matter.  You could call the macro in the worksheet still from vbs.

want to try that, first?

or just work with .txt files?

let me know and i'll work on it

jskfanAuthor Commented:
the excel spreadsheet is somwhere in sharepoint site.

<<<or just work with .txt files?>>> I guess this more simplier. Please go ahead.
I appreciate it.

jskfanAuthor Commented:
I found this code , it worked for now

Const ForReading = 1, ForWriting = 2
Dim fso, txtFile, txtFile2, strLine1, strLine2, strMatch
Set fso = CreateObject("Scripting.FileSystemObject")
Set txtFile1 = fso.OpenTextFile("CurrentDIR.txt", ForReading)
Set f = fso.OpenTextFile("Files2Download.txt", ForWriting, True)

Do Until txtFile1.AtEndOfStream
strMatch = False
    strLine1 = txtFile1.Readline
Set txtFile2 = fso.OpenTextFile("LOG.txt", ForReading)
        Do Until txtFile2.AtEndOfStream
            strLine2 = txtFile2.Readline
                If Trim(UCase(strLine2)) = Trim(UCase(strLine1)) Then
                    strMatch = True
                End If
                If strMatch <> True then
                    f.writeline strLine1
                End If
Wscript.Echo "Done"

dlmilleConnect With a Mentor Commented:
Ok - this is called from the commandline (or you can make a script to do it from explorer).

File & Usage:  findMissing.VBS <primary.txt> <compare.txt>
The app flags an error if there aren't 2 arguments passed to the VBS, then aborts.  The app opens both files, creates and output file with results and a date/time stamp.  It reads in the computer names from the primary file into a dynamic array primaryComputers(), then reads the compare file, line by line, looping through the primaryArray to find a match.  If there is no match, the noMatchCtr followed by the missing computer is wrtten to the output file.

You get a message box upon completion, after files are all closed, with a reminder of the output file created.

Here's the code:
Dim primaryArray()
    Dim compareArray()
    Dim i
	Dim tmpVar
    If WScript.Arguments.Count <> 2 Then
        MsgBox "Please provide 2 inputs: 1 - fully pathed Primary file, 2 - Compare file.  The results file (this script.csv) will indicate which of the primary file's computers are missing from the compare file."
    End If
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Const intForReading = 1
    Const intoForWriting = 2
    strPrimaryFile = WScript.Arguments.Item(0)
    Set objPrimaryFile = objFSO.OpenTextFile(strPrimaryFile, intForReading, False)
    strCompareFile = WScript.Arguments.Item(1)
    Set objCompareFile = objFSO.OpenTextFile(strCompareFile, intForReading, False)
    strResultsFile = Left(WScript.ScriptFullName, Len(WScript.ScriptFullName) - 4) & "Results_" & TimeNow(Now) & ".csv"
    Set objResultsFile = objFSO.CreateTextFile(strResultsFile, intForWriting, True)
    objResultsFile.Writeline "Missing Computers: Primary File[" & strPrimaryFile & "] Compare File[" & strCompareFile & "]"

    While Not objPrimaryFile.AtEndOfStream
		tmpVar = Trim(objPrimaryFile.ReadLine)
		If tmpVar <> "" Then
			ReDim Preserve primaryArray(i)
			primaryArray(i) = tmpVar
			i = i + 1
		End If
	noMatchCtr = 0
	While Not objCompareFile.AtEndOfStream
		strCompare = Trim(objCompareFile.ReadLine)
		If strCompare <> "" Then
			foundMatch = False
			For i = 0 to UBound(primaryArray)
				If primaryArray(i) = strCompare Then 
					foundMatch = True
				End If
			If Not foundMatch Then
				noMatchCtr = noMatchCtr + 1
				objResultsFile.Writeline "[" & noMatchCtr & "]: " & strCompare
			End If
		End If

	MsgBox "Processing Complete.  Please see results in " & strResultsFile
Function TimeNow(dDateTime)
      TimeNow = Year(Now) & "-" & Right("00" & Month(Now), 2) & "-" & Right("00" & Day(Now), 2) & "-" & Right("00" & Hour(Now), 2) & "-" & Right("00" & Minute(Now), 2) & "-" & Right("00" & Second(Now), 2)
End Function

Open in new window

See attached VBS file.


dlmilleConnect With a Mentor Commented:
I hadn't seen your post.  The advantage of my code over your example, is you aren't opening and reopening the file over and over which causes lots of thrashing.

Hope you find mine useful as you can see, I put some thought behind it :)

dlmilleConnect With a Mentor Commented:
Did you try my post?  Do you require any additional enhancements?

jskfanAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.