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 :)
 
 

Brian Element Mon, 06/26/2017 - 16:23

Hi Staven, welcome to the site and welcome to IDEA.

The answer is yes.  IDEAScripting gives the functionality to obtain the project folder and then you just add on the sub folder.

Below is an example of using the Client.WorkingDirectory to get the project folder and then adding on the additional information for each of the file.  So long as the files are stored in the "Source Files.ILB" directory within the project folder the script will be able to find them. 


Sub Main
	projectFolder = client.WorkingDirectory
	projectFolder = projectFolder & "Source Files.ILB"
	'to access the files and put them in a variable
	file1 = projectFolder & "\File1.xlsx"
	file2 = projectFolder & "\File2.xlsx"
	file3 = projectFolder & "\File3.xlsx"
End Sub

Hope this helps you out.

Brian

Staven Mon, 06/26/2017 - 17:44

Hi Brian,
I was already head deep in dialog boxes, diving further into vba. Sometimes things can be so simple. This is precisely what I was looking for.
Thank you!

Staven Tue, 06/27/2017 - 08:39

Since you're asking :P
 
Is there a way to display the contents of a variable to test if the result is as you want it to be.
For example if you create a variable of concatenated variables, that you van see whether the concatenation has succeeded?

Brian Element Tue, 06/27/2017 - 08:42

Sure, you can use the msgbox to do this.  Here is an example

MyVariable = "This is a string"

msgbox MyVariable

This will open a message box with "This is a string", you can use it for any type of variables.

Staven Tue, 06/27/2017 - 10:24

So I made this routine:
Macro1:
Sub Main
 
Dim projectdir As String
 
Dim fiscalyear As String
 
projectdir = client.WorkingDirectory
 
fiscalyear = 2016
 
End Sub
 
I want to re-use the variables from macro 1 in macro 2 and on. For this example only macro2:
 
Sub Main
 
client.RunIDEAScript "Macros.ILB\Macro 1.iss"
 
Dim file1 As String
                        
projectFolder = projectdir & "Source Files.ILB"
file1 = projectFolder & "\users" & fiscalyear & ".xlsx"
 
Call ExcelImport()'C:\Users\Staven\Documents\My IDEA Documents\IDEA Projects\ProjectX\Source Files.ILB\users 2016.xlsx
End Sub
 
 
' File - Import Assistant: Excel
Function ExcelImport
Set task = Client.GetImportTask("ImportExcel")
dbName = file1
task.FileToImport = dbName
task.SheetToImport = "users"
task.OutputFilePrefix = "00"
task.FirstRowIsFieldName = "TRUE"
task.EmptyNumericFieldAsZero = "FALSE"
task.PerformTask
dbName = task.OutputFilePath("users")
Set task = Nothing
Client.OpenDatabase(dbName)
End Function
 
Apparently defined variables in one macro are not retained and cannot be reused in others? You can only push four arguments to child macro's? http://ideascripting.com/forum/call-script-within-script
 
I then just tried the simple version:
 
Sub Main 
Dim file1 As String
 
projectdir = client.WorkingDirectory                      
 
projectFolder = projectdir & "Source Files.ILB"
 
file1 = projectFolder & "\users" & "2016" & ".xlsx"
 Call ExcelImport()'C:\Users\Staven\Documents\My IDEA Documents\IDEAProjects\ProjectX\Source Files.ILB\users 2016.xlsx
 
End Sub
 
 
  ' File - Import Assistant: Excel
Function ExcelImport
Set task = Client.GetImportTask("ImportExcel")
dbName = file1
task.FileToImport = dbName
task.SheetToImport = "users"
task.OutputFilePrefix = "00"
task.FirstRowIsFieldName = "TRUE"
task.EmptyNumericFieldAsZero = "FALSE"
task.PerformTask
dbName = task.OutputFilePath("users")
Set task = Nothing
Client.OpenDatabase(dbName)
End Function
 
 I then get an error; "you must enter a valid input file name".
 
Any tips?
 
Preferably I would want one Macro that contains all the parameters, and to be activated whenever I run another macro, so any macro I start will use the same parameters. In that way also not all macro's and/or script always has to be run.
Is that possible?

Brian Element Tue, 06/27/2017 - 12:43

Hi Staven,

The reason you are getting an invalid input file name for file1 is that in the sub you have defined it as a local variable.  In IDEAScript there are local variables that you can only access from within the sub or function you have defined them.  There are also global variables that any sub or function can have access to.  In this case move the Dim file1 as string before the Sub Main and that should fix that problem.

When you are calling a script from a script you can only transfer four variables.  The way to get around this is to create a text file, save your variable info there and then the second script opens that file and gets the variables from it.

If you develop anything for SmartAnalyzer there are option there to save variables and share them between SmartAnalyzer apps.

Brian

Staven Tue, 06/27/2017 - 18:40

Hi Brian,
Again thank you for sharing this information. Also figured that the public statement instead of dim doesn't work in IDEA.
To avoid another surge of ineffecient trial and error, perhaps you can share an example of writing to a text file? Unless either of these suffice?: https://stackoverflow.com/questions/11503174/how-to-create-and-write-to… or https://msdn.microsoft.com/en-us/library/aa265018(v=vs.60).aspx
Will check out the smartanalyzer as well ! :)

Brian Element Tue, 06/27/2017 - 18:59

Yes, there are a lot of differences.  

For writing to a text file you have found the source.  IDEA has some internal sources but usually I use the Scripting.FileSystemObject for reading and writing to files as it has a lot more functionality then what is available within IDEA.

Good luck with your project.

Brian