Skip to main content

Using a global variable defined Macro script 1 and using the same variable value in Macro script2

Hi All & Brian,
I have a doubt regarding declaring a variable in one macro script and then using the variable's value defined by the end user in the first macro in the second macro. To give you more perspective this is the code i am working on:
 
Macro 1: 
Option Explicit
Global sFilename As String
( The variable sFilename then will contain the name of the database selected by the user, it was taken from your field mapping script template )
 
 
Macro 2:
Sub Main
Call TopNExtraction()       'sFilename is the variable database name
client.RefreshFileExplorer
Call ExportDatabaseXLSX()       'KPI 1.IMD
End Sub 
 
' Data: Top Records Extraction
Function TopNExtraction
Set db = Client.OpenDatabase(sFilename)    ' I get get an error code here, saying the database was not found or is in use.
Set task = db.TopRecordsExtraction
Dim dbName As String ' Made an addition while using sFilename as a variable
task.IncludeAllFields
task.AddKey "CREDIT", "A"
dbName = "KPI 1.IMD"
task.OutputFileName = dbName
task.NumberOfRecordsToExtract = 30
task.CreateVirtualDatabase = False
task.PerformTask
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)
End Function
 
--------------
 
My end goal is that the after the user selects the database and maps the neccesary fields in the dialogue box, the 2nd Macro which has the actual analysis to be run, then selects that database from the variable defined in the 1st Macro i.e. sFilename.Please help me here, i am stuck.

Brian Element Wed, 07/03/2019 - 11:35

How many variables will you be transferring between the scripts.  Calling a script from a script you can send four variables, if it is greater than that then you need to create a text file to hold the variables and script two would read the text file to populate the variables.

rahulsaxena3 Mon, 07/08/2019 - 01:25

Hi Brian,
Firstly, thanks for replying !
It would be more than 4 variables, can you please show me an example of how to create that text file and then how to call it in the 2nd script ?
 
What would i need write exactly in that text file. p

Brian Element Mon, 07/08/2019 - 09:14

In reply to by rahulsaxena3

Here are two example files for you.  The first one, the Send Variable writes the variables to a text file and then calls the second script and sends the name of the text file.  The second file gets the text file name and then loads in the variables.  I have attached both files for you.

Example Macro to Send Variables.iss


Option Explicit

'define variables to send (could also use an array)
Dim var1 As String
Dim var2 As String
Dim var3 As String
Dim var4 As String
Dim var5 As String
Dim sVarTextFilename As String

Sub Main

	'set-up the variables
	var1 = "1"
	var2 = "Oranges"
	var3 = "20190708"
	var4 = "Test"
	var5 = "Script"
	'call the file that writes the variables to a text file and returns the filename and path
	sVarTextFilename = writeVariablesToTextFile()
	'call the second IDEAScript and send the filename that holds the variables
	Client.RunIDEAScriptEX "Macros.ILB\Example Macro to Get Variables.iss", sVarTextFilename, "", "", ""
End Sub

Function writeVariablesToTextFile() As String
	Const ForReading = 1
	Const ForWriting = 2
	Dim fso As Object
	Dim f As Object
	Dim sTextFilename As String
	
	'set the default file location and name
	sTextFilename = Client.WorkingDirectory() & "var test file.txt"
	'The Scripting.FileSystemObject allows us to access the file system to write and read files
	Set fso = CreateObject("Scripting.FileSystemObject")
		'Create the new file if it doesn't exist and write the variables to it.
		Set f = fso.OpenTextFile(sTextFilename, ForWriting, True)
			f.writeline var1
			f.writeline var2
			f.writeline var3
			f.writeline var4
			f.writeline var5
			f.close
		Set f = Nothing
	Set fso = Nothing
	writeVariablesToTextFile = sTextFilename
End Function

Example Macro to Send Variables.iss


Option Explicit
'define variables to send (could also use an array)
Dim var1 As String
Dim var2 As String
Dim var3 As String
Dim var4 As String
Dim var5 As String
Dim sVarTextFilename As String

Sub Main
	'obtain the name of the text file that holds the variables
	sVarTextFilename = arg1
	
	Call readTheVariables()
	MsgBox "Var1: " & var1
	MsgBox "Var2: " & var2
	MsgBox "Var3: " & var3
	MsgBox "Var4: " & var4
	MsgBox "Var5: " & var5
End Sub

Function readTheVariables()
	Const ForReading = 1
	Const ForWriting = 2
	Dim fso As Object
	Dim f As Object
	Dim i As Integer
	Dim sText As String

'MsgBox sVarTextFilename 
'sVarTextFilename = "C:\Users\elementb\Documents\My IDEA Documents\IDEA Projects\Samples\var test file.txt"
	i = 1
	Set fso = CreateObject("Scripting.FileSystemObject")
		'Set 'open the file for reading
		Set f = fso.OpenTextFile(sVarTextFilename, ForReading)
			Do While f.AtEndOfStream <> True
				'for large number of variables using an array would be better
				Select Case i
					Case 1
						var1 = f.readline
					Case 2
						var2 = f.readline
					Case 3
						var3 = f.readline
					Case 4
						var4 = f.readline
					Case 5
						var5 = f.readline
				End Select
				i = i + 1
			Loop
			'msgbox f.ReadAll
		Set f = Nothing
	Set fso = Nothing
End Function

rahulsaxena3 Wed, 07/10/2019 - 02:16

Thank you so mcuh Brian, let me try and use this, will take a few days but i will surely get back to you. Thnk you for taking out time and answering my questions