Microsoft Excel – Clean function – calling functions from VBS – Accessing files from VBS on Mac

Today I have been working on importing a text file into Excel and using VBS to do some processing and transformation of it.  In the process I found 3 things worth sharing/commenting on:

1) You may be familiar with the “trim()” function in Excel and in VBS.  It removes extra white spaces in a string.  But did you know that there is a “clean()” function in Excel that will remove “non-printable” characters.  There is a good write up of it at www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=118:excel-text-and-string-functions-trim-a-clean&catid=78&Itemid=474

 

2) There is no “clean()” function in VBS.  But you can call all of the Excel functions by using “Application.WorksheetFunction” object.  Therefore you can call the “clean()” function from VBS by using “Application.WorksheetFunction.clean()”

 

3) File paths from VBS in Mac are not what you expect.  You can not just put in the /path/to/the/file.txt and expect for it to work.  Still a little more work to figure this one out.

 

Leave a Reply

Your email address will not be published.