This may seem long-winded, but hopefully someone will get my point.
My team builds macros with Macro Express Pro. A couple years ago, we developed macro tracking using VBScript to see if a macro was being used, by who and how many people, and to see gather certain pieces of data to see if they were paying claims correctly. This would be called by a separate macro. It's sole chore is to accept what the calling macro sent to it as parameters, then call the VBScript and send the parameters as arguments the VBScript would pick up and process. Since then, the VBScript has grown and has really shown us the data we were looking for.
Recently, some of us have been building end-user tools in Excel using VBA. Our leadership has requested that these tools utilize the same VBScript for tracking purposes as well. Since it would make no sense to call the Macro Express Pro macro to send the data to the VBScript, we're trying to send the data collected straight from the VBA code to the VBScript.
Here's what the Macro Express Pro tracking macro does when sending data to the VBScript:
Here's how the VBScript captures that info:
' 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
EDITED
What we're looking for is a way to do something similar to the following, which calls an external program and stores data on the Windows clipboard:
' 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
And then collect that data by using the following:
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
Hopefully that's not too confusing. Anyone have any advice on how this could be done? I'm not finding anything relevant online and I've already searched through the possible duplicates here on SO. The solution we're trying is a variation of passing argument from vba to vbs, but it's not working.
