Solved

Would like to add some options to VB script

Posted on 2004-04-01
12
533 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
  • 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
 

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Short answer to this question: there is no effective WiFi manager in iOS devices as seen in Windows WiFi or Macbook OSx WiFi management, but this article will try and provide some amicable solutions to better suite your needs.
PRTG Network Monitor lets you monitor your bandwidth usage, so you know who is using up your bandwidth, and what they're using it for.
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now