Solved

Excel  Search

Posted on 2011-09-07
14
267 Views
Last Modified: 2012-06-21
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
Comment
Question by:jskfan
  • 7
  • 7
14 Comments
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 36501037
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
0
 

Author Comment

by:jskfan
ID: 36501089
I am getting error: Expected end of statement
line:2
Char:13
0
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 500 total points
ID: 36501135
Are you running this macro from the spreadsheet?

Dave
0
 

Author Comment

by:jskfan
ID: 36501321
No, I thought it was VBS code
0
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 500 total points
ID: 36501324
No.  Not sure I interpreted "I need some type of script" correctly.  Must this be run from VBS?

Dave
0
 

Author Comment

by:jskfan
ID: 36501328
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
 

Author Comment

by:jskfan
ID: 36501331
since I need to compare just one column, that might be easier.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 500 total points
ID: 36501338
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
 

Author Comment

by:jskfan
ID: 36501346
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
 

Author Comment

by:jskfan
ID: 36501672
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
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 500 total points
ID: 36501704
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
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 500 total points
ID: 36501708
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
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 500 total points
ID: 36508183
Did you try my post?  Do you require any additional enhancements?

Dave
0
 

Author Closing Comment

by:jskfan
ID: 36528140
thanks
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now