Solved

Would like to add some options to VB script

Posted on 2004-04-01
12
541 Views
Last Modified: 2010-04-11
Need scriot to  - record a successfull ping so I know that computer name was changed.


Dim objExcel, strPathExcel, objFile

Set objExcel = CreateObject("Excel.Application")
strPathExcel = "\\DS\ADLookup\ADLookupR.xls"
objExcel.Workbooks.open strPathExcel
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
Set SH = CreateObject("WScript.Shell")

row = 1

newName = ""

Do While Trim(objSheet.cells(row, 1).Value) <> ""
   CName = objSheet.cells(row, 1)
   objSheet.cells(row, 6) = "CHANGED"
   objSheet.cells(row, 7) = Trim(CStr(Now))
   newName = Trim(objSheet.cells(row, 5).Value)
   
   SH.Run "RENCOMP " + CName + " " + newName, 0, true

   a = MsgBox("Changed " + CName + " to " + newName + "!", vbInformation)
 
   row = row + 1
Loop

objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close
0
Comment
Question by:Roebuck1967
[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
  • 6
  • 6
12 Comments
 
LVL 11

Expert Comment

by:YohanShminge
ID: 10732500
OK, I'm not sure if this is what you want, but it runs that script, then after its all done, it loops through the spreadsheet once again in order to send pings to the new names.  If ping does NOT find the host, it writes to "c:\logfile.txt" that the host could not be found.  If it does find the host, it writes a success message to "c:\logfile.txt".  After the script is finished executing completely, you will see a messagebox telling you so.  If it doesnt work right, I apologize, I didnt have time to test it thoroughly.

-------------------------------------------------------------

Dim objExcel, strPathExcel, objFile

Set objExcel = CreateObject("Excel.Application")
strPathExcel = "\\DS\ADLookup\ADLookupR.xls"
objExcel.Workbooks.open strPathExcel
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
Set SH = CreateObject("WScript.Shell")

row = 1

newName = ""

Do While Trim(objSheet.cells(row, 1).Value) <> ""
   CName = objSheet.cells(row, 1)
   newName = Trim(objSheet.cells(row, 5).Value)
   
   SH.Run "RENCOMP " + CName + " " + newName, 1, true

   a = MsgBox("Ran RENCOMP for " + CName + " to change name to " + newName + "!", vbInformation)
 
   row = row + 1
Loop

Dim fso, ts, fso2, ts2

Const ForWriting = 8
Const ForReading = 1
Set fso = CreateObject("Scripting.FileSystemObject")
Set fso2 = CreateObject("Scripting.FileSystemObject")

'Temporary file path
Set ts = fso.OpenTextFile("c:\temp.tmp", ForReading, True)

'Log file path *******
Set ts2 = fso2.OpenTextFile("c:\logfile.txt", ForWriting, True)

row = 1

Do While Trim(objSheet.cells(row, 1).Value) <> ""
   newName = Trim(objSheet.cells(row, 5).Value)
   SH.Run "cmd /c ping " + newName + " >c:\temp.tmp", 0, True  
   a = ts.ReadLine()  

   If InStr(a, "Ping request could not find") > 0 Then
      ts2.WriteLine ("Computer " + newName + " was NOT be changed! " + CStr(Now))
   Else
      ts2.WriteLine ("Computer " + newName + " was successfully changed! " + CStr(Now))
   End If
   row = row + 1
Loop

ts2.Close
ts.Close

objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close

MsgBox("Script is complete, check logfile.txt for information!")
0
 
LVL 11

Expert Comment

by:YohanShminge
ID: 10733192
Perhaps you would like this better.  It still logs out to the text file but it also updates columns F and G in the spreadsheet:

-------------------------------------------------------------

Dim objExcel, strPathExcel, objFile

Set objExcel = CreateObject("Excel.Application")
strPathExcel = "\\DS\ADLookup\ADLookupR.xls"
objExcel.Workbooks.open strPathExcel
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
Set SH = CreateObject("WScript.Shell")

row = 1

newName = ""

Do While Trim(objSheet.cells(row, 1).Value) <> ""
   CName = objSheet.cells(row, 1)
   newName = Trim(objSheet.cells(row, 5).Value)
   
   SH.Run "RENCOMP " + CName + " " + newName, 1, true

   a = MsgBox("Ran RENCOMP for " + CName + " to change name to " + newName + "!", vbInformation)
 
   row = row + 1
Loop

Dim fso, ts, fso2, ts2

Const ForWriting = 8
Const ForReading = 1
Set fso = CreateObject("Scripting.FileSystemObject")
Set fso2 = CreateObject("Scripting.FileSystemObject")

'Temporary file path
Set ts = fso.OpenTextFile("c:\temp.tmp", ForReading, True)

'Log file path *******
Set ts2 = fso2.OpenTextFile("c:\logfile.txt", ForWriting, True)

row = 1

Do While Trim(objSheet.cells(row, 1).Value) <> ""
   newName = Trim(objSheet.cells(row, 5).Value)
   SH.Run "cmd /c ping " + newName + " >c:\temp.tmp", 0, True  
   a = ts.ReadLine()  

   If InStr(a, "Ping request could not find") > 0 Then
      ts2.WriteLine ("Computer " + newName + " was NOT be changed! " + CStr(Now))
      objSheet.cells(row, 6) = ""
      objSheet.cells(row, 7) = ""
   Else
      ts2.WriteLine ("Computer " + newName + " was successfully changed! " + CStr(Now))
      objSheet.cells(row, 6) = "CHANGED"
      objSheet.cells(row, 7) = Trim(CStr(Now))
   End If
   row = row + 1
Loop

ts2.Close
ts.Close

objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close

MsgBox("Script is complete, check logfile.txt for information!")
0
 

Author Comment

by:Roebuck1967
ID: 10733357
I will give it a shot
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:Roebuck1967
ID: 10735157
How did you get the script to work without rebooting the computer?
0
 
LVL 11

Accepted Solution

by:
YohanShminge earned 500 total points
ID: 10735264
That wasn't anything I did, it just happened I guess... Perhaps that computer had already been renamed?

Anyway, I just finished perfecting a script which I believe to be the best solution.  It consolidates the code a bit, and gets its input directly from RENCOMP, which returns "Failed to copy..." if it cant access the remote computer.  It does this by looping just once through the spreadsheet, and it logs only to the F and G columns in the spreadsheet.

-------------------------------------------

Dim objExcel, strPathExcel, objFile
Dim fso, ts, fso2, ts2
Const ForWriting = 8
Const ForReading = 1
Set fso = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")
strPathExcel = "\\DS\ADLookup\ADLookupR.xls"
objExcel.Workbooks.open strPathExcel
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
Set SH = CreateObject("WScript.Shell")

row = 1

newName = ""

Do While Trim(objSheet.cells(row, 1).Value) <> ""
   CName = objSheet.cells(row, 1)
   newName = Trim(objSheet.cells(row, 5).Value)
   
   If objSheet.cells(row, 6) <> "CHANGED" Then

      SH.Run "cmd /c RENCOMP " + CName + " " + newName + " >c:\temp.tmp", 1, True

      Set ts = fso.OpenTextFile("c:\temp.tmp", ForReading, True)
      a = ts.ReadLine()
      ts.Close

      If InStr(a, "Failed") > 0 Then
         MsgBox ("Failed to rename " + CName + " to " + newName + "!")
         objSheet.cells(row, 6) = ""
         objSheet.cells(row, 7) = ""
      Else
         MsgBox ("Successfully renamed " + CName + " to " + newName + "!")
         objSheet.cells(row, 6) = "CHANGED"
         objSheet.cells(row, 7) = Trim(CStr(Now))
      End If
   End If

   row = row + 1
Loop

objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close
MsgBox ("Script complete.")
0
 

Author Comment

by:Roebuck1967
ID: 10735374
I will give it a shot.....You are the man!
0
 

Author Comment

by:Roebuck1967
ID: 10740632
Script works great..only thing now it is rebooting the users computer where as before is was not.  Any idea?
0
 
LVL 11

Expert Comment

by:YohanShminge
ID: 10742354
As I said, I did nothing different that would allow a rename without a restart... Perhaps the computer was already renamed?  As stated, RENCOMP restarts the computer, unless that computer has been renamed already.
0
 

Author Comment

by:Roebuck1967
ID: 10742395
Perhaps RENCOMP was not running in the first script

(First script above) SH.Run "RENCOMP " + CName + " " + newName, 1, true
(Latest script)          SH.Run "cmd /c RENCOMP " + CName + " " + newName + " >c:\temp.tmp", 1, True
0
 
LVL 11

Expert Comment

by:YohanShminge
ID: 10742482
It should have run fine in the first script, but regardless, I think the only way it will work is with a restart.  FYI - the second run command does the same thing, but it runs RENCOMP through CMD, which allows the output from RENCOMP to be directed to c:\temp.tmp so that the script can then read it and determine if the rename worked.
0
 

Author Comment

by:Roebuck1967
ID: 10742903
You get the Points YohanShminge  - - - Just posted a new Question regarding screen saver.
0
 
LVL 11

Expert Comment

by:YohanShminge
ID: 10742953
I'll get right on that one...as soon as I get back to my computer!  Thanks for the points!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

#Citrix #Citrix Netscaler #HTTP Compression #Load Balance
During and after that shift to cloud, one area that still poses a struggle for many organizations is what to do with their department file shares.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Suggested Courses
Course of the Month10 days, 9 hours left to enroll

628 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