Preparing Excel to run sysadmin scripts

Excel is a very powerful tool when it comes to gathering, organizing, processing and even presenting data. It can also help system administrators in a variety of ways. I’ve made a small attempt to show as to how can we use excel macros in utilizing various vbscripts. Although there are various ways of setting up your excel sheet to run macros, the steps below are procedures that I usually follow while creating my scripts. This can be used as a standard template for all your excel vba system admin scripts.

i. Enabling Macros / Developer Tools

You can follow the below screenshots (for excel 2013)

EnableDevTools

ii. Inserting a Execute button

ExecuteButton

Once you enable developer option just insert a button on the sheet to run the macros.(we still have to assign the macro). Rename the button to “execute” that’s my personal preference 🙂

iii. Create a Sub and assign the Macro to Execute button.

The code needs to be inserted in the “Microsoft Visual Basic for Applications” window which opens once you click on “view code” option under developers tab.

I’ve created a small Subroutine (the vb code piece) which would run once the button is clicked. This is where you can put all your WMI vb codes for execution.

The code has pingtest subroutine which takes the computer name input from Cell on row 5, column 2. It calls a small ping function and then throws the output on the cell (5, 3).

This is just a sample code to understand how macros works.

‘———————————————————-

Sub PingTest()

Dim strcomputer

strcomputer = Cells(5, 2)

If Ping(strcomputer) = True Then

strpingtest = “Online”

Else

strpingtest = “Offline”

End If

Cells(5, 3) = strpingtest

End Sub

Function Ping(strcomputer)

Dim objshell, boolcode

Set objshell = CreateObject(“wscript.shell”)

boolcode = objshell.Run(“ping -n 1 -w 1000 ” & strcomputer, 0, True)

If boolcode = 0 Then

Ping = True

Else

Ping = False

End If

End Function

‘———————————————————-

Assign the macro to the execute button.

As shown below right click the button and select the assign macro tab

AssignMacro

Once you assign the macro, make sure that you add the computer name/URL/IP address of the system that you want to ping.

Notes

– make sure that the excel sheet is saved in a macro compatible file format (in my case its .xlsm).

– enabling macros is a security risk so make sure you enable it in a controlled environment only.

Video below explains the steps in brief

8 thoughts on “Preparing Excel to run sysadmin scripts

  1. Pingback: Script to Ping multiple servers using excel | Wintel Geeks

  2. Pingback: Excel to list installed MS patches on a Computer | Wintel Geeks

  3. Pingback: Script to check uptime and free space on list of windows servers | Wintel Geeks

  4. Pingback: Script to check uptime and C Drive space on list of windows servers | Wintel Geeks

  5. Pingback: Script to check uptime and C Drive space on list of windows servers – Wintel Geeks

  6. Pingback: Excel to list installed MS patches – Wintel Geeks

  7. Pingback: Script to Ping multiple servers using excel - Wintel Geeks

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s