Excel Macro Munging
Category: Programming
#Excel #VBA #macro
The problem:
A set of items in VSO where we wanted the ID as part of the title text.
(It's a long-ish story, but the default VSO board shows only title and assigned to so we adopted a convention of adding the ID to the title text too).
Over time, we'd gathered a number of MVPs on the board where the ID wasn't included. Rather than hand editing, I slurped the query into Excel using the TFS team extensions and ran a little macro to update the items. Here's the code:
Sub fixMissingIDInTitle()
Dim MyCell As Range, MyRange As Range
Dim count, total As Long
Dim sTitle As String
Dim sID As String
Dim iIDLength As Integer
' grab the starting point and then extend to get the range of items
Set MyRange = Sheets("All MVPs").Range("C3")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
total = 0
count = 0
' iterate over the range
For Each MyCell In MyRange
sTitle = MyCell.Value
sID = MyCell.Offset(0, -2).Value
iIDLength = Len(sID)
If (Left(sTitle, iIDLength) = sID) Then
' do nothing - ID already at start
' Q: but is it the right ID?
Else
MyCell.Value = sID + " - " + sTitle
count = count + 1
End If
total = total + 1
Next MyCell
MsgBox ("Updated " + CStr(count) + " of " + CStr(total) + " items.")
End Sub