Help with dates within an idea script

Macro script attached

Hi pcallan0,

That is strange about not being able to attach a file, I just tried it as a user and it seemed to work.  What type of file where you trying to upload and where you using the Choose Files option below to attach a file?

As for your script I think maybe the easiest way is to have a script that reads a text file with the information and then creates the equation based on the script.  The user would then only have to update a text file instead of changing the iss file.  I will see if I can get an example for you.

Hi pcallan0,

Here is an example of using a text file for your dates and rates and then reading the text file to create the equation.  The text file needs to be in the project folder.

Let me know if you have any questions.

Thanks

Brian

Hi Brian,

Thanks a million. I'm away for a couple of days but will get back to you properly on my return.

All the best,

Phil

Hi Brian,

That works perfectly, and your solution is far better than the one I had envisaged.

It will be far more efficient to have a "rates" file that can be updated when needed, and there will be no need to keep changing the macro.

I'll be incorporating your code into my current macro, and I have a couple of additional questions about working in folders.

The macro is designed to create a folder, import the source file, and move it to the folder. The folder is defined as a string and passed through the macro as foldername (where foldername = "Customs Debts")

I do this for tidiness, as folks might run the macro within an existing audit project and it keeps the debt calculation element separate.

However, working in folders can cause a problem which I've never been able to satisfactorily sort out.

Say for example my working directory at the beginning is my IDEA project directory e.g. C:\Phil\Idea Project 1

However, when I create the folder the working directory becomes C:\Phil\Idea Project 1\Customs Debts

This means I am unable to code paths to folders or files in the original project

For example wd &"Exports.ILB\Output file.xlsx" becomes C:\Phil\Idea Project 1\Customs Debts\Exports.ILB\Output file.xlsx

I'd love a way to define and "lock in" the main project directory path as a string so that I call on it whenever I need to.

I'd say this is one of those questions where the answer is very obvious to those who know, but unfortunately I'm not one of them :D

If you could advise you'd fix something that has puzzled me for several years.

Kind regards,

Phil

Hi Phil, this is a known issue that I have asked many times that Caseware fix or at least add another variable that always holds the project folder variable but it is still outstanding.

The workaround is to do a client.closeall, this closes all the IDEA files and it seems to reset the working directory to the same as the project folder.  Here is a short demo showing what is going on.  It creates a folder and copies a file into it and then opens the file, when the file is opened that is when the working directory changes, after that I do a closeall and the working directory changes back to the project folder.  Hopefully this solves your problem. 

Dim wd As String

Sub Main
	Dim task As task
	wd = Client.WorkingDirectory()
	MsgBox "Start of script: " & wd
	
	'create a new folder
	Set task = Client.ProjectManagement
	On Error Resume Next 'need this in case the folder already exists
	task.CreateFolder "IDEATest"
	Client.RefreshFileExplorer
		
	wd = Client.WorkingDirectory()
	MsgBox "After creating the folder: " & wd
	
	task.CopyDatabase "Sample-Customers.IMD", "IDEATest"
	
	wd = Client.WorkingDirectory()
	MsgBox "After copying the file to the new folder: " & wd
	
	Client.OpenDatabase("IDEATest\Sample-Customers.IMD")
	
	wd = Client.WorkingDirectory()
	MsgBox "After opening the file in the new directory, this changes the working directory: " & wd
	
	client.closeall
	
	wd = Client.WorkingDirectory()
	MsgBox "After performing a closeall, this resets to the project folder: " & wd
	
End Sub

Hi Brian,

Thank you, this does solve the problem.

I typically open the final "results" file so it is displayed to users at the end of the macro run.

However, I know see this is changing the workingdirectory path which is what causes problems if the macro is rerun.

Your solution does fix my problem so I guess the users will just have to navigate to the file.

I had tried to fix this by adapting this snippet from the IDEA Script Editor help:

Sub Main

' Get the current Desktop project folder.

Current = Client.WorkingDirectory()

' Display the Desktop project folder on screen.

MsgBox Current

' Set a new Desktop project location.

Client.WorkingDirectory = "C:\TEST"

' Show the result.

MsgBox Client.WorkingDirectory

' Restore the original Desktop project location.

Client.WorkingDirectory = Current

MsgBox Current

End Sub

When I tried this as a stand alone script it seemed to work.

When I incorporated it into my script the message box at the end of the macro showed the original working directory.

I thought I was onto a winner, but I thought too soon because when I reran the macro the initial message showed the changed working directory.

Very confusing but your client.closeall does give me the workaround.

Thank you once again and have a lovely weekend,

All the best

Phil

EUREKA

Hi Brian,

Thank once again for your advice. I've been playing around with the client.closeall and found something that might be of interest.

If I put the client.closeall as the first command in submain, before I define the working directory, this resets everything.

Then when the working directory is defined (intially) it will be the project folder.

So no matter what I do in the macro, if I need to rerun it on the same project it will always work and my paths will make sense.

This also allows me to run my macro and open the results file at the end to display to the user.

In addition, if the macro crashes for any reason, it doesn't affect the ability to rerun it as the directory is reset at the start and not the end.

A win win for me and I would never have figured this out without your advice.

Thanks a million,

Phil