Skip to main content

Macro that will import new files monthly

Hi,
I am fairly new to IDEA script, and I am trying to modify a macro so that it will import two new files monthly that we receive from another department.  The file names are the same except for the date at the end which is why I have tried using an input box that you can see in part of my script attached.  I am getting many syntax errors and am having trouble working around them.  Is there a better way to go about this?
 

Brian Element Thu, 11/03/2016 - 12:21

Hi Breanna and welcome to the site.

I had a quick look at your script and have a few comments.

You are missing the end function at the end of the first TextImport.  Can you let me know what other syntax errors you are receiving.  Sometimes it is hard to tell without running the script.

How is using your script?  If it is for you then you don't need any error checking for the proper date entry, if others are using it you might want to consider some error checking to validate the date format.

Thanks

Brian

 

Breanna_28 Thu, 11/03/2016 - 13:49

Hi Brian,
Thanks for taking a look!  That and a couple other tweeks seems to have gotten rid of the syntax errors. I didn't realize that was missing. However, now when it prompts me to type in the date the error comes up that the file can't be found.  When I recorded the macro I don't think the right path is written to grab the file.  What's the proper way to do this?
Also, others will be using this so error checking to validate the date format is a good idea. Thanks for the tip!
Breanna

Brian Element Fri, 11/04/2016 - 07:38

Hi Breanna,

Your function doesn't have any path information so it would default to the project folder.  Do you have a certain place where you store the documents for import?  You might want add the working folder info to the path:

workingFolder = Client.WorkingDirectory()
Client.ImportDelimFile workingFolder & "IDEA_Account_" & dateParm & ".csv", "IDEA_Account_" & dateParm & ".IMD", FALSE, "", workingFolder & "IDEA_Account_20161011.RDF", TRUE

If are still getting an error make sure that the path information is the same and that your naming of the file is correct.  The other alternative instead of having an input to get the date is to have an open dialog where the user can select the file.  As there are two files this would have to be done twice.

Brian

 

Brian Element Fri, 11/04/2016 - 07:52

Hi Breanna,

Here is a loop that will check to make sure they have used the proper date format.  The range for the years is 2000 to 2020 but you can change that to your years range.  It will not capture problems like someone entering February 30, but additional code can be added if you want to make sure that the user uses the proper number of days in a month, right now it will accept from 1 to 31 days.  You might also want to add a message if it is false to warn the user that it was not in the proper format.

Do 
	dateParm = InputBox ("Enter Date String (YYYYMMDD): ")
	bDate = True
	If Len(dateParm) <> 8 Then
		bDate = False
	ElseIf Val(Mid(dateParm, 1, 4)) < 2000 Or Val(Mid(dateParm, 1, 4)) > 2020 Then
		bDate = False
	ElseIf Val(Mid(dateParm, 5, 2)) < 1 Or Val(Mid(dateParm, 5, 2)) > 12 Then
		bDate = False
	ElseIf Val(Mid(dateParm, 7, 2)) < 1 Or Val(Mid(dateParm,7 , 2)) > 31 Then
		bDate = False
	End If
Loop While bDate = False

 

Breanna_28 Fri, 11/04/2016 - 11:04

Thanks a lot Brian! The loop seems to work great!  I'm still having trouble with defining my path.  I attached a screenshot of the script and error below.  I'm not sure if my syntax is incorrect or not.  I'm not too experienced with coding in general, so I apologize for the basic level questions.
Breanna

Brian Element Fri, 11/04/2016 - 11:23

Hi Breanna, I see you are adding your path directly to the Client.ImportDelimFile so you can get rid of the Client.WorkingDriectory line.  The WorkingDirctory function will get the current path for the project or change the project's path.  It looks like that it won't allow you to change the path in IDEA Server so just delete the line and see what it gives you.

Brian

Breanna_28 Fri, 11/04/2016 - 12:02

Hi Brian, hmm unfortunately it tells me that the file cannot be found; however, the path it lists in the error is correct

Brian Element Fri, 11/04/2016 - 12:09

Probably a dumb question but you are sure that it is the name of the file.  No extra spaces or anything? 

Breanna_28 Fri, 11/04/2016 - 12:51

No worries not a dumb question! But yes that is the file name.  As you can see here this is the path when I go choose the file manually.  I can't spot any differences.

Brian Element Fri, 11/04/2016 - 12:55

HI Breanna,

Could you post the history for the import.  Also I noticed that the Client.RunAtServer True was not in your script.  Unfortunately I don't have experience using IDEA in a server environment, just the desktop, so I am not sure what the differences might be in scripting for the two.

Brian