Finding the last instance of a character

I had an issue at work where a VBA application I'd developed was not working due to the persons name. I'll make up the names to retain privacy.
The string I used was in the format "Full Name - StaffID" so for example, "John Smith - 123456".
The program looked for " - " (note the spaces on either side of the dash) and then pulled out everything following that as the staff ID. This worked fine for people like John, and for people like "Sarah-Jane Smith - 789123" and "Derek Parker-Smith - 456789". The problem occured when someone's name had been entered into the system as "Joseph Macfarland - Smith - 321654", because the system took "Smith - 321654" as his staff ID and obviously bugged out.

To rectify this I needed to search the string backwards (in effect).
The easiest way I could find to do this was to split it into an array, using " - " as the delimiter, I could then just use the last entry in the array.
Here's the code I used...

Dim v as Variant
v = split(MainForm.staffmember.text, " - ")
StaffID = v(UBound(v))

a nice simple three lines of code...
Hope this helps you,

Ben

Random IT Bits Numero Uno

Good morning guys (and gals)!
I started my other blog (www.benmoorhouse.blogspot.com) a while back, but didn't really know what was going to be put in it, or even the type of content I was going to include!
This is a kick start for me. I'm going to duplicate content though as an experiment on domain names.
This blog will cover the odd random ME update, but mainly me providing script/a walkthrough about a computer bit which I've either found or used in the past week. I'll provide the script / walkthrough, and then talk you through how it works, with a view to helping you use it on your own site.
If at any point you want something adding/researching/explaining that little bit more, PLEASE feel free to just drop me an email (development@moorhouses.plus.com) - I dont bite, and would love to hear from you.

Ok enough of the introduction, lets get onto some code.

Opening an .xls document from a web page in Excel rather than in IE.

Script

This goes in the head
< language="VBScript">
Function openExcel()
Set ExcelApp = CreateObject("Excel.application")
ExcelApp.Visible = True
strPathDoc = "test.xls"
Set myExcelDoc = ExcelApp.Workbooks.Open(strPathDoc)
ExcelApp.Run ("Auto_Open")
Set myExcelDoc = Nothing
Set ExcelApp = Nothing
End Function
< /script>

This goes in the body where you want the user to click
< href="#" title="test xls file" style="text-decoration:none" onclick="openExcel()">

Explanation

Ok now some of you may be thinking "Why on earth would I want to do that?", but you would be suprised at how many times people ask me either because they think it looks rubbish, or they want to really mess around with the excel environment, and it breaks when it loads in IE.

The code is Visual Basic Script, and is embedded into the HTML of your website just as you would embed some JavaScript code. You then call the function again, just as you would a JavaScript function, by calling it from a link.

How it works

This is a nice and easy to tweak script.
First it creates an instance of the Excel application called ExcelApp, and makes it visible.
Then so that you dont have one line of code stretching acros 82 lines (!), it puts the path to the file in a variable called strPathDoc.
Then it uses ExcelApp (the newly opened instance of Excel) to open the workbook in the variable.
As the main reason for doing this code is because you want to heavily alter the Excel environment, I assume you will want to launch some code when the file has opened. Which is why the next line runs the subroutine called Auto_Open (which would normally start automatically when the file was opened via the file system).
The next three lines are tidying up - setting the objects to nothing, and ending the function.

nice and easy.
This one is so easy to change to work for you its amazing, simply set your filepath to your excel file on strPathDoc, and change the name of the code you want to run. You could even just comment the last bit out if you didnt need it.


Oh, and for those of you who dont know very much HTML, the link line is broken down into:
< a - the all standard anchor tag
href="#" - the hypertext reference - turns the anchor tag into something clickable without changing the page (you can replace the # with a URL to make the page change too).
title - the title of the link, this will come up when you hover over the link as a tool tip.
Style - you can add style information in here, I removed the text decoration so that the link wasn't underlined. This isn't generally good practice, but was the make or break for my intranet page.
onClick - the all important bit of code which calls the function which was described above.

Anyway, my wife has just got up so it's time to raise the troops and get them ready for off.
All the best,

Ben