使用ListObject.Add创建表样式时出错 [英] Error using ListObject.Add to create a Table Style
问题描述
我正在尝试使用CSV格式的PowerShell和Excel ComObject创建自定义表(就像单击Excel栏中的将格式设置为表一样")
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
这是我的代码...
$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.
推荐答案
根据您的代码,您不能在基础QueryTable
仍然存在的情况下向Worksheet
添加ListObject
.如果您尝试在普通Excel(非COM)中执行此操作,则会收到类似以下错误:
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:
如果在其中单击Yes
并记录宏,宏就会起作用,Excel只会删除QueryTable
并添加ListObject
.删除QueryTable
不会影响基础数据.
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.
在VBA世界中,您的代码如下所示:
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
以PowerShell(不是我的母语)为目标,您应该能够做到:
Taking a stab at PowerShell (not my native tongue) you should be able to do:
$worksheet.QueryTables.item($Connector.name).Delete()
或可能:
$Connector.Delete()
因为$Connector
似乎是对QueryTable
对象的有效引用.
since $Connector
appears to be a valid reference to the QueryTable
object.
这篇关于使用ListObject.Add创建表样式时出错的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!