asked on

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

Rory Archibald

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. :)

Brent387

ASKER

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.

Brent387

ASKER

That is interesting though, i just got what you were saying about the strComputer. Lemme give that a shot

Brent387

ASKER

It didn't work

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brent387

ASKER

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