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

About these ads

0 Responses to “Refreshing TFS list in Excel using VBscript”



  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




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 Joel Oleson Blog

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

RSS Ben Curry’s Blog

  • I'm at the ATL users group Monday night, Nov 16th November 15, 2009
  • Upgrading to SharePoint Server 2010 November 3, 2009

RSS Mauro Cardarelli’s Blog

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

RSS SharePoint PS Blog

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

RSS Lori Gowin’s Blog

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

RSS Greg Kamer’s Blog

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

Blog Stats

  • 239,772 hits

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: