创建VBA宏以提取匹配的数据 [英] Creating a VBA Macro to Extract Data for matches

查看：172 发布时间：2017/9/9 19:58:08 excel data-extraction

本文介绍了创建VBA宏以提取匹配的数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在寻找如何在Excel中构建VBA宏（我从未创建过一个）的指导，它将在两个单独的excel文件中查找唯一的标识符匹配，然后提取匹配的伴随行数据。

更简单地说：

我有两个独立的excel文件，它们每个都有一列存在于其中，用于唯一的标识符。

我想让VBA宏找到其中唯一标识符与另一个文件中相同的文件之一中的匹配项。

在Excel文件中找到匹配项后，我想提取匹配所在行的数据。

理想情况下，我希望将提取的数据放入新的excel工作表中。

解决方案

这是一个例子，将导致你做你想要的去做。以下是您必须采取或考虑的步骤：

启用开发人员工具

在VBA中创建一个模块

将一些常量添加到模块顶部

创建代码，以检查工作簿是否打开;

- 如果不是，打开它

- 如果工作簿不存在，创建它并打开它

创建将使用上述代码打开一本或多本图书的代码

创建将循环访问文件1中的行，检入文件2以及匹配位置的代码发现写入文件3

在Excel中启用开发人员工具

使用本文： https://msdn.microsoft.com /en-us/library/bb608625.aspx

打开第一个文件。然后创建一个模块，下一个主题显示

创建模块

使用此文章并按照步骤3 - 创建模块： https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/

创建常量

编写下面的代码来声明文件名和工作表名称的常量。

  Const FIRST_FILE_NAME As String =Book1.xlsx'当前文件
 Const SECOND_FILE_NAME As String =Book2.xlsx
 Const RESULTANT_FILE_NAME As String =Result.xlsx
 
 Const wstFirst As String =Sheet1'第一个文件的工作表名称
 Const wstSecond As String =Sheet1
 Const wstResultant As String =Sheet1

创建将检查工作簿是否打开的代码

将此代码写入常量声明代码
'检查工作簿是否打开;如果无效，创建一个函数Isworkbookopen（FileName As String） Dim ff As Long，ErrNo As Long Dim wkb As Workbook Dim nam As String wbname = FileName 错误恢复下一步 ff = FreeFile（）打开FileName输入锁定为#ff 关闭ff ErrNo = Err 错误GoTo 0 选择案例ErrNo 案例0：Isworkbookopen = False 案例70：Isworkbookopen = True 案例53： Workbooks.Add ActiveWorkbook.SaveAs FileName：= RESULTANT_FILE_NAME Isworkbookopen = False 案例：错误ErrNo 结束选择结束函数
如果文件不存在，创建一个新文件并报告该文件未打开。我刚刚使用了Dan Wagner的代码，在 VBA宏将数据从一个excel文件复制到另一个答案。其余的代码也是Dan的代码修改。

创建打开书籍的代码

在下面的代码的下面写下这段代码。此代码将获取文件名和引用变量。如果工作簿未打开，请打开它并将其分配给引用变量。您将不得不自己在 ByRef 上进行阅读。
'打开一个工作簿并传递引用 Private Sub OpenBook（FileName As String，ByRef wkb As Workbook） ret = Isworkbookopen（FileName）如果ret = False然后设置wkb = Workbooks.Open（FileName） Else 设置wkb =工作簿（FileName）如果结束Sub
创建将循环并在结果文件中插入数据的代码

编写此代码在你当前的代码的底部。此代码将打开所有3本书（第一本书，第二本书及其结果书，其中将粘贴数据）。第一个文件逐行读取（假设第一个和第二个文件之间的公共字段只读取第一个单元格）。然后，文件1
Sub copydata（） Dim wkbFirst As Workbook Dim wkbSecond作为工作簿 Dim wkbResultant作为工作簿 '打开所有3个文件 OpenBook FIRST_FILE_NAME，wkbFirst OpenBook SECOND_FILE_NAME，wkbSecond OpenBook RESULTANT_FILE_NAME，wkbResultant Dim First_File_Counter As Integer，Second_File_Counter As Integer，Resultant_File_Counter As Integer Dim First_Value As String，Second_Value As String Resultant_File_Counter = 1 '循环从第1行到第文件＃1的大量对于First_File_Counter = 1到10000 '获取A1的值，然后在每个循环中的A2等等 '如果该单元格没有一个值，假设没有更多的数据行 First_Value = wkbFirst.Worksheets（wstFirst）.Range（A& First_File_Counter）.Value 如果IsNull（First_Value）或Len（Trim （FIRST_VALUE）） = 0然后退出对于 '从第1行到文件＃2 '的循环，并查找从文件＃2中的文件＃1获取的信息对于Second_File_Counter = 1到10000 Second_Value = wkbSecond.Worksheets（wstSecond）.Range（A& SecondFile_Counter）.Value 如果IsNull（Second_Value）或Len（Trim（Second_Value））= 0，则退出对于 '如果第一个文件的A1匹配此第二个文件中的任何行 '将第一个文件中的行复制到结果文件如果First_Value = Second_Value然后 wkbFirst.Worksheets（wstFirst）.Rows（First_File_Counter）.EntireRow.Copy wkbResultant.Worksheets（wstResultant ）.Rows（Resultant_File_Counter）。选择 wkbResultant.Worksheets（wstResultant）.Paste Resultant_File_Counter = Resultant_File_Counter + 1 退出结束如果下一个 Next End Sub
示例 p>

我创建了Book1.xlsx。我有：
AB ----- -------- 1 UID名称 2 1 John 3 2 Matt 4 3 Katie
Book2.xlsx有
AB ----- -------- 1 UID地址 2 1 100芝加哥第二大街 3 3休斯顿卢卡斯塔
当我点击任何一行的copycode并按 F5 时，将会运行copycode子程序。它将通过代码，然后生成的文件将如下所示：
AB ----- -------- 1 UID名称 2 1 John 3 3 Katie
请注意，文件1中的数据到文件3，但只有文件2中具有匹配的UID的那些行。从文件1的Matt的行没有转到结果文件，因为文件2没有UID 2。

希望这会让你说出来。

I am looking for guidance on how to build a VBA Macro in Excel (I have never created one before) that would look for a unique identifier match in two separate excel files and then would extract the accompanying row data for the match.

Put more plainly:

I have two separate excel files, they each have a column present in them for a unique identifier.

I want the VBA Macro to find the matches in one of the files where the unique identifier is the same as it is in the other file.

Once a match is found in the Excel file, I want to extract the data for that specific row where the match is found.

Ideally, I would like the extracted data to be put into a new excel worksheet.

解决方案
This is an example that will lead you to doing what you want to do. Here're the steps you will have to take or think about:

Enable developer tools

Create a module in VBA

Add some constants to the top of the module

Create code that will check if a workbook is open;

-- if it is not, open it

-- if workbook does not exist, create it and open it

Create code that will use the above code to open one or more books

Create code that will loop through rows in file 1, check in file 2 and where a match is found writes to file 3

Enable developer tools in Excel

Use this article: https://msdn.microsoft.com/en-us/library/bb608625.aspx

Open the first file. Then create a module as the next topic shows

Create module

Use this article and follow it till step 3 - create module: https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/

Create constants

Write the code below to declare constants of file names and sheet names.
Const FIRST_FILE_NAME As String = "Book1.xlsx" ' This current file Const SECOND_FILE_NAME As String = "Book2.xlsx" Const RESULTANT_FILE_NAME As String = "Result.xlsx" Const wstFirst As String = "Sheet1" ' Sheet name of first file Const wstSecond As String = "Sheet1" Const wstResultant As String = "Sheet1"
Create code that will check if workbook is open

Write this code below the constant declaration code
' Check if a workbook is open; if inexistant, create one Function Isworkbookopen(FileName As String) Dim ff As Long, ErrNo As Long Dim wkb As Workbook Dim nam As String wbname = FileName On Error Resume Next ff = FreeFile() Open FileName For Input Lock Read As #ff Close ff ErrNo = Err On Error GoTo 0 Select Case ErrNo Case 0: Isworkbookopen = False Case 70: Isworkbookopen = True Case 53: Workbooks.Add ActiveWorkbook.SaveAs FileName:=RESULTANT_FILE_NAME Isworkbookopen = False Case Else: Error ErrNo End Select End Function
In case where a file does not exist, create a new file and report that the file is not open. I just used the code by Dan Wagner in VBA macro to copy data from one excel file to another answer. Rest of the code is a modification of Dan's code also.

Create code that will open a book

Write this code next below your rest of the code. This code will take a file name and a reference variable. If workbook is not open, open it and assign it to reference variable. You will have to do some reading on ByRef yourself.
' Open a workbook and pass the reference back Private Sub OpenBook(FileName As String, ByRef wkb As Workbook) ret = Isworkbookopen(FileName) If ret = False Then Set wkb = Workbooks.Open(FileName) Else Set wkb = Workbooks(FileName) End If End Sub
Create code that will do looping and insert data in resultant file

Write this code at the bottom of your current code. This code will open all 3 books (First book, second book and resultant book in which data will be pasted). First file is read row by row (only 1st cell is read assuming that is the common field between first and second file). Then, file 1
Sub copydata() Dim wkbFirst As Workbook Dim wkbSecond As Workbook Dim wkbResultant As Workbook ' open all 3 files OpenBook FIRST_FILE_NAME, wkbFirst OpenBook SECOND_FILE_NAME, wkbSecond OpenBook RESULTANT_FILE_NAME, wkbResultant Dim First_File_Counter As Integer, Second_File_Counter As Integer, Resultant_File_Counter As Integer Dim First_Value As String, Second_Value As String Resultant_File_Counter = 1 ' loop from row 1 to a large number for file #1 For First_File_Counter = 1 To 10000 ' get value of A1, then A2 and so on during each loop ' if that cell does not have a value, assume that there're no more rows of data First_Value = wkbFirst.Worksheets(wstFirst).Range("A" & First_File_Counter).Value If IsNull(First_Value) Or Len(Trim(First_Value)) = 0 Then Exit For ' loop from row 1 to a large number for file #2 ' and look up information obtained from file #1 in file #2 For Second_File_Counter = 1 To 10000 Second_Value = wkbSecond.Worksheets(wstSecond).Range("A" & Second_File_Counter).Value If IsNull(Second_Value) Or Len(Trim(Second_Value)) = 0 Then Exit For ' if first file's A1 matches any of the rows in this second file ' copy the row from first file into the resultant file If First_Value = Second_Value Then wkbFirst.Worksheets(wstFirst).Rows(First_File_Counter).EntireRow.Copy wkbResultant.Worksheets(wstResultant).Rows(Resultant_File_Counter).Select wkbResultant.Worksheets(wstResultant).Paste Resultant_File_Counter = Resultant_File_Counter + 1 Exit For End If Next Next End Sub
Example

I created Book1.xlsx. In that I have:
A B ----- -------- 1 UID Name 2 1 John 3 2 Matt 4 3 Katie
Book2.xlsx has
A B ----- -------- 1 UID Address 2 1 100 2nd St, Chicago 3 3 Lukas Tower, Houston
When I click on any line of copycode and press F5, copycode subroutine will run. It will go through the code and then the resultant file will look like this:
A B ----- -------- 1 UID Name 2 1 John 3 3 Katie
Note that data from file 1 went to file 3 but only those rows that had matching UID in file 2 did. Matt's row from file 1 did not go to resultant file because file 2 does not have UID 2.

Hopefully that will get you stated.

这篇关于创建VBA宏以提取匹配的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

创建VBA宏以提取匹配的数据 [英] Creating a VBA Macro to Extract Data for matches

问题描述

相关文章

Office最新文章

热门教程

热门工具

登录关闭

创建VBA宏以提取匹配的数据 [英] Creating a VBA Macro to Extract Data for matches

问题描述

相关文章

Office最新文章

热门教程

热门工具

登录 关闭

登录关闭