VBA Excel Project - Wake on Lan

[Silas25]

I have been searching the Web/Forums for about an hour without finding anything close to what I'm wanting to do.

I currently have an excel Spreadsheet (VBA Macro) that will Ping a list of computers names and respond back if they respond to the ping or fail. What I would like to do is after I run the ping test in excel I would like to execute another VBA Macro that will then try to wake up the machines (WOL) that in Column b List Failed to Respond.

Provided is the excel ping

Option Explicit

Dim ws2 As Worksheet
Dim i As Long
Sub Pinger()
Dim X As Object
Dim rowsa As Long

Set ws2 = Sheets("Ping Win") 'this sheetc ontains all PC names
rowsa = ws2.Cells(Rows.Count, 1).End(xlUp).Row
Set ws2 = ActiveSheet

For i = 1 To rowsa 'starts on Row1 of column A
If ws2.Cells(i, 1) <> "" Then
Call Pinger2(ws2.Cells(i, 1))
End If
Next i

End Sub

Sub Pinger2(ByVal strIP As String)
' Ping the target and return the round trip time in milliseconds
' or a negative value indicating a failure.
Dim PingResult As Object
Dim PingStatus As Object

Set PingResult = GetObject("WinMgmts:{impersonationLevel=impersonate}").ExecQuery("SELECT * FROM Win32_PingStatus WHERE Address = '" & strIP & "' ")
For Each PingStatus In PingResult
If Not IsNull(PingStatus.StatusCode) Then
If PingStatus.StatusCode = 0 Then
ws2.Cells(i, 2) = "Response time: " & PingStatus.Properties_("ResponseTime").Value
Exit For
Else
ws2.Cells(i, 2) = "Failed to respond"
End If
End If
Next

End Sub

 

[Silas25]

I have found what I believe is a work around, but I'm not sure how to implement it. Baically I have a worksheet with all the mac addresses of the machiens I want to wake and will add a macro VBA that will go down the list and call a powershell that will pass the mac address to the powershell that will in turn wake the machine. I assume I will need to use a shell() but have no idea how to set this up




Sub Macro1()
'
Dim retval

retval = Shell("powershell ""C:\Users\account\Desktop\send-wol.ps1""", 1)
End Sub