Using VBScript to write to Excel

[ttobaben]

I have written a VBScript that will allow me to locate computer in Active Directory and change the local administrator password on those computers. My script is successful in doing this and I have it putting the results in an Excel spreadsheet. One sheet is labeled Password Success and one is labeled Password Fail. The problem I am running into is with my code listing the successes and failures properly. If I have a success it will list it at the top of the spreadsheet as it should, if I have a failure it will list it on the proper spreadsheet, but the next row down. The next success is again listed on the proper spreadsheet, but it skips a row between the last successes as there was a failure in between. the way my code is written, it advances to the next row to write the information as it should, but it should do this on EACH SHEET not as a combined effort. I am posting the code, if anyone could help me resolve this it would be much appreciated.

(On a side note, I am looking to advance this code a little and have it look to see if the spreadsheet it creates exists and if it does read from the Password Fail sheet to try those machines again and move them to the Password Success side. If the spreadsheet does not exist it will then execute the code I have written. If someone could point me in the direction on how to accomplish this as well, that would be great!)

On Error Resume Next
Const xlExcel7 = 39
strExcelPath = "c:\PasswordChange.xls"

Set objExcel = CreateObject("Excel.Application")



objExcel.Workbooks.Add

Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objSheet.Name = "Password Success" 

Set objSheet2 = objExcel.ActiveWorkbook.Worksheets(2)
objSheet2.Name = "Password Fail"

objSheet.Cells(1, 1).Value = "Computer Name"
objSheet.Cells(1, 2).Value = "Status"
objSheet.Cells(1, 3).Value = "Date"
objSheet2.Cells(1, 1).Value = "Computer Name"
objSheet2.Cells(1, 2).Value = "Status"
objSheet2.Cells(1, 3).Value = "Date"

x=2

Set objOU = GetObject("LDAP://ou=, ou=, ou=, dc=, dc=, dc=, dc=") 
objOU.Filter = Array("Computer")
 
For Each objComputer in objOU 
    strComputer = objComputer.CN 
 
    Set objShell = CreateObject("WScript.Shell") 
    strCommand = "%comspec% /c ping -n 3 -w 1000 " & strComputer & "" 
    Set objExecObject = objShell.Exec(strCommand) 
 
    Do While Not objExecObject.StdOut.AtEndOfStream 
        strText = objExecObject.StdOut.ReadAll() 
        If Instr(strText, "Reply") > 0 Then 

    Set objUser = GetObject("WinNT://" & strComputer & "/Administrator")
    objUser.SetPassword "Password"
    Wscript.Echo strComputer & " passowrd changed."
    objSheet.Cells(x, 1).Value = strComputer
	objSheet.Cells(x, 2).Value = "Password Changed"
	objSheet.Cells(x, 3).Value = Now()
  
	
	     Else 

    Wscript.Echo strComputer & " could not be reached."
    objSheet2.Cells(x, 1).Value = strComputer
	objSheet2.Cells(x, 2).Value = "Computer couldn't be reached, Password was NOT changed"
	objSheet2.Cells(x, 3).Value = Now()

 
End If
    x = x + 1
Loop
Set objRange = objExcel.Range("A1")
objRange.Activate

Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

Set objRange = objExcel.Range("B1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()

Set objRange = objExcel.Range("C1")
objRange.Activate

Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()


Set objRange = objExcel.Range("A1").SpecialCells(11)
Set objRange2 = objExcel.Range("C1")
Set objRange3 = objExcel.Range("A1")

	  
Next

	   ' Save the spreadsheet and close the workbook.
	   ' Specify Excel7 File Format.
	   objExcel.ActiveWorkbook.SaveAs strExcelPath, xlExcel7
	   objExcel.ActiveWorkbook.Close

	   ' Quit Excel.
	   objExcel.Application.Quit

 

[Geates]

You answered your own question in your post.

How do you write to different sheets? By having different objects, objSheet and objSheet2. It would seem quite apparent that you should use a different counters for different sheets, x and x2.

Do While Not objExecObject.StdOut.AtEndOfStream
	strText = objExecObject.StdOut.ReadAll()
	If Instr(strText, "Reply") > 0 Then
		Set objUser = GetObject("WinNT://" & strComputer & "/Administrator")
		objUser.SetPassword "Password"
		Wscript.Echo strComputer & " passowrd changed."
		objSheet.Cells(x, 1).Value = strComputer
		objSheet.Cells(x, 2).Value = "Password Changed"
		objSheet.Cells(x, 3).Value = Now()
		[red]x = x + 1[/red]
	Else
		Wscript.Echo strComputer & " could not be reached."
		objSheet2.Cells([red]x2[/red], 1).Value = strComputer
		objSheet2.Cells([red]x2[/red], 2).Value = "Computer couldn't be reached, Password was NOT changed"
		objSheet2.Cells([red]x2[/red], 3).Value = Now()
		[red]x2 = x2 + 1[/red]
	End If
Loop

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

 

[ttobaben]

You are absolutely right. Thanks, sometimes it takes someone else to point out the obvious!

 

[SkipVought]

I would question why you are writing this data to 2 separate sheets? Why can't ALL the data be in one sheet in one table? Makes the maintenance simpler and makes the analysis and reporting of data simpler as well!

Why complicate things???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ttobaben]

Skip, my initial thought was to make it easier for everyone to readily identify which machines did not have their passwords changed. However if I advance my script to go back through to retry the machines that had not been done then there is no reason that it couldn't be done on one sheet/table. You're right, I need to reevaluate the script and apply the KISS principle!