VB Script to Select a Month and Year

[#1]

Hi,

I was wondering if it was possible to produce some VB Script which would enable me to have a dropdown box where I can select the month and year:

e.g.

Month:

Jan

Feb

Mar

Apr

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Year:

2010

2011

2012

2013

Once, I've made these selections - I want this to become a variable value vFilePath = "C:\My Documents\Data (Month Year)" which I can put in my loading script. i.e. if I choose Jul and 2012, then vFilePath becomes C:\My Documents\Data (Jul 2012)

Is this possible to do?

Thanks.  Any help would be greatly appreciated.

[hic]

You do not need a macro for this.

Do it this way instead:

1) Have this in the script:

Dates:

Load

   Date(AddMonths(MakeDate(2010,1),recno()-1),'MMM YYYY') as YearMonth

   autogenerate 48;

2) Display this field in the UI.

3) Create an inputbox with one variable: "vFilePath"

4) Set "Input box properties -> Constraints -> Settings for selected variable -> Value" to

='C:\My Documents\Data (' & MinString(YearMonth) & ')'

Now you can select your month in a list box and getyour variable that you can use in the script.

HIC

[#3]

Hi Henric,

Thanks for getting back to me.  This doesn't work when I reference vFilePath in the load script (i.e. $(vFilePath)).

When I run the script, it comes up with an error and shows vFilePath as 'C:\My Documents\Data (' & MinString(YearMonth) & ')' instead of the actual path using the list box when selecting the month/year.  Is there anyway this can be copied as text into the variable?

Thanks.

[jonathandienst]

Hi

Make sure that you include the = sign as first character in the string.

Regards

Jonathan

[#5]

Hi,

Henric is correct...

Create a variable and in the definition section write ='C:\My Documents\Data (' & MinString(YearMonth) & ')' , you have to do it through Variables Overview.

[#6]

Hi,

I've put the equals sign in and it still doesn't work.

I get this error message:

Cannot open file '='C:\My Documents\Data\( '&MinString')  The filename, directory name, or volume label syntax is incorrect.

Maybe I'm doing something wrong.

Thanks.

[hic · Accepted solution]

You're right. I have tested it and for some reason it does not do what I expected.

Anyway, there is another way to do this without macros.Check the attached file. In it I have an input box where the variable values are constrained to the only possible years/months. When you run the script, these variables can be used to define fields or file paths.

Good luck!

HIC

[#8]

Thanks Henric!! That works now!