I am trying to create, one, a macro, that when data is entered into a cell range, it will trigger a PowerShell script. Within this PowerShell script, I am trying to get the objects from my PowerShell query to return back to the active worksheet, where the macro was triggered. The only hard part is, the Workbook is on a SharePoint server.
Does anyone have any insight of how to accomplish my goal, or any links to help point me in the right direction.
Thank you.
You will need a change event so you can handle checking when the data in the cell has changed.
Secondly you need a method returning data from a powershell command. I simply use the >> operator from a command line operation to put the data into a text file and then read the text file later.
Thirdly you need to do something with that data.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then ' If A1 changes...
Dim FileNum As Integer
Dim FileName As String
Dim DataLine As String
Dim objShell
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("WScript.Shell")
FileName = "C:\data.txt"
cmdString = "Powershell DIR >>" + FileName ' Cmd String for Powershell
Call objShell.Run(cmdString, 0, True) ' Run the command, data is exported to FileName path
FileNum = FreeFile()
Open FileName For Input As #FileNum ' Open the File we generated
While Not EOF(FileNum)
Line Input #FileNum, DataLine ' read in data 1 line at a time
' Do something with data line that was saved from the shell script.
Wend
Close #FileNum
Call fso.DeleteFile(FileName) ' Delete the file we generated
End If
End Sub
