Styjun

I have an excel workbook consisting of a source data and a pivot table.
the source data gets updated with new records daily along with some changes in the existing data.

I have written a vbscript in notepad and saved it with .vbs and I am calling it from Rstudio to perform the refresh pivot table action.

But,when I run this script in Rstuido :

 pathofvbscript = ("D:\Users\703225799\WIP\R\pivot\r5.vbs")
 shell(shQuote(normalizePath(pathofvbscript)),"cscript",flag = 
 "//nologo")

I am getting the following error :

 D:Users703225799WIPRpivotr5.vbs(14, 1) Microsoft VBScript runtime 
 error: Object doesn't support this property or method: 'objWB.Range'

VBS code :

 '------------------------------------------------------------------------
 'Set Pivot Table & Source Worksheet
 '------------------------------------------------------------------------
 Set objExcel = CreateObject("Excel.Application")
 Set objWB = 
 objExcel.Workbooks.Open("D:Users703225799WIPRpivotNew 
 folderBook1.xlsx")
 Set Pivot_Sheet = objWB.Worksheets("pvt")

 '-----------------------------------------------------------------------
 'Enter in Pivot Table Name
 '-----------------------------------------------------------------------
 PivotName = "PivotTable1"
 objWB.Activate

 Set StartPoint = objWB.Range("A1")
 LastCol = StartPoint.End(xlToRight).Column
 DownCell = StartPoint.End(xlDown).Row
 Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))

 New_range = objWB.Name & "!" & DataRange.Address(xlR1C1)

 Pivot_Sheet.PivotTables(PivotName). _
 ChangePivotCache ActiveWorkbook. _
 PivotCaches.Create(xlDatabase,NewRange)

 '----------------------------------------------------------------------
 'Ensure Pivot Table is Refreshed
 '----------------------------------------------------------------------
 Pivot_Sheet.PivotTables(PivotName).RefreshTable

 'Data_Sheet.Save
 'MsgBox "Your Pivot Table is now saved."

 'Data_Sheet.Close
 'MsgBox "Your Pivot Table is now closed."

 'objExcel.Quit
 'MsgBox "objExcel quit."

 '----------------------------------------------------------------------
 'Complete Message
 '----------------------------------------------------------------------

 Pivot_Sheet.Activate
 MsgBox "Your Pivot Table is now updated."


 objWB.Save
 objWB.Close 

 set objExcel = Nothing
 set Data_Sheet = Nothing
 Set Pivot_Sheet = Nothing

Please help with the error.

Thanks,
Sayan

Please try using Pivot_Sheet.Range("A1") instead of objWB.Range("A1") as the worksheet object is Pivot_sheet