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

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


  1. 1 Jackson June 25, 2014 at 4:21 am

    Hi
    I’m getting the error” Object required” when executing the above code. The error is ming from the below line. Please assist
    Set objWorkbook = CreateObject(“Excel.Application”)

    • 2 andrei338 June 25, 2014 at 4:48 am

      Did you replace the “your_file_path” with the correct path? It needs to include the directory path and file name.

      Const INPUT_FILE = “your_file_path”

  2. 3 Shobin December 9, 2014 at 1:44 pm

    Hi,
    While refreshing, normally a popup message asks for confirmation. Can we handle that also in excel ?

  3. 4 bellevueboy May 9, 2015 at 8:26 pm

    I was looking for this exact code but the script does not see my xls file. When I modify it for a text file I do not get that error but of course the refresh fails as its not really applicable. Please help I desperately need this to work.

  4. 5 andrei338 May 10, 2015 at 10:26 pm

    Make sure that you modify the following line in the script with the correct path to your file:

    Const INPUT_FILE = “your_file_path”

    replace “your file path” with something like “C:\myfile.xls”

    • 6 bellevueboy May 11, 2015 at 12:18 am

      Sorry I should have replied when I got the solution yesterday. It was my mistake, it was resolved when I changed the file name to .xlsx
      Thank you for your code. It’s going to help me a lot.


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 Ben Curry’s Blog

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

RSS Mauro Cardarelli’s 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.

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

  • 300,271 hits

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: