[Webinar] Streamline your web hosting managementRegister Today

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

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
0
Brent387
Asked:
Brent387
  • 4
  • 2
1 Solution
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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