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 :)
Staven
Offline
Last seen: 5 years 10 months ago
Joined: 06/26/2017 - 15:53
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.
Hope this helps you out.
Brian
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!
Not a problem, let me know if you have any other questions.
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?
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.
Great thanks :) !
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?
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
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 ! :)
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
Pages