Making use of a source file path variable

Staven

Forums

Hi there,
I recently moved from ACL to IDEA, so I'm in the process of learning.
After having searched for some time I couldn't really find an answer to the following question.
 
So I have 3 different source files I would like to import. After creating the script I want to be able to share my IDEA project with scripts (macro's) with a colleague. The goal is for that colleague to run the macro's without modifying the scripts (or at least bring it back to a minimum).
So in ACL I could make use of variables. Is that possible in IDEA as well?
Scenario:
Source file location: C:\Users\Staven\Documents\My IDEA Documents\IDEA Projects\Project X\Source Files.ILB\
In that location I have 3 files: File1.xlsx, File2.xlsx and File3.xlsx
The problem is that my username is in the file path. 
In ACL I would've been able to make one variable, assign the project location once and it could be reused for every import statement:
v_Source_dir = "C:\Users\Staven\Documents\My IDEA Documents\IDEA Projects\Project X\Source Files.ILB\"
Using the variable:
Macro1
Sub Main
Call ExcelImport()'%v_Source_dir%\File1.xlsx
End Sub
Function etc
Macro2
Sub Main
Call ExcelImport()'%v_Source_dir%\File2.xlsx
End Sub
Function etc
Macro3
Sub Main
Call ExcelImport()'%v_Source_dir%\File3.xlsx
End Sub
Function etc
 
Is something like that possible in IDEA?
Thank you in advance :)
 
 

rahulsaxena3 Tue, 07/02/2019 - 06:18

Hi Brian,
I have a simillar 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)
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.