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)

ii. Inserting a Execute button

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

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