Create File Directory From Excel Rows

[steve.johnson]

This script works fine, however, I can't get it to stop properly. Can anyone assist?

Code: Select all

//excel file location
Let>filename=C:\SJ_FW_TEST\FIRMWARE_FILE.xlsx
IfFileExists>filename
ExecuteFile>filename
WaitWindowOpen>Microsoft Excel -*
Let>r=2
Label>start
//get the fields for this row
DDERequest>Excel,filename,R%r%C1,field_1,60
DDERequest>Excel,filename,R%r%C2,field_2,60
DDERequest>Excel,filename,R%r%C3,field_3,60
//remove the CRLF that excel adds
StringReplace>field_1,CRLF,,field_1
StringReplace>field_2,CRLF,,field_2
StringReplace>field_3,CRLF,,field_3
//create file directory for excel row
CreateDir>e:\%field_1%
CreateDir>e:\%field_1%\%field_2%
CreateDir>e:\%field_1%\%field_2%\%field_3%
If>field_*=NIL,finish
Let>r=r+1
Wait>0.05
Goto>start
Label>finish
CloseWindow>Microsoft Excel -*
Message>Files Have Been Added!

[JRL]

If>field_*=NIL,finish

Macro Scheduler doesn't have a built in value for "NIL". In Macro Scheduler its just another text string unless you assign it a value

Code: Select all

Let>NIL=

But nil is available by specifying nothing as in the above example.

Also an asterisk is not a wildcard character in Macro Scheduler. My thought on this to follow up on the code you already have would be three if> statements:

Code: Select all

If>%field_1%=,finish
If>%field_2%=,finish
If>%field_3%=,finish

So your rewritten code would be:

Code: Select all

//excel file location
Let>filename=C:\SJ_FW_TEST\FIRMWARE_FILE.xlsx
IfFileExists>filename
ExecuteFile>filename
WaitWindowOpen>Microsoft Excel -*
Let>r=2
Label>start
//get the fields for this row
DDERequest>Excel,filename,R%r%C1,field_1,60
DDERequest>Excel,filename,R%r%C2,field_2,60
DDERequest>Excel,filename,R%r%C3,field_3,60
//remove the CRLF that excel adds
StringReplace>field_1,CRLF,,field_1
StringReplace>field_2,CRLF,,field_2
StringReplace>field_3,CRLF,,field_3
//create file directory for excel row
CreateDir>e:\%field_1%
CreateDir>e:\%field_1%\%field_2%
CreateDir>e:\%field_1%\%field_2%\%field_3%
If>%field_1%=,finish
If>%field_2%=,finish
If>%field_3%=,finish
Let>r=r+1
Wait>0.05
Goto>start
Label>finish
CloseWindow>Microsoft Excel -*
Message>Files Have Been Added!

I can't be sure this will work because I can't be sure your "field_X" variable values are actually equal to "nothing". You need to make sure each of the three "If>%field_X%=???,finish" lines are matching the actual value of the variables at the time you want the loop to end.

Hope this makes sense.
(Edit- fixed typo)

[Me_again]

You may want to put the

Code: Select all

If>%field_1%=,finish
If>%field_2%=,finish
If>%field_3%=,finish

section before the CreateDir> lines otherwise you'll be trying to create unnamed directories. Also note that as written this code will terminate the routine if any of the three fields is blank which may not be what you intend.

[steve.johnson]

Thank you very much! It works perfect.