excel - 使用从文本文件导入的范围创建表

Question

我正在将一个文本文件导入 Excel，然后尝试将该范围转换为表格。

当您尝试手动创建表时，您会收到提示:

Your selection overlaps one or more external data ranges. Do you want to convert the selection to a table and remove all external connecitons?

如果您选择是，它会起作用。

如果您尝试从名称管理器中删除命名范围，那么当您获得范围选择框并单击“确定”时，没有任何 react ，没有错误，该框不会消失，没有任何 react 。删除连接也不能解决这个问题，但命名范围似乎是实际导致它的原因。

当您尝试通过代码创建表时，您将获得:

error 1004: A table cannot overlap a range that contains a PivotTable report, query results, protected cells or another table.

Sub TestNameConnDelete()
    Dim item As Variant
    For Each item In ActiveWorkbook.names
        item.Delete
    Next item

    For Each item In ActiveWorkbook.Connections
        item.Delete
    Next item
End Sub

Sub TestTableCreate()
    Dim lastrow As Long
    Dim lastcol As Long

    With ActiveSheet
        lastrow = .Cells(.rows.count, 1).End(xlUp).row
        lastcol = .Cells(1, .Columns.count).End(xlToLeft).Column

        .Range(.Cells(1, 1), .Cells(lastrow, lastcol)).Locked = False
        .ListObjects.Add xlSrcRange, .Range(.Cells(1, 1), .Cells(lastrow, lastcol)), , xlYes
    End With
End Sub

我找到的唯一解决方案是将整个内容作为值复制到另一张表中并在那里制作表格。我尝试了多个文本文件，它们都导致了问题。

Answer 1

看起来您需要使用 QueryTable ，类似的东西

ActiveSheet.QueryTables(1).Delete

可能是来自 QueryTable.ListObject 的有用片段文档:

If you import data by using the user interface, data from a web query or a text query is imported as a QueryTable object, while all other external data is imported as a ListObject object.

If you import data by using the object model, data from a web query or a text query must be imported as a QueryTable, while all other external data can be imported as either a ListObject or a QueryTable.

因此，对于文本查询，您似乎坚持删除 QueryTable ，然后创建 ListObject正如你目前正在做的那样。