VBA to refresh connections in Excel.

In this code, you can pass parameters to the connection and refresh it, the pivot table in the excel will get updated. It is very useful when developing the excel reports.

 

Sub ToRefreshPivot() Dim Alias As String 'Alias = Application.UserName Alias = Environ("USERNAME") With ActiveWorkbook.Connections( _ "MarketSharePivot"). _ OLEDBConnection .BackgroundQuery = True .CommandText = Array("exec GetMarketSharePivot" & " '" + Alias + "'") .CommandType = xlCmdSql .Connection = Array( _ "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=MarketShareToolFY11;Data Source=TK5SDATWeb02;Us" _ , _ "e Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=NARESH-MSFT;Use Encryption for Data=False;Tag with" _ , " column collation when possible=False") .RefreshOnFileOpen = False .SavePassword = False ' .SourceConnectionFile = "" ' .SourceDataFile = "" .ServerCredentialsMethod = xlCredentialsMethodIntegrated .AlwaysUseConnectionFile = False End With ActiveWorkbook.Connections( _ "MarketSharePivot").Refresh End Sub