Macro that will import new files monthly
Forums
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?
Hi Brian,
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
Hi Breanna,
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
Hi Breanna,
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
Thanks a lot Brian! The loop
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
Hi Breanna, I see you are
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
HI Breanna,
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
Hi Breanna and welcome to the
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