Error while using ListObject.Add to create table style

Question

Error while using ListObject.Add to create table style

I am trying to create a custom table (same as clicking Format as Table in excel row) using PowerShell and Excel ComObject from 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've been on it for a while and haven't found a solution.

+3

powershell excel

Avshalom June 25. 15 at 12:59

source to share

1 answer

Byron wall · Accepted Answer · 2015-06-25T18:40:53+0000

Based on your code, you can't add ListObject

in Worksheet

with the base QueryTable

one still in place. If you try to do this in regular Excel (not COM), you will get an error:

enter image description here

If you click Yes

and record the macro while it does its job, Excel will just remove QueryTable

and add ListObject

. Deletion 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 hit in PowerShell (not my native language), you should be able to do:

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

or perhaps:

$Connector.Delete()

because it $Connector

appears to be a valid object reference QueryTable

.

Error while using ListObject.Add to create table style

More articles: