title: vbscript to dynamically update the excel cell range,when source data is updated and refresh the pivot table simultaneously
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>But,when I run this script in Rstuido :</p>
  
  <pre><code>  pathofvbscript = ("D:\\Users\\703225799\\WIP\\R\\pivot\\r5.vbs")
    shell(shQuote(normalizePath(pathofvbscript)),"cscript",flag = 
    "//nologo")
  </code></pre>
  
  <p>I am getting the following error :</p>
  
  <pre><code>   D:\Users\703225799\WIP\R\pivot\r5.vbs(14, 1) Microsoft VBScript runtime 
     error: Object doesn't support this property or method: 'objWB.Range'
  </code></pre>
  
  <p>VBS code :</p>
  
  <pre><code> '------------------------------------------------------------------------
   'Set Pivot Table &amp; Source Worksheet
   '------------------------------------------------------------------------
    Set objExcel    = CreateObject("Excel.Application")
    Set objWB       = 
    objExcel.Workbooks.Open("D:\Users\703225799\WIP\R\pivot\New 
    folder\Book1.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 &amp; "!" &amp; 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
  </code></pre>
  
  <p>Please help with the error.</p>
  
  <p>Thanks,
  Sayan</p>