Solved

Excel  Search

Posted on 2011-09-07
14
274 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
14 Comments
 
LVL 42

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
1
 

Author Comment

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

Assisted Solution

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

Dave
0
Industry Leaders: 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!

 

Author Comment

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

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
 
LVL 42

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 42

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 42

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 42

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

707 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