VBA和PowerShell集成？ [英] VBA and PowerShell integration?

问题描述

我正在尝试创建一个宏，当数据输入单元格范围时，它将触发一个PowerShell脚本。在这个PowerShell脚本中，我试图从PowerShell查询中获取对象，返回到活动工作表，宏被触发。唯一困难的是，Workbook位于SharePoint服务器上。有没有人有任何见解如何完成我的目标，或任何链接，以帮助我指向正确的方向。

谢谢。

解决方案

您将需要一个更改事件，以便您可以处理检查单元格中的数据是否改变。

其次，您需要一种从powershell命令返回数据的方法。我只需使用命令行操作的>>操作符将数据放入一个文本文件中，然后再读取文本文件。

第三，你需要做一些事情该数据。

  Private Sub Worksheet_Change（ByVal Target As Range）
如果Target.Address =$ A $ 1然后'如果A1更改... 
 Dim FileNum As Integer 
 Dim FileName As String 
 Dim DataLine As String 
 Dim objShell 
 Dim fso 
 Set fso = CreateObject（Scripting.FileSystemObject）
设置objShell = CreateObject（WScript.Shell）
 FileName =C：\data.txt
 cmdString =Powershell DIR> >中+ FileName'Powershell的Cmd字符串
调用objShell.Run（cmdString，0，True）'运行命令，数据导出到FileName路径
 
 FileNum = FreeFile（）
打开FileName输入为#FileNum'打开文件我们生成
虽然不是EOF（FileNum）
行输入#FileNum，DataLine'一次读入数据1行
'做某事从shell脚本保存的数据行。 
 Wend 
关闭#FileNum 
调用fso.DeleteFile（FileName）'删除我们生成的文件
 End If 
 
 End Sub 
  

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

这篇关于VBA和PowerShell集成？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！