Created
June 15, 2013 03:09
-
-
Save vdavez/5786709 to your computer and use it in GitHub Desktop.
This is a preliminary method of pulling a Short Title from LIMS
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub LIMS_Test() | |
' | |
' LIMS_Test Macro | |
' | |
' | |
Dim LIMS_URL As String | |
Dim Short_Title As String | |
Dim IE As InternetExplorer | |
Set IE = New InternetExplorer | |
Dim doc As HTMLDocument 'variable for document or data which need to be extracted out of webpage | |
LIMS_URL = "http://dcclims1.dccouncil.us/lims/legislation.aspx?LegNo=" & "B20-0001" | |
IE.Visible = False | |
IE.Navigate (LIMS_URL) | |
Do | |
DoEvents | |
Loop Until IE.ReadyState = READYSTATE_COMPLETE | |
Set doc = IE.Document | |
Short_Title = doc.getElementById("ShortTitle").innerText | |
Short_Title = PCase(Short_Title) 'Borrowed from the internet | |
Short_Title = Right(Short_Title, Len(Short_Title) - 1) | |
Short_Title = Left(Short_Title, Len(Short_Title) - 3) | |
MsgBox Short_Title | |
End Sub | |
'************************************** | |
' Name: A Better Proper Case Function | |
' Description:Just copy/paste this function into your code, and it will allow you to convert a string to proper case. Now you've got UCase, LCase, AND PCase. | |
' By: Doug Tyson | |
' | |
' Assumes:I tried to account for as many "unimportant" words as I could, but I'm sure I've missed some. Just add any entries you feel necessary in the select statement. | |
' | |
'This code is copyrighted and has' limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=8548&lngWId=4'for details.'************************************** | |
Function PCase(strInput) | |
'Variable declaration. | |
Dim strArr | |
Dim tmpWord | |
Dim tmpString | |
Dim last | |
'Create an array to store each word in the string separately. | |
strArr = Split(strInput, " ") | |
If UBound(strArr) > 0 Then | |
For x = LBound(strArr) To UBound(strArr) | |
'Set each word to lower case initially. | |
strArr(x) = LCase(strArr(x)) | |
'Skip the unimportant words. | |
Select Case strArr(x) | |
Case "a" | |
Case "an" | |
Case "and" | |
Case "but" | |
Case "by" | |
Case "for" | |
Case "in" | |
Case "into" | |
Case "is" | |
Case "of" | |
Case "off" | |
Case "on" | |
Case "onto" | |
Case "or" | |
Case "the" | |
Case "to" | |
Case "a.m." | |
strArr(x) = "A.M." | |
Case "p.m." | |
strArr(x) = "P.M." | |
Case "b.c." | |
strArr(x) = "B.C." | |
Case "a.d." | |
strArr(x) = "A.D." | |
Case Else | |
'Capitalize the first letter, but don't forget to take into account that | |
'the string may be in single or double quotes. | |
If Len(strArr(x)) > 1 Then | |
If Mid(strArr(x), 1, 1) = "'" Or Mid(strArr(x), 1, 1) = """" Then | |
tmpWord = Mid(strArr(x), 1, 1) & UCase(Mid(strArr(x), 2, 1)) & Mid(strArr(x), 3, Len(strArr(x)) - 2) | |
Else | |
tmpWord = UCase(Mid(strArr(x), 1, 1)) & Mid(strArr(x), 2, Len(strArr(x)) - 1) | |
End If | |
strArr(x) = tmpWord | |
End If | |
End Select | |
'The unimportant words may need to be capitalized if they follow a dash, colon, | |
'semi-colon, single quote or double quote. | |
If x > 0 Then | |
If InStr(strArr(x - 1), "-") _ | |
Or InStr(strArr(x - 1), ":") _ | |
Or InStr(strArr(x - 1), ";") Then | |
tmpWord = UCase(Mid(strArr(x), 1, 1)) & Mid(strArr(x), 2, Len(strArr(x)) - 1) | |
strArr(x) = tmpWord | |
End If | |
End If | |
Next | |
Else | |
strArr(0) = LCase(strArr(0)) | |
End If | |
'Make sure the first word in the array is upper case, but don't forget to take into account | |
'that the string may be in single or double quotes. | |
If Mid(strArr(0), 1, 1) = "'" Or Mid(strArr(0), 1, 1) = """" Then | |
tmpWord = Mid(strArr(0), 1, 1) & UCase(Mid(strArr(0), 2, 1)) & Mid(strArr(0), 3, Len(strArr(0)) - 2) | |
Else | |
tmpWord = UCase(Mid(strArr(0), 1, 1)) & Mid(strArr(0), 2, Len(strArr(0)) - 1) | |
End If | |
strArr(0) = tmpWord | |
'Also, make sure the last word in the array is upper case, but don't forget to take into account | |
'that the string may be in single or double quotes. | |
last = UBound(strArr) | |
If Mid(strArr(last), 1, 1) = "'" Or Mid(strArr(last), 1, 1) = """" Then | |
tmpWord = Mid(strArr(last), 1, 1) & UCase(Mid(strArr(last), 2, 1)) & Mid(strArr(0), 3, Len(strArr(last)) - 2) | |
Else | |
tmpWord = UCase(Mid(strArr(last), 1, 1)) & Mid(strArr(last), 2, Len(strArr(last)) - 1) | |
End If | |
strArr(last) = tmpWord | |
'Rebuild the whole string from the array parts. | |
For x = LBound(strArr) To UBound(strArr) | |
tmpString = tmpString & strArr(x) & " " | |
Next | |
PCase = tmpString | |
End Function |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment