Error using ListObject.Add to create a Table Style

Question

I'm Trying to create a Custom Table(Just like click the 'Format as Table' in the excel bar) with PowerShell and Excel ComObject from a CSV

Here's my code...

$Excel = New-Object -ComObject excel.application 
$Excel.visible = $true
$Excel.sheetsInNewWorkbook = $csvFiles.Count
$workbooks = $excel.Workbooks.Add()
$worksheets = $workbooks.worksheets
$CSVFullPath = C:\temp.csv
$worksheet = $worksheets.Item(1)
$worksheet.Name = "Temp"

$TxtConnector = ("TEXT;" + $CSVFullPath)
$CellRef = $worksheet.Range("A1")

$Connector = $worksheet.QueryTables.add($TxtConnector,$CellRef)
$worksheet.QueryTables.item($Connector.name).TextFileCommaDelimiter = $True
$worksheet.QueryTables.item($Connector.name).TextFileParseType  = 1
$worksheet.QueryTables.item($Connector.name).Refresh()
$worksheet.UsedRange.EntireColumn.AutoFit()

## So Far So good - CSV Imported ##
## My Problem Starts here... ##

$listObject = $worksheet.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, $worksheet.UsedRange, $null),[Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes,$null) 

## Then I Received the following error: ##

Exception calling "Add" with "5" argument(s): "A table cannot overlap a range that contains a PivotTable report, query
results, protected cells or another table."
At line:1 char:41
+ $ListObject = $WorkSheet.ListObjects.Add <<<< ([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange,$R
ange,$null,[Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes,$null)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation    

I'm have been on it for some time and not found a solution.

Answer 1

Based on your code, you cannot add a ListObject to the Worksheet with an underlying QueryTable still in place. If you try to do this in normal Excel (non-COM), you will get an error like:

If you hit Yes there and record a macro while it does its work, Excel just deletes the QueryTable and adds the ListObject. Deleting the QueryTable does not affect the underlying data.

In the VBA world, your code would look like this:

Sub DeleteQueryTableAndAddListObject()

    Dim sht As Worksheet
    Set sht = ActiveSheet

    ''code up here to create a QueryTable

    Dim i As Integer
    For i = sht.QueryTables.Count To 1 Step -1
        sht.QueryTables(i).Delete
    Next i

    sht.ListObjects.Add xlSrcRange, sht.UsedRange, , xlYes

End Sub

Taking a stab at PowerShell (not my native tongue) you should be able to do:

$worksheet.QueryTables.item($Connector.name).Delete()

or possibly:

$Connector.Delete()

since $Connector appears to be a valid reference to the QueryTable object.