Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations waross on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to change connection string for Pivots using vba

Status
Not open for further replies.

Sidtha

Aerospace
Nov 25, 2012
30
IN
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.


Capture_xljyrh.jpg

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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top