Check if correct file type selected?

[RNIB]

I've written a macro that creates a Word document from the content of a spreadsheet but because the name and location of these documents will differ each time the macro is run I'm getting the macro to ask the user to locate the files in question which it then stores as a variable.

However when the macro asks the user to locate the spreadsheet I want it to check that the file ends in .xls i.e. to make sure that the user has selected a spreadsheet and not accidentally clicked on the wrong file.

This is my code at present:

Code: Select all

Let>INPUT_BROWSE=1
Input>spreadsheet_path,Please locate the spreadsheet you wish to convert to Word,
If>spreadsheet_path=*.xls"
Goto>locate_word
Else
Label>refind_xls
Let>INPUT_BROWSE=1
Input>spreadsheet_path,That is not the correct file type. Please locate a spreadsheet ending in .xls,
If>spreadsheet_path=*.xls
Goto>locate_word
Else
Goto>refind_xls
Endif
Endif
Label>locate_word

Trouble is this isn't working and no matter what file I select it then displays the 2nd message box and then gets caught in a loop which I can't escape. Clearly I've not written the code correctly but I don't know where I've gone wrong. How do you get it to check that the file selected does end in .xls

[Marcus Tettmar]

Use the ExtractFileExt function.

But also you don't need all those labels and gotos. You might also want to cater for the newer .xslx extension. E.g.:

Code: Select all

Let>INPUT_BROWSE=1

Let>extension=
While>{(%extension% <> ".xls") AND (%extension% <> ".xlsx")}
  Input>spreadsheet_path,Please locate the spreadsheet you wish to convert to Word,
  ExtractFileExt>spreadsheet_path,extension
EndWhile

Also, if you used your own custom dialog you could create one with a file browse button which restricted the filetype selection to the ones you want. However, I guess the user could still override that by typing something else so you'd still want to verify it.

[RNIB]

Cheers Marcus.

Sorry to appear totally dumb, but are you saying to use that code instead of all the code I posted? If so it still allows me to select a non .xls file.

[Marcus Tettmar]

Yes, I'm saying use the ExtractFileExt command for a quick way to get the file extension. And I'm also suggesting the While/EndWhile loop instead of all those if/the/else and labels and duplicated code which you don't need.

While it will still let you select a non xls file it will keep prompting you until you do.

Which version of Macro Scheduler do you use? ExtractFileExt and While/EndWhile were introduced in v12. If you're using an older version we'll need to do a bit extra work.

[RNIB]

Ahh I see, unfortunately I only have MS 10

[Marcus Tettmar]

Ok, here's an alternative which should work in v10 (I only have v12 and v11 here to test with right now):

Code: Select all

Let>INPUT_BROWSE=1

Let>extensionOK=FALSE
Repeat>extensionOK
  Input>spreadsheet_path,Please locate the spreadsheet you wish to convert to Word,
  Separate>spreadsheet_path,.,file_parts
  Let>extension=file_parts_%file_parts_count%
  Let>extensionOK={(%extension% = "xls") OR (%extension% = "xlsx")}
Until>extensionOK=TRUE

[RNIB]

Thats great Marcus, thank you very much. Just one thing though, is it possible to allow the user to cancel that dialog as at present clicking Cancel just keeps asking the user to locate the spreadsheet again i.e. it gets caught in a loop?

[Marcus Tettmar]

Ok. So you would want to check for an empty string and then break out of the loop and set a flag, or whatever. So if you need me to do that:

Code: Select all

Let>INPUT_BROWSE=1

Let>userCancelled=FALSE
Let>extensionOK=FALSE
Repeat>extensionOK
  Input>spreadsheet_path,Please locate the spreadsheet you wish to convert to Word,
  If>spreadsheet_path<>{""}
    Separate>spreadsheet_path,.,file_parts
    Let>extension=file_parts_%file_parts_count%
    Let>extensionOK={(%extension% = "xls") OR (%extension% = "xlsx")}
  Else
    //cancelled
    Let>userCancelled=TRUE
    Let>extensionOK=TRUE
  Endif
Until>extensionOK=TRUE

If>userCancelled=FALSE
  MessageModal>chosen file: %spreadsheet_path%
Else
  MessageModal>user cancelled
Endif

[RNIB]

Ahh yes, I understand now. Thanks so much Marcus you've really helped me understand how these things work better now. Much appreciated!