如何以SELECT-> gt;的方式使用Excel VBA选择表的子集.从->在哪里 [英] How to select a subset of a table with Excel VBA in the manner of SELECT -> FROM -> WHERE

问题描述

抱歉，这是经典的新手问题，但我无法找到具体答案.

Apologies is this is the classic newbie question, but I haven't been able to find a concrete answer.

我对Excel VBA和一般编程是完全陌生的.我想做的是从一个大型excel表中选择一个数据子集，该子集的格式大致如下:

I'm entirely new to Excel VBA and to programming in general. What I'm trying to do is select a subset of data from a large excel sheet that's roughly in this format:

    Name          Data One     Data Two    Data N
    ----------------------------------------------------
    Person One       x            x          x
    Person One       x            x          x
    Person One       x            x          x
    Person Two       x            x          x
    Person Two       x            x          x
    Person Three     x            x          x

我将每隔几天有一张新表处理，但我不知道上面会列出多少人，或者每个人会有多少条目.

I will have a new sheet to deal withe every few days, but I won't know how many people will be listed on it or how many entries there will be for each person.

我的最终目标(目前是……)是获取第一人称的所有数据并将其复制到名为第一人称"的新工作表(在同一工作簿中)，将第二个人的所有数据复制到新工作表称为第二个人，依此类推.

My end goal (for now...) is to take all the data for Person One and copy that to a new sheet (in the same workbook) called Person One, copy all the data for Person Two to a new sheet called Person Two, and so on.

如果我对SQL的基本理解是正确的，我将能够以SELECT数据形式使用命令，该数据来自表WHERE Person = Person One.

If my rudimentary understanding of SQL is correct I would be able to use a command in the form of SELECT data FROM table WHERE Person = Person One.

但是，我无法在Excel中找到一种简单的方法来做到这一点.我可以看到有几种方法可以使用循环来遍历该行，并记录数据从人称一"更改为人称二"并定义这些范围的点.

However, I hacen't been able to find a simple way to do this in Excel. I can see there are ways that I could do it by using a loop to cycle through the row and note the point where the data change from 'Person One' to 'Person Two' and then define those ranges.

我还看到我可以使用自动筛选"功能选择所需的数据-我可以在第二张工作表中过滤第一列中的唯一记录，然后在自动筛选"设置中循环浏览每个人的姓名.

I also see that I can use the AutoFilter function to select the data that I want - I could filter the first column for unique records on a second sheet, then cycle through the names of each person in the AutoFilter setting.

在我看来，我可以使用循环遍历第一列中的条目，并在每个条目下方插入空白行，因此表选择功能将为每个人选择每个数据块.

It has also occurred to me I could use a loop to run through the entries in the first column and insert a blank row under each, so the table select function would select each chunk of data for each person.

但是，有没有更好的方法来完成我的任务?我本以为这种情况很普遍，但是要么不是，要么我在寻找错误的东西.

Is there a better way to accomplish my end though? I would have thought this scenario was pretty common, but either it isn't or I'm searching on the wrong things.

干杯！

斯蒂芬

推荐答案

您可以使用ADO:

Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String

strFile = ActiveWorkbook.FullName

''Note HDR=No, then F1,F2 etc is used for column names
''If HDR=Yes, the names in the first row of the range
''can be used.
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")


cn.Open strCon

'Case sensitive
strSQL = "SELECT [Name],[Data One],[Data Two],[Data N] FROM [Sheet1$] " _
       & "WHERE [Name]='Person 1'"


rs.Open strSQL, cn, 3, 3
Worksheets("Sheet2").Cells(2, 1).CopyFromRecordset rs

请注意，您可以选择不同的[名称]，并在结果记录集中循环以获取每个人的工作表.

Note that you could select distinct [name] and cycle through the resulting recordset to get a sheet for each person.

这篇关于如何以SELECT-> gt;的方式使用Excel VBA选择表的子集.从->在哪里的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！