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
Solved

Would like to add some options to VB script

Posted on 2004-04-01
12
537 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
Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

 

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

Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

Question has a verified solution.

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

Suggested Solutions

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.
Don’t let your business fall victim to the coming apocalypse – use our Survival Guide for the Fax Apocalypse to identify the risks and signs of zombie fax activities at your business.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
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…

790 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