外壳方法
几乎所有与命令行交互的内容都可以使用 Shell 进行访问。
下面是一个简单的示例,它连接到正在运行的 MongoDB 实例并将查询打印到即时窗口。您需要添加对Windows Script Host Object Model
.
Private Sub Test()
Dim wsh As New WshShell
Dim proc As WshExec
Dim line As String
Set proc = wsh.Exec("mongo")
With proc
.StdIn.WriteLine "use test"
.StdIn.WriteLine "db.restaurants.find({""address.zipcode"":""10075""})"
.StdIn.WriteLine "quit()"
Do While .Status = WshRunning
line = .StdOut.ReadLine
If line = "Type ""it"" for more" Then
.StdIn.WriteLine "it"
ElseIf line Like "{*" Then
Debug.Print line
End If
DoEvents
Loop
End With
End Sub
然而,仅打印原始 JSON 字符串并不是很令人兴奋或有用。您可以编写自己的 JSON 解析器,但在本例中,我们将使用 Tim Hall 的 VBA-JSON(您可以在 GitHub 上找到它 https://github.com/VBA-tools/VBA-JSON).
在撰写本文时,使用 VBA-JSON 解析从 MongoDB 返回的字符串时必须解决一个问题。任何包含括号的值,例如"_id": ObjectId("...")
,会抛出错误。对此的一个快速而肮脏的解决方法是使用 RegEx 来清理解析器的字符串。您将需要参考Microsoft VBScript Regular Expressions 5.5
库用于以下功能的工作。
Private Function CleanString(str As String) As String
Dim temp As String
Dim rx As New RegExp
With rx
.IgnoreCase = True
.Global = True
.Pattern = "[a-z]*\(" ' Left
temp = .Replace(str, "")
.Pattern = "\)" ' Right
temp = .Replace(temp, "")
End With
CleanString = temp
End Function
然后我们可以解析从 MongoDB 返回的 JSON 并将每个对象添加到Collection
。访问值变得非常简单。
Private Sub Mongo()
Dim wsh As New WshShell
Dim proc As WshExec
Dim line As String
Dim response As New Collection
Dim json As Object
Set proc = wsh.Exec("mongo")
With proc
.StdIn.WriteLine "use test"
.StdIn.WriteLine "db.restaurants.find({""address.zipcode"":""10075""})"
.StdIn.WriteLine "quit()"
Do While .Status = WshRunning
line = .StdOut.ReadLine
If line = "Type ""it"" for more" Then
.StdIn.WriteLine "it"
ElseIf line Like "{*" Then
response.Add ParseJson(CleanString(line))
End If
DoEvents
Loop
End With
For Each json In response
Debug.Print json("name"), json("address")("street")
Next
End Sub
...这将从 MongoDB 中产生以下输出示例数据集 https://docs.mongodb.org/getting-started/shell/import-data/.
Nectar Coffee Shop Madison Avenue
Viand Cafe Madison Avenue
Don Filippo Restaurant Lexington Avenue
Lusardi'S Restaurant Second Avenue
Due Third Avenue
Lenox Hill Grill/Pizza Lexington Avenue
Quatorze Bistro East 79 Street
Luke'S Bar & Grill Third Avenue
Starbucks Coffee Lexington Avenue
New York Jr. League East 80 Street
Doc Watsons 2 Avenue
Serafina Fabulous Pizza Madison Avenue
Canyon Road Grill 1 Avenue
Sushi Of Gari East 78 Street
Gotchas
-
ReadLine
and WriteLine
are 阻塞函数.
- 打开的窗户是由
Exec
无法隐藏.
上述两种情况的解决方法是使用两层方法,其中 VBA 使用以下命令调用隐藏脚本wsh.Run
,然后运行Exec
(以及与过程交互的任何其他代码)。这种方法的缺点是 StdIn(以及一定程度上的 StdOut)必须写入文件。