Run-time error 1004 Table Cannot Overlap A Range...


  • Hi,

    I used the macro recorder in Excel 2010 to record the code below. The code is supposed to use a .iqy to pull data from a SharePoint 2010 site. When I run the macro, I receive the following error: A table cannot overlap a range that contains a PivotTable report, query results, protected cells or another table. I have no issue running the .iqy query from excel without using the macro. Any idea what's causing the 1004 error?

    Sub Macro1()
    ' Macro1 Macro
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
            "OLEDB;Provider=Microsoft.Office.List.OLEDB.2.0;", Destination:=Range("$A$1") _
            .CommandType = 5
            .CommandText = Array( _
            "<LIST><VIEWGUID>{50A58D53-347D-4E68-B9BC-CA834F7A068E}</VIEWGUID><LISTNAME>{A486016E-80B2-44C3-8B4A-8394574B9430}</" _
            , _
            "LISTNAME><LISTWEB>http://pwd-gsops5/_vti_bin</LISTWEB><LISTSUBWEB></LISTSUBWEB><ROOTFOLDER>/Lists/Projects List</RO" _
            , "OTFOLDER></LIST>")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .SourceConnectionFile = "C:\Users\shress2\Downloads\owssvr.iqy"
            .ListObject.DisplayName = "Table_owssvr1"
            .Refresh BackgroundQuery:=False
        End With
        ChDir "C:\Users\shress2\Downloads"
        ActiveWorkbook.SaveAs Filename:="C:\Users\shress2\Downloads\Book1.xlsm", _
            FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    End Sub

    Any help/suggestion is greatly appreciated. Thanks in adv!
    Thursday, May 10, 2012 3:43 PM

All replies