使用ListObject.Add创建表样式时出错

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

我正在尝试使用PowerShell和Excel ComObject从CSV创建自定义表(就像在Excel工具栏中单击“格式为表格”一样)

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.

我已经有一段时间没有找到解决方案了。

1 个解决方案

#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:

根据您的代码,您无法将ListObject添加到工作表中,并且底层的QueryTable仍然存在。如果您尝试在普通Excel(非COM)中执行此操作,您将收到如下错误:

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.

如果你在那里点击是并在宏工作时记录它,Excel只删除QueryTable并添加ListObject。删除QueryTable不会影响基础数据。

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

在VBA世界中,您的代码如下所示:

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:

抓住PowerShell(不是我的母语)你应该能够做到:

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

or possibly:

$Connector.Delete()

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

因为$ Connector似乎是对QueryTable对象的有效引用。