这可能看起来很啰嗦,但希望有人能明白我的意思。
我的团队使用 Macro Express Pro 构建宏。几年前,我们使用 VBScript 开发了宏跟踪,以查看是否正在使用宏、由谁和多少人使用,并查看收集的某些数据以查看他们是否正确支付索赔。这将由单独的宏调用。唯一的工作就是接受调用宏作为参数发送给它的内容,然后调用 VBScript 并将参数作为 VBScript 将接收和处理的参数发送。从那时起,VBScript 不断发展,并真正向我们展示了我们正在寻找的数据。
最近,我们中的一些人一直在使用 VBA 在 Excel 中构建最终用户工具。我们的领导层要求这些工具也使用相同的 VBScript 进行跟踪。由于调用 Macro Express Pro 宏将数据发送到 VBScript 是没有意义的,因此我们尝试将直接从 VBA 代码收集的数据发送到 VBScript。
以下是将数据发送到 VBScript 时 Macro Express Pro 跟踪宏的作用:
以下是 VBScript 捕获该信息的方式:
' Sets up the object variables.
Option Explicit
Dim objFSO, objTextFile, conn
' Sets up the all the string variables for the program.
Dim Arg, UserID, MyDocs, strTextFile, strTextLine, strDataLine, Macro_Name, User_ID
Dim Run_Time, Claim_Number, strSQLData(28), dbHeader, dbSQLData, strConnect, message
Dim value1, value2, value3, value4, value5, value6, value7, value8, value9, value10, value11
Dim value12, value13, value14, value15, value16, value17, value18, value19, value20
Dim StartTime, EndTime, Macro_FileName, Ver, strSQL, strTrackVar
Dim strcount, i
'This creates the required Objects
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set Arg = Wscript.Arguments
strcount = Wscript.Arguments.Count
For i = 0 to strcount - 1
If strTextLine = "" Then
strTextLine = Arg.item(i)
Else
strTextLine = strTextLine & "," & Arg.item(i)
End If
Next
'------------------Calls up the Process Tracking Submission sub-------------------
ProcessTrackingSubmission
Sub ProcessTrackingSubmission()
' Set up the origin and destination files
on error resume next
' Read the entire origin file and store as an array usable variables
strDataLine = Split(strTextLine, ",")
Macro_FileName = strDataLine(0)
Macro_FileName= objFSO.GetFile(Macro_FileName).Name
Macro_Name = strDataLine(1)
User_ID = strDataLine(2)
StartTime = strDataLine(3)
EndTime = strDataLine(4)
Claim_Number = strDataLine(5)
If strDataLine(5) <> "" Then
value1 = strDataLine(6)
End If
If strDataLine(6) <> "" Then
value2 = strDataLine(7)
End If
If strDataLine(7) <> "" Then
value3 = strDataLine(8)
End If
If strDataLine(8) <> "" Then
value4 = strDataLine(9)
End If
If strDataLine(9) <> "" Then
value5 = strDataLine(10)
End If
If strDataLine(10) <> "" Then
value6 = strDataLine(11)
End If
If strDataLine(11) <> "" Then
value7 = strDataLine(12)
End If
If strDataLine(12) <> "" Then
value8 = strDataLine(13)
End If
If strDataLine(13) <> "" Then
value9 = strDataLine(14)
End If
If strDataLine(14) <> "" Then
value10 = strDataLine(15)
End If
If strDataLine(15) <> "" Then
value11 = strDataLine(16)
End If
If strDataLine(16) <> "" Then
value12 = strDataLine(17)
End If
If strDataLine(17) <> "" Then
value13 = strDataLine(18)
End If
If strDataLine(18) <> "" Then
value14 = strDataLine(19)
End If
If strDataLine(19) <> "" Then
value15 = strDataLine(20)
End If
If strDataLine(20) <> "" Then
value16 = strDataLine(21)
End If
If strDataLine(21) <> "" Then
value17 = strDataLine(22)
End If
If strDataLine(22) <> "" Then
value18 = strDataLine(23)
End If
If strDataLine(23) <> "" Then
value19 = strDataLine(24)
End If
If strDataLine(24) <> "" Then
value20 = strDataLine(25)
End If
InsertIntoDatabase()
End Sub
已编辑
我们正在寻找的是一种执行类似于以下操作的方法,该方法调用外部程序并将数据存储在 Windows 剪贴板上:
' This is used for tracking purposes
StartTime = Now
Set wshShell = CreateObject("Wscript.Shell")
MacroFile = "Stand-alone Tool"
CurrentMacro = "VBA Tracking Test.xlsm"
UserId = Environ("UserName")
ScriptPath = Environ("UserProfile") & "\Documents\Insight Software\Macro Express\Macro Logs\"
ScriptFile = ScriptPath & "SMT_SP_v3.vbs"
ClaimNum = "1234567890"
EndTime = Now
wshShell.Run """" & ScriptFile & """" & MacroFile & """ """ & CurrentMacro & """ """ & UserId & """ """ & StartTime & """ """ & _
EndTime & """ """ & ClaimNum & """ """ & Value1 & """ """ & Value2 & """ """ & Value3 & """ """ & Value4 & """ """ & Value5 & """ """ & _
Value6 & """ """ & Value7 & """ """ & Value8 & """ """ & Value9 & """ """ & Value10 & """ """ & Value11 & """ """ & Value12 & """ """ & _
Value13 & """ """ & Value14 & """ """ & Value15 & """ """ & Value16 & """ """ & Value17 & """ """ & Value18 & """ """ & Value19 & """ """ & Value20
然后使用以下方法收集该数据:
Set Arg = Wscript.Arguments
strcount = Wscript.Arguments.Count
For i = 0 to strcount - 1
If strTextLine = "" Then
strTextLine = Arg.item(i)
Else
strTextLine = strTextLine & "," & Arg.item(i)
End If
Next
希望这不会太令人困惑。有人对如何做到这一点有任何建议吗?我没有在网上找到任何相关的东西,我已经在 SO 上搜索了可能的重复项。我们正在尝试的解决方案是将参数从 vba 传递到 vbs的变体,但它不起作用。
感谢omegastripes,我现在能够完成任务。虽然这“可能看起来像其他例子”,但我一直在寻找特定的东西,并且 omegastripes 出现了。这是代码:
' This is used for tracking purposes
StartTime = Now
Set wshShell = CreateObject("Wscript.Shell")
MacroFile = "Stand-alone Tool"
CurrentMacro = "VBA Tracking Test.xlsm"
UserId = Environ("UserName")
ScriptPath = Environ("UserProfile") & "\Documents\Insight Software\Macro Express\Macro Logs\"
ScriptFile = ScriptPath & "SMT_SP_v3.vbs"
ClaimNum = "1234567890"
EndTime = Now
wshShell.Run """" & ScriptFile & """ """ & MacroFile & """ """ & CurrentMacro & """ """ & UserId & """ """ & StartTime & """ """ & _
EndTime & """ """ & ClaimNum & """ """ & Value1 & """ """ & Value2 & """ """ & Value3 & """ """ & Value4 & """ """ & Value5 & """ """ & _
Value6 & """ """ & Value7 & """ """ & Value8 & """ """ & Value9 & """ """ & Value10 & """ """ & Value11 & """ """ & Value12 & """ """ & _
Value13 & """ """ & Value14 & """ """ & Value15 & """ """ & Value16 & """ """ & Value17 & """ """ & Value18 & """ """ & Value19 & """ """ & Value20 & """"
我能够通过查看数据库来确认所有数据都得到了完美的跟踪。再次感谢。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句