![]() ![]() Step 3 – Execution takes place from lines 25 to 35. Next up is Step 2 – Exploration, which is just line 20 in this case! Since there are (potentially) many filepaths in the fdoUserPicks object, we use a For…Next loop to cycle through them. So much work accomplished in this little one-liner!įinally, we’ll check fdoUserPicks to see if it is Nothing on line 17 - this is to catch a user clicking “Cancel” on the Windows Explorer window that was prompted on line 14… and with that, the Setup phase is done. The PromptUserToSelectFiles function returns a FileDialog object, which we assign to variable fdoUserPicks on line 14. TargetFileType (String): this allows you to limit the select-able file type to, say, just text - this function supports “XLSX”, “XLSB”, “XLSM”, “XLS”, “CSV”, “TXT”, and “ALL”.DisplayText (String): this is the message you’ll see on the Windows Explorer prompt - here we set it to strMessage, which was assigned to “Please select the target semicolon-delimited TXT files” on line 13 above.AllowMultiples (Boolean): if you’d like the user to be able to select more than one file, set this to True - otherwise set it to False.Essentially, you only need to pass it 3 things: The Step 1 – Setup phase is handled entirely by lines 13 through 17 - the awesome PromptUserToSelectFiles function from the VBA Toolbelt mentioned earlier in this tutorial. Let’s examine ConvertSemicolonTextToXLS using the 4-Step VBA Process as our guide: Here’s a link to the code above so you can review it side-by-side with the walk through below. Here’s a subroutine to convert those suckers that leverages one of my absolute favorite VBA Toolbelt functions, PromptUserToSelectFiles.īefore you get TOO lathered up about the length, know that lines 43 to 140 are already written for you in the Toolbelt, so we’re only going to focus on lines 1-41 (the ConvertSemicolonTextToXLS subroutine):
0 Comments
Leave a Reply. |