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”
Set objWorkbook = CreateObject(“Excel.Application”)
objWorkbook.Visible = True
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