Archive for April, 2009

Refreshing TFS list in Excel using VBscript

Ok, this post isn’t even SharePoint-related, but since it took me 2 hours to figure out how to do this task (and since there’s little documentation on the subject), I’m going to post it here anyway. Maybe it’ll save someone hours of research and coding.

My task today was to programmatically refresh a Team Foundation Server list that was embedded into an Excel workbook. The process had to be completely automated. I found John Lawrence’s post and also the TFS team blog post. The problem with these solutions, they only work within an Excel VBA macro. I needed a way to open Excel, perform the TFS list refresh, and close it, without any user intervention.

Placing the macro into the workbook start-up didn’t work – apparently, the Team toolbar (and the accompanying Refresh button) don’t activate until AFTER all the start-up macros have run (which caused a problem for me).

So here’s a simple solution using my good old friend VBScript:

Const INPUT_FILE = “your_file_path”

Dim objWorkbook
Dim Controls

Set objWorkbook = CreateObject(“Excel.Application”)
objWorkbook.Visible = True
objWorkbook.Workbooks.Open INPUT_FILE

Set Controls = objWorkbook.CommandBars

For Each objControl in Controls

If Instr(objControl.Name, “Team”) Then

For Each objButton in objControl.Controls

If Instr(objButton.Tag, “IDC_REFRESH”) Then

objButton.Execute

End If

Next

End If

Next


RSS Information Week Headlines

  • An error has occurred; the feed is probably down. Try again later.

RSS SharePoint Team Blog

  • An error has occurred; the feed is probably down. Try again later.

RSS InfoPath Team Blog

  • An error has occurred; the feed is probably down. Try again later.

RSS Joel Oleson Blog

  • An error has occurred; the feed is probably down. Try again later.

RSS Susan Hanley’s KM Blog

  • An error has occurred; the feed is probably down. Try again later.

Blog Stats

  • 346,221 hits