Excel VBA获取和使用表的名称

I got stuck at a problem with Excel VBA.

我遇到了Excel VBA的问题。

I am supposed to do the kinda easy task of copy/paste a variable range of cells from "sheet2" into the same range in "sheet1".

我应该做一个简单的任务,从“sheet2”复制/粘贴可变范围的单元格到“sheet1”中的相同范围。

500 Rows like in my code is far too much, but I tried it this way, to "catch" the variable aspect.

像我的代码中的500行太多,但我尝试这种方式,以“捕捉”变量方面。

The tricky part is, that the range in "sheet1" is a table(which gets created from TFS).

棘手的部分是,“sheet1”中的范围是一个表(从TFS创建)。

Sub CopyP()


Sheets("Sheet1").Range("B3:F500").Value = Sheets("Sheet2").Range("B3:F500").Value

SheetObject.ListObjects (ListObjectName)
Range("NAME OF TABLE[Iteration Path]").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

The [Iteration Path] is a column name of my table, i want to check in this column/with this parameter, if the "row" is empty.

[Iteration Path]是我的表的列名,如果“row”为空,我想用此参数检查此列/。

I know the code is far from being good or clean but the table is giving me a hard time copying.

我知道代码远不是好的或干净的,但桌子让我很难复制。

With this code I got another problem: the table gets created from TFS, no problem to copy into that, BUT the name of the table is variable(seems like TFS creates the name), so unless I put the name manually in the code, the "program" cant execute, because of missing range.

使用此代码我遇到了另一个问题:表是从TFS创建的,没有问题可以复制到那里,但是表的名称是可变的(好像TFS创建了名称),所以除非我在代码中手动输入名称,由于缺少范围,“程序”无法执行。

Didn't find a way to get a return of the table name somehow.

没有办法以某种方式获得表名的返回。

But I think I am just following the wrong path overall, maybe someone can be bring me on the right track.

但我认为我只是走错了道路,也许有人可以带我走上正轨。

My other Idea is to Iterate through the Rows in Sheet 2 to fetch just as much data is needed and then copy them with an iteration into the table. But i guess I would be the same problem with the table-name there.

我的另一个想法是迭代第2页中的Rows以获取所需数据,然后将迭代复制到表中。但我想我会在那里使用表名同样的问题。

Every information I find using google , talks about tables where the user can "name" the table. In my case I cant, so I have to work with the name TFS uses for my table.

我使用谷歌找到的每一条信息,都会谈到用户可以“命名”表格的表格。在我的情况下,我不能,所以我必须使用TFS用于我的表的名称。

1 个解决方案

#1

Further to my comments below your question, I just typed this in notepad. Please amend it to suit your need.

继我在你的问题下面的评论之后,我只是在记事本中输入了这个。请根据您的需要进行修改。

This will give you the names of all tables in the activesheet. If there are multiple tables then you will get multiple names. (UNTESTED AS POSTING FROM PHONE)

这将为您提供活动表中所有表的名称。如果有多个表,那么您将获得多个名称。 (未通过电话发布)

Sub sample()
    Dim objLB As ListObject, TableName As String

    For Each objLB In ActiveSheet.ListObjects
        TableName = objLB.Name
        Exit For
    Next

    Range(TableName & "[Iteration Path]").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

#1