Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

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.

Thanks
0
jskfan
Asked:
jskfan
  • 7
  • 7
7 Solutions
 
dlmilleCommented:
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
        Else
            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
    Wend
    
    Close #1 'tidy up, closing the file

End Sub

Open in new window


Enjoy!

Dave
missingComputers-r1.xlsm
computers.txt
1
 
jskfanAuthor Commented:
I am getting error: Expected end of statement
line:2
Char:13
0
 
dlmilleCommented:
Are you running this macro from the spreadsheet?

Dave
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Dave
0
 
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.

0
 
jskfanAuthor Commented:
since I need to compare just one column, that might be easier.
0
 
dlmilleCommented:
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

Dave
0
 
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.

0
 
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
                Else
                End If
        Loop
        txtFile2.Close
                If strMatch <> True then
                    f.writeline strLine1
                End If
Loop
f.Close
Wscript.Echo "Done"

0
 
dlmilleCommented:
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."
        WScript.Quit
    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 & "]"
    objResultsFile.Writeline

    While Not objPrimaryFile.AtEndOfStream
		tmpVar = Trim(objPrimaryFile.ReadLine)
		If tmpVar <> "" Then
			ReDim Preserve primaryArray(i)
			primaryArray(i) = tmpVar
			i = i + 1
		End If
    Wend
	
	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
			Next
		
			If Not foundMatch Then
				noMatchCtr = noMatchCtr + 1
				objResultsFile.Writeline "[" & noMatchCtr & "]: " & strCompare
			End If
		End If
	Wend
	
    objPrimaryFile.Close
    objCompareFile.Close
    objResultsFile.Close

	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.

Enjoy!

Dave  
missingComputersR1.vbs
computers.txt
compare.txt
0
 
dlmilleCommented:
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 :)

Dave
0
 
dlmilleCommented:
Did you try my post?  Do you require any additional enhancements?

Dave
0
 
jskfanAuthor Commented:
thanks
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now