如何在将多个工作簿合并到一个工作簿时添加一些查找数据 [英] how to add some lookup data while merging multiple workbooks into one

问题描述

我现在有一个程序，该程序可以合并多个工作簿中的特定工作表.该程序必须处理大约300个工作簿并创建一个合并的工作簿.在合并的工作簿中，我必须向从其他工作簿复制的每一行中添加更多数据点.目标工作簿中的行数可能约为100,000.

I have a program now which merges a particular worksheet from multiple workbooks. This program has to process around 300 workbooks and create a consolidated workbook. In the consolidated workbook, I have to add a few more data points to each of the rows copied from other workbooks. The number of rows in the target workbook is likely be around 100,000.

我还有另一个XL，其中包含许多其他与客户有关的数据，例如姓名，国家/地区，货币等，我必须将其添加为其他列.

I have another XL which contains many other data related to a customer like the name, country, currency, etc which I have to add as other columns.

一个工作簿仅包含来自一位客户的数据.因此，对于从一个工作簿复制的数据，所有这些列的数据将基本相同.

One workbook contains data from one customer only. So, for the data copied from one workbook, the data for all these columns will essentially be the same.

现在，我正在考虑打开customerData工作簿，并根据customerID有选择地复制数据，然后在粘贴每个工作表中的数据时将其粘贴到目标工作簿中.

Right now, I am thinking of opening the customerData workbook and copying the data selectively based on customerID and pasting it into the target workbook when I paste the data from each worksheet.

现在代码将像这样:

For each workbook in the source Folder
    Open source workbook
    Copy the range from worksheet1
    Copy the customerID from worksheet2
    Open target workbook
    Paste range from source.worksheet1 in the target worksheet
    Fill the range in the target worksheet with the first column as customerID
    Open workbook with customer master data
    Copy data based on customerID
    Paste in the target worksheet using fill range

另一种方法是完成粘贴所有工作表中的所有数据，然后查找客户数据-这样做的好处是可以为客户提供多个文件中的数据，因此在避免多个数据方面有一定的效率查找.客户主数据文件仅需打开一次.

Other approach is to finish pasting all the data from all the worksheets and then do the lookup of the customer data - the advantage here is that there can be data for the customer in multiple files, hence there is some efficiency in avoiding multiple lookups. And the customer master data file has to be opened only once.

我想知道我采用的方法是否正确，是否可以做任何改进.

I would like to know whether the approach I have taken is right and whether there is any improvement that I can do it.

具体来说，我有以下问题:

Specifically, I have the following questions:

• 是否有避免打开每个源文件的方法?
• 将源工作簿中的所有数据添加到内存中的数组并将其复制一次到目标工作簿中是否更好?
• 如果我采用数组方法，应该在粘贴之前将查找添加到数组中还是在粘贴目标范围内的数据之后执行查找?

推荐答案

我将其写为回复，因为它可能太长，无法发表评论.

I write this as a reply because it may be too long as a comment.

1)无法避免打开源文件来检索数据.对于这样的操作，非常重要的是，对于每个要打开以供检索的工作簿，都创建一个新的Excel实例(CreateObject方法)，并在完成后通过应用.Quit方法将其完全从内存中清除.Workbook.close并非如您所期望的那样完美无缺(不会完美地清除内存)，并且您尝试在一个循环中打开/关闭的工作簿的数量越多，您的应用程序可能在某个时刻崩溃/冻结的机会就越高.观点.

1) There is no way to avoid opening the source files to retrieve data. For operations like this, it is very important that for every workbook you open for retrieval, you create a new instance of Excel (CreateObject method) and clear it entirely from memory when finished by applying the .Quit method. Workbook.close is not flawless as you would expect (the memory is not cleared flawessly), and the higher the number of workbooks you try to open / close in one loop, the higher the chance that your application may crash/freeze at a certain point.

2)读/写操作花费的时间最多，因此确实建议您尝试将它们的频率降低到最小.实际上，最好的办法是一次将每个检索到的工作簿的所有数据读取到一个范围(和数组)中，并在内存中执行所有操作.执行所需的操作后，可以将检索到的数组写入单个范围.
您可以按照您的建议一次将所有工作簿加载到一个数组中(据我所知，数组的大小仅受可用内存量的限制.出于实用性的考虑，我建议您编写每次关闭文件时，都会将数据存储到目标工作簿中.如果发生意外情况，则无需重新运行整个代码，但是可以将for next循环设置为上次执行的位置.

2) Reading / Writing operations take the most time, so it is indeed advised that you try to reduce their frequency to a minimum. The best is indeed to read all data per retrieved workbook at once into a single range (and array) and perform all operations in memory. You can write the retrieved array to a single range when you have performed the operations you wanted to do.
You can load all the workbooks at once in an array like you suggest (as far as I know, the size of an array is only limited to the amount of available memory. For reasons of being practical, I would suggest though that you write the data to the target workbook every time you close the file. If something unexpected happens, you don't need to re-run the entire code, but you can set the for next loop as from where you were the last time.

3)如果要防止每次遇到错误时应用程序都停止运行(当您检索到此数据量时很可能会出现这种情况)，请使用

3) If you want to prevent that the application stops every time a bug is met (which is highly likely when you retrieve this amount of data), use

On Error goto ErrHandler
Err.Handler: 
    'Display error number / Description + place in logfile 
    Resume next 

建议您创建一个小的错误处理程序，在单独的日志"文件中写入错误代码，描述和错误发生的位置，以便以后可以跟踪错误.但是通过这种方式，您可以在代码运行时喝咖啡.

It is advised that you create a small error handler that writes the error code, description, and the place where the error occurred in a separate "log" file, so you can track the mistakes afterwards. But this way you can get your coffee while the code runs.

4)至于查找，我不确定我是否正确理解了您尝试执行的操作.最快的查找自然是循环最短的查找.嵌套循环通常相当昂贵，肯定是很长的时间.
我只会提到(就像我通常一样)，建议将来将MS Access用于用户数据库(或任何其他RDBMS)(当我听到客户ID"时，这是我首先想到的).性能方面的主要优点是您可以通过应用索引直接访问记录.使用简单的SQL语句很容易进行查询，这比必须使用VBA自己编写所有程序并遍历数组的情况要容易得多.SQL语句可以用于执行常规的CRUD(创建，检索，更新，删除记录)操作，但还可以执行更多操作.此外，您可以应用VBA来完成所有其他转换.

4) As for the lookup, I am not certain that I correctly understand what you try to do. The lookup that is the fastest is naturally the one that has the shortest loop. Nested loop are usually rather expensive, certainly when they are long.
I will just mention (like I usually do) that it is advised to use MS Access for user db's (or any other RDBMS) in the future (when I hear "customer ID", this is the first thing I think about). The main advantage in the field of performance is that you can access records directly by applying indexes. It is easy to query using simple SQL statements, much easier than when you have to program everything yourself with VBA and loop through arrays. SQL Statements can be used to perform general CRUD (Create, Retrieve, Update, Delete records) operations, but also much more. Additionally you can apply VBA to take care of any additional transformations.

我希望我的答复对您有所帮助，尽管我没有提供任何实际代码.

I hope my reply was of any help to you, although I didn't offer any real code.

这篇关于如何在将多个工作簿合并到一个工作簿时添加一些查找数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！