title: How can I programmatically redefine a Pivot Table datasource in VBA
body: <p>Using 'CopyFromRecordset' has been an effective way to keep a data source from MS Access to a pivot table- 'unlinked'.   </p>
  
  <p>The Access database produces the report which is independent and can be e-mailed with no macros.</p>
  
  <p>I'm trying to write a routine that will automatically redefine the  datasource for the pivot table.  I know enough to clean up a recorded macro, so that it doesn't have stray Excel objects left open, but sometimes I miss something that either leaves an object open - OR doesn't set the object right.</p>
  
  <p>In this particular case, it is on this line:</p>
  
  <pre><code>xlSheet.ListObjects.Add(xlSrcRange, xlSheet.Range(rngWorkingRange), , xlYes).Name _
          = "tbl_MOSI"
  </code></pre>
  
  <p>I get Run-time error '1004'
  Object defined error</p>
  
  <p>Here is my complete routine for reference:</p>
  
  <pre><code>Public Sub GetMOSIData(strSheetToPlaceData As String, strPathToWorkbook As String)
  
  'Puts a recordset into a specific cell of an Excel workbook 
  
  
  '''
  '8 objects created
  Dim xlApp As Object
  Dim WB As Object
  Dim xlSheet As Object
  Dim xlMain As Object
  Dim xlOther As Object
  Dim fld As Variant
  Dim rs As DAO.Recordset
  Dim rngWorkingRange As Range
  
  
  Dim x As Integer
  
  
  '''
  '6 objects set
  Set xlApp = CreateObject("Excel.Application")
  Set WB = xlApp.Workbooks.Open(strPathToWorkbook)
  
  Set xlSheet = WB.Sheets(strSheetToPlaceData) 'or you can manually type the sheet name in place of strSheetToPlaceData
  Set xlMain = WB.Sheets("MOSI")
  Set xlOther = WB.Sheets("SKL-LV-View")
  
  Set rs = CurrentDb.OpenRecordset("Select * from " &amp; strSheetToPlaceData)
  
  'Assumes you will use A1 as the anchor cell - but it can be changed
  
  'FIRST CLEAR THE RANGE OF CELLS
  xlSheet.Range("A1").CurrentRegion.ClearContents
  
  'PLACE THE FIELD NAMES
  x = 1
  For Each fld In rs.Fields
      xlSheet.Cells(1, x).Value = fld.Name
      x = x + 1
  Next
  
  'PLACE
  xlSheet.Range("A2").CopyFromRecordset rs
  
  'determine the data range for the PivotTable
  
  
  'RECORDING FOR REFERENCE - I DON'T USE THIS CODE
  '
  '    Sheets("tbl_MOSI_Detail").Select
  '    Range("C4").Select
  '    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$AB$23411"), , xlYes).Name _
  '        = "Table1"
  '    Range("Table1[#All]").Select
  '    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight9"
  '    ActiveSheet.ListObjects("Table1").Name = "tbl_MOSI"
  '    Sheets("MOSI").Select
  '    Range("G4").Select
  '    ActiveSheet.PivotTables("pvMOSI").ChangePivotCache ActiveWorkbook.PivotCaches. _
  '        Create(SourceType:=xlDatabase, SourceData:="tbl_MOSI_Detail!tbl_MOSI", _
  '        Version:=xlPivotTableVersion12)
  'END RECORDING FOR REFERENCE
  
  'ATTEMPT to clean up recording...
  '7th object set
  Set rngWorkingRange = xlSheet.Range("A1").CurrentRegion
  
  'convert tbl_MOSI_Detail Range into a Table
  
      xlSheet.ListObjects.Add(xlSrcRange, xlSheet.Range(rngWorkingRange), , xlYes).Name _
          = "tbl_MOSI"
      xlSheet.ListObjects("tbl_MOSI").TableStyle = "TableStyleLight9"
  
  'set pvMOSI to point to this table
      xlMain.PivotTables("pvMOSI").ChangePivotCache WB.PivotCaches. _
          Create(SourceType:=xlDatabase, SourceData:="tbl_MOSI_Detail!tbl_MOSI", _
          Version:=xlPivotTableVersion12)
  
  xlMain.RefreshAll
  xlMain.Select
  
  WB.Save
  WB.Close
  
  xlApp.Quit
  
  '8 objects destroyed
  Set rs = Nothing
  Set rngWorkingRange = Nothing
  Set fld = Nothing
  Set xlSheet = Nothing
  Set xlMain = Nothing
  Set xlOther = Nothing
  Set WB = Nothing
  Set xlApp = Nothing
  
  
  PresentExcel (strPathToWorkbook)
  MsgBox "MOSI report is complete", vbOKOnly, "MOSI"
  
  
  End Sub
  </code></pre>