0

I was wondering if it was possible to create a vbs file to highlight a cell in an excel workbook.

I have an excel worksheet with multiple computer host-names, I also run a script in batch that pings each host-name in a text document. I want to call the vbs file to highlight the cell in excel if the ping result was successful. Is this possible?

Thanks!

Dimitri

Community
  • 1
  • 1
Dimitri H
  • 36
  • 1
  • 12
  • YES, anything is possible (almost!). – Pankaj Jaju Dec 27 '13 at 20:01
  • I am not just pinging the computers with the batch, I am also patching the computers with the batch if it replies to the ping request. With that being said, I guess to do it your way Bill, I would have to change my batch file and pipe the computer name to the batch to execute the patching process. I really just wanted a way to highlight completed ones by executing a vb script. Hope this answers some questions. – Dimitri H Dec 27 '13 at 21:07
  • Ok, well using either of the methods I've provided you can do the patching from within Excel, as well. That said, if you want to continue to use your batch file, then you can still use my code, you'd just have to migrate it into the VBScript and create an Excel object from within the script to utilize it. Then you can call the VBS in the batch using any of the methods found here: http://stackoverflow.com/questions/11899730/running-vbscript-from-batch-file – Bill N. Dec 27 '13 at 21:49
  • Sorry, I need more clarification as to the specific part of the problem that you're having issue with, then. Is it calling the VBS from the batch? Is it opening Excel and modifying the worksheet in VBScript? Is it the code to highlight a cell? All I'm saying in my answer is that you'd find it more efficient to ping and patch all of the hostnames, store the results of that process, then go back and highlight the worksheet based on those stored results rather than trying to pipe whatever hostname you're working on at any point in the batch to a VBS script and update that cell only. – Bill N. Dec 27 '13 at 21:55
  • All I have is the batch file, never worked with VB in excel before. The reason I want to have the batch call the excel file and highlight the computer name column on success is because we have hundreds of computer names, and if the script runs, I end up with hundreds of successful patches. Now that's all fine and dandy, but going back by hand and highlighting the successful comps is a hand sore. The other reason I don't think excel executing the script would work is because the script needs to be executed as an administrator, not a user, so could cause an issue. – Dimitri H Dec 27 '13 at 22:07
  • I feel like we're not on the same page here. Effectively, I'm just saying that whatever you're doing in your batch file can be done directly in Excel using APIs provided by Windows. Literally anything that you want to do in a batch file can be exposed in VBA. It makes more sense to do it this way because you're clearly maintaining your list of host names in the spreadsheet and want it to be that way. Even running with administrator privileged. So why do any bit of the process externally when it doesn't need to be? Anyway, hopefully what I provided you gives you some ideas. I hope it's enough. – Bill N. Dec 27 '13 at 22:43

1 Answers1

1

There are plenty of ways to do this, but I must ask why you're using a batch script and a text file to ping the hostnames when you can do that right in Excel?

There are two ways to do this... one is a bit more complex and correct and the other is quick and dirty. Frankly, I recommend the quick and dirty.

Correct Way: Declare the ReadConsole & WriteConsole methods from your Windows kernel32.dll and utilize them to get the results of your ping. It's described well here:

http://visualbasic.about.com/od/learnvb6/l/bldykvb6dosa.htm

Q&D Way: Use the built-in Shell() function in VBA and pipe the output of the ping to a text file. Parse said text file and delete it when you're done.

e.g.

for each currCell in hostnameRange
   ' Ping each hostname and pipe the results to a file
   shell "ping " + currCell.value + " >> ping_result.txt"
next currCell

inFile = FreeFile()
Open "ping_result.txt" for Input as #inFile

fileBuffer = Input$(LOF(inFile ), inFile) ' Open and read the file to a buffer

for each currCell in hostnameRange
   ' Search for ping failures in the buffer
   if instr(1, fileBuffer, "could not find host " + currCell.value) = 0 then
        debug.print "Ping successful."
   end if
next currCell
Bill N.
  • 218
  • 1
  • 7
  • Bill ... how is this an answer and not a comment? – Pankaj Jaju Dec 27 '13 at 20:30
  • 1
    How is it a comment? It gives two possible solutions to the problem? What do you expect out of an answer? The full work done for the person? I guess it's not quite as motivating as your comments appear to be. – Bill N. Dec 27 '13 at 20:37
  • You provided the possible solutions based on assumptions, which are not substantiated. Secondly, you just provided the link of someone else's work ... how is that answer again? – Pankaj Jaju Dec 27 '13 at 20:43
  • The only assumption is that they're doing more work than they need to by pinging the hostnames through a batch file instead of doing it directly in the spreadsheet. I felt it was a safe assumption to answer on and it's still usable whether they want to call a VBS file to do it or not. Additionally, by someone else's work, do you mean the programmer who wrote the API in kernel32.dll? ...because that's all the link I provided explains. I also provided an alternative solution, which, by the way, I suggested they use over the externally referenced methods. – Bill N. Dec 27 '13 at 20:48
  • Have a look at [this](http://meta.stackexchange.com/questions/118582/what-is-an-acceptable-answer) and lets agree to disagree. – Pankaj Jaju Dec 27 '13 at 20:51
  • I'll be sure to review that more thoroughly when my answer is deleted or converted to a comment. If you're looking at my answer and all you see if me copying someone else's code and taking credit for it then you have a very odd perception of things. Do yourself a favor and don't try to be a self-appointed moderator of StackOverflow a few months after you've joined. If the mods have an issue with the answer, they clearly have procedures in place to deal with it and will do so at their own accord. – Bill N. Dec 27 '13 at 20:56