I have a requirement to update data source of the pivot in a workbook. Challenge is, I cannot create a macro to update the Workbook is downloaded from one of the data source. I have tried below code and getting 'Exception from HRESULT: 0x80070057 (E_INVALIDARG)' error.
Const xlDatabase as Integer = 1
Const xlR1C1 as Integer = -4150
Dim wb as Object = GetWorkbook(handle, workbook)
Dim ws as Object = wb.Worksheets(worksheet)
Dim newDataSource
Try
newDataSource = wb.PivotCaches.Add (SourceType:=xlDatabase, _
SourceData:= "[Sheet1]" & ws.Range(newLink).Address(ReferenceStyle:=xlR1C1))
Dim pt = ws.PivotTables("PivotTable1")
pt.ChangePivotCache (newDataSource)
success = True
Catch e As Exception
success = False
Message = e.Message
End Try
Anyone tried this scenario? Replies are appreciated. 🙂
