Teaching an Excel spreadsheet to PING or RDP to a listed IP Address.

Here are some notes on configuring an Excel spreadsheet with the macros to PING or RDP a listed IP address.

 

  • With Office 2007, the file has to have the *.xlsm extension (aka macro enabled). Apparently a file with the regular *.xls or *.xlsx extensions won’t even try to run the macros.
  • Also, corporate users with restricted settings on their computer may need to click the security warning options (below the ribbon, above the header row) to enable macros each time the spreadsheet is opened.  Even if I could figure out how to sign the macro, many organizations have Windows security policies which would not allow permanently enabling the macros in a spreadsheet.
  • So far I haven’t found a good way to send username/password parameters thru the script.  The standard version of “mstsc.exe” doesn’t include command line support for username/password parameters.  I’ve found some 3rd party modules, but I’d prefer to find something a little more trustworthy (like an MS PowerTool) before I try scripting that functionality.

Did some more research on this.  Apparently Microsoft only provides the additional Remote Desktop command line options in two scenarios.

  • Their RDP client for Mac OS X has these options.
  • Their Windows 7 RDP client when connecting to Windows Server 2008r2.

If you want to view or edit the VBA code in Excel 2007, the steps are:

  • Open spreadsheet,
  • Click the funky windows icon in upper left corner,
  • Click the “Excel Options” button on bottom of that dialog box,
  • Within the “Excel Options” dialog box, select “Popular in the left side,
  • On the right hand side of “Excel Options | Popular”, check the box that says “Show Developer tab in the Ribbon”,
  • Click “OK”, return to the Ribbon, and select “Visual Basic”…  from there you get the VBA IDE interface like we had in previous versions of Office… they haven’t ribbonized it yet.

You’ll need to attached these VB functions to buttons displayed in the spreadsheet.  There are at least three ways to approach this.

  1. The option I consider the simplest user experience and easiest to maintain places buttons at the top of the spreadsheet and using the code below.  Select a cell, and then click the button.
  2. Option two may be a little more obvious, but may play havoc with your spreadsheet over the long term.  Place a button within every row of the spreadsheet.  If you sort, filter, or print your spreadsheet this may get ugly.  Additionally, if you more than a few dozen rows, the file size will get bloated.
  3. Add some additional code and make the appropriate cells “option clickable”.  With some create VB code, you can create a function which would listen for an event such as  “Control + F2 + Right Mouse Click”.  When the event occurs, the code could check which cell was clicked on and pass that value to the PING or RDP function described below.

Back when my work involved accessing routers and switches on a daily basis, I would implement option 3 in the VB code.  But don’t do these things on a daily basis any more, so it’s easier to place the button at the top of the spreadsheet and not worry about remembering additional keyboard shortcuts.

The two functions below are helpful for checking up on remote Windows boxes.  If you are working with other targets, edit the Shell() portion of the code to do things like Telnet, SSH, TFTP, etc.

The VB for the PING function is:

Sub buttonPingIP()
Dim stringIPaddress As String
Dim retval
stringIPaddress = ActiveCell.Value
retval = Shell(“c:\Windows\System32\ping.exe ” & stringIPaddress, 1)
End Sub

 

The VB for the RDP function is:

Sub buttonRDP()
Dim stringIPaddress As String
Dim retval
stringIPaddress = ActiveCell.Value
retval = Shell(“c:\Windows\System32\mstsc.exe /v:” & stringIPaddress, 1)
End Sub

If you are keeping your IP information in an Access Database, the same VB code should work if attached to a button on a form.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s