How to change connection string for Pivots using vba

[Sidtha]

Hello All,

I have create below code to change or update the connection file using excel vba,
But its changing path in Connection file but not updating in Connection String.
Could some one please help me to solve this issue.

Sub Update_PivotConnection()

Application.StatusBar = "Macro is running...."

Application.ScreenUpdating = True
Application.DisplayAlerts = False

'Define main sheet
Dim MainSheet As Worksheet
Set MainSheet = ThisWorkbook.Worksheets("Main")

'Open the 6Months Backlog file from the Input folder
Dim In6MonthsPath As String
In6MonthsPath = MainSheet.Range("D3").Value
In6Months = Dir(MainSheet.Range("D3").Value & "* - Backlog _Older6M_SupportSNOW.xlsx")
Dim InBook6Months As Workbook
Set InBook6Months = Workbooks.Open(In6MonthsPath & In6Months)


Set InBook6MonthsSht = InBook6Months.Sheets("Data")
InBook6Months.Sheets("Data").Visible = True

InBook6MonthsSht.Activate

'Test the activesheet's protection
If InBook6MonthsSht.ProtectContents = True Then
'If protected
MsgBox InBook6MonthsSht.Name & " Is protected! Please Unprotect the sheet and save it, Run the macro", _
vbOKOnly + vbInformation, InBook6MonthsSht.Name & " is protected!"
Exit Sub
Else
'If not protected
' MsgBox "Hooray! " & InBook6MonthsSht.Name & " is not protected!", _
' vbOKOnly + vbInformation, InBook6MonthsSht.Name & " is unprotected!"
End If

'Open the RAWT file from the folder
Dim InRAWTPath As String
InRAWTPath = MainSheet.Range("D3").Value
InBRAWT = Dir(MainSheet.Range("D3").Value & "*-EPIM_Analisis_RAWT.xlsb")

'Data source path
DSource = InRAWTPath & InBRAWT

Set InBook6MonthsSht = InBook6Months.Sheets("Data")
InBook6MonthsSht.Activate

'Update pivot connections
cnt = InBook6Months.Connections.Count
Set conn = InBook6Months.Connections.Item(1)

InBook6Months.Connections.Item(1).Name = "EPIM_Analisis_RAWT"
InBook6Months.Connections.Item(1).Description = "Backlog$" '& MainSheet.Range("C23").Value
InBook6Months.Connections.Item(1).OLEDBConnection.CommandText = "Backlog$"
InBook6Months.Connections("EPIM_Analisis_RAWT").OLEDBConnection.SourceConnectionFile = DSource


'Refresh pivot tables
InBook6Months.Connections("EPIM_Analisis_RAWT").Refresh


MsgBox "Pivot Connections Update Done!!!!"

End Sub