Solved

Would like to add some options to VB script

Posted on 2004-04-01
12
534 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
Give your grad a cloud of their own!

With up to 8TB of storage, give your favorite graduate their own personal cloud to centralize all their photos, videos and music in one safe place. They can save, sync and share all their stuff, and automatic photo backup helps free up space on their smartphone and tablet.

 

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

Network it in WD Red

There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Tracking Down IP in VMware 41 96
Why APC patch cords are not recommended wirth 10GBase SFP+ Gbics? 6 69
Allowing Multicast in the firewall 2 42
DNS with WiFi Network 5 55
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.
In this article, I am going to show you how to simulate a multi-site Lab environment on a single Hyper-V host. I use this method successfully in my own lab to simulate three fully routed global AD Sites on a Windows 10 Hyper-V host.
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…
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…

914 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

17 Experts available now in Live!

Get 1:1 Help Now