Find text in Excel so vbscript only runs once

I want this script to create a log of who has certain software and on what computer. However, I don't want multiple copies of the same data in the spreadsheet. I want it to search the spreadsheet for the computer name and if it already exists, quit. This is what I have and everything works except that it always runs so I have tons of the same data. Any advice?

Set WshShell = CreateObject("WScript.Shell")
strProgramFiles = WshShell.ExpandEnvironmentStrings("%PROGRAMFILES%")
' Bind to Excel object.
On Error Resume Next
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = FALSE
objExcel.Silent = TRUE

Set objShell = CreateObject("WScript.Shell")
strUserName = objShell.ExpandEnvironmentStrings("%username%")

set objComp = WScript.CreateObject ("WScript.Shell")
strComputer = objComp.ExpandEnvironmentStrings("%Computername%")

If objExcel.Cells.Find("%Computername%") = 0 Then
Set ObjPath = CreateObject("scripting.FileSystemObject")
If objPath.FileExists(strProgramFiles & "\Software.exe") Then
' Create a new workbook.
set objWorkBook = objExcel.Workbooks.open("\\path\excel.xls")
strText = objWorkbook.Readall
' Bind to worksheet.
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objSheet.Name = "Software Logs"      
' Populate spreadsheet cells with user attributes.
objSheet.Cells(1, 1).Value = "User Name"
objSheet.Cells(1, 2).Value = "Computer Name"
' Enumerate users and add user names to spreadsheet.
For Each objUser In obShell
    objSheet.range("A" & objsheet.rows.count).end(-4162).offset(1,1).Value = strComputer
    objSheet.range("A" & objsheet.rows.count).end(-4162).offset(1,0).Value = strUserName
Next

' Format the spreadsheet.
objSheet.Range("A1:C1").Font.Bold = True
objSheet.Select
objSheet.Range("B5").Select
objExcel.Columns(1).ColumnWidth = 20
objExcel.Columns(2).ColumnWidth = 30
objExcel.Columns(3).ColumnWidth = 20

' Save the spreadsheet and close the workbook.
objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.Save
objExcel.DisplayAlerts = True

' Quit Excel.
objExcel.Application.Quit

' Clean Up
Set objUser = Nothing
Set objSheet = Nothing
Set objExcel = Nothing
Else
WScript.quit
End if
Else
WScript.quit
End if
LVL 1
Brent387Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rory ArchibaldCommented:
There is no Application.Silent property so I dont' know what that line is supposed to achieve. You also appear to be searching for the computername before you open the workbook, and you are looking for the actual string "%COMPUTERNAME%" rather than using the strComputerName variable, which is why you'll never find it. :)
0
Brent387Author Commented:
The Application.Silent actually makes it so that when I run the script, Excel doesn't open, write the text, and close. I'd never heard of it, but it works. I never got around to putting my script back together the way it was so it's a little out of order. What I am looking for is something that will search the excel script, like a conditional statement that says if the text is there, then quit.
0
Brent387Author Commented:
That is interesting though, i just got what you were saying about the strComputer. Lemme give that a shot
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Brent387Author Commented:
It didn't work
0
Rory ArchibaldCommented:
There's no Silent property. I think you'll find if you remove the On Error Resume Next line, you will get an error on the Silent line.
Your code would be something like this, though I am confused by your For Each objUser loop as I can't see what you are trying to loop through.

Set WshShell = CreateObject("WScript.Shell")
strProgramFiles = WshShell.ExpandEnvironmentStrings("%PROGRAMFILES%")
' Bind to Excel object.
On Error Resume Next
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = FALSE

Set objShell = CreateObject("WScript.Shell")
strUserName = objShell.ExpandEnvironmentStrings("%username%")

set objComp = WScript.CreateObject ("WScript.Shell")
strComputer = objComp.ExpandEnvironmentStrings("%Computername%")

' Open the logging workbook.
set objWorkBook = objExcel.Workbooks.open("\\path\excel.xls")
'strText = objWorkbook.Readall
' Bind to worksheet.
Set objSheet = objWorkbook.Worksheets(1)
objSheet.Name = "Software Logs"      
' check name does not already exist
If objSheet.Usedrange.Find(strComputername) Is Nothing Then
   Set ObjPath = CreateObject("scripting.FileSystemObject")
   If objPath.FileExists(strProgramFiles & "\Software.exe") Then

' Populate spreadsheet cells with user attributes.
objSheet.Cells(1, 1).Value = "User Name"
objSheet.Cells(1, 2).Value = "Computer Name"
' Enumerate users and add user names to spreadsheet.
For Each objUser In objShell 
    objSheet.range("A" & objsheet.rows.count).end(-4162).offset(1,1).Value = strComputer 
    objSheet.range("A" & objsheet.rows.count).end(-4162).offset(1,0).Value = strUserName 
Next

' Format the spreadsheet.
objSheet.Range("A1:C1").Font.Bold = True

objSheet.Columns(1).ColumnWidth = 20
objSheet.Columns(2).ColumnWidth = 30
objSheet.Columns(3).ColumnWidth = 20

' Save the spreadsheet and close the workbook.
objExcel.DisplayAlerts = False 
objWorkbook.Save 
objExcel.DisplayAlerts = True
End If
objWorkbook.Close False
' Quit Excel.
objExcel.Quit

' Clean Up
Set objUser = Nothing
Set objSheet = Nothing
Set objExcel = Nothing

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brent387Author Commented:
Whenever a user logs on who has the software I want it to log it in excel. From what I understand I need For Each objUser for that to work. Also, I tried to run your script and now I can't get into the original spreadsheet because it's saying that it's open.

If objSheet.Usedrange.Find(strComputername) Is Nothing Then
   Set ObjPath = CreateObject("scripting.FileSystemObject")
   If objPath.FileExists(strProgramFiles & "\Software.exe") Then

That is what I was looking for. Thanks for your advice
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.