在Excel工作表中插入10000多行

I am creating an Excel macro while pulls data from an Excel sheet (containing more than 10 thousand rows) and populates another Excel sheet.

我正在创建Excel宏,同时从Excel工作表(包含超过1万行)中提取数据并填充另一个Excel工作表。

I have to insert data row-wise since for every row I need to fetch data from 2-3 different sheets based on a column value (say, EMP_ID).

我必须逐行插入数据,因为我需要根据列值(例如,EMP_ID)从2-3个不同的表中获取数据。

e.g. Excel file - 1 has

例如Excel文件 - 1有

Emp_ID | Emp_Name | Age

Now based on each employee ID I need to fetch employee related data from 3 other Excel sheets. So I have to loop through 10k records.

现在,根据每个员工ID,我需要从其他3个Excel工作表中获取员工相关数据。所以我必须循环通过10k记录。

When I execute the code the Excel application just hangs. I think this is because I try to insert data row-wise.

当我执行代码时,Excel应用程序就会挂起。我想这是因为我尝试逐行插入数据。

Can someone suggest a faster way to insert/update large number of rows.

有人可以建议更快的方式来插入/更新大量的行。

I have already tried using Variant/Array to store data and then populate the sheets. But it still doesn't seem to work.

我已经尝试使用Variant / Array来存储数据,然后填充工作表。但它似乎仍然没有奏效。

NOTE: I am reading records from the Source file into a RecordSet. I have already added:

注意:我正在将源文件中的记录读入RecordSet。我已添加:

Application.DisplayAlerts = False, 
Application.ScreenUpdating = False,
Application.Calculation = xlCalculationManual

And then set it back to default.

然后将其恢复为默认值。

3 个解决方案

#1

I know this is not a direct answer, but sometimes it's better to teach how the work has to be done, intead of simply reply.

我知道这不是一个直接的答案,但有时最好教导如何完成工作,简单回答。

Your work needs to be done with Access (or any other dbms). You have to define three tables, each indexed by Emp_ID, and all the staff become simple.

您的工作需要使用Access(或任何其他dbms)完成。您必须定义三个表,每个表都由Emp_ID索引,并且所有工作人员都变得简单。

#2

I tend to agree with Sergio.
If using a database is totally not an option, using an array is the way to go.

我倾向于同意塞尔吉奥。如果使用数据库完全不是一个选项,那么使用数组是可行的方法。

I have already tried using Variant/Array to store data and then populate the sheets. But it still doesn't seem to work.

我已经尝试使用Variant / Array来存储数据,然后填充工作表。但它似乎仍然没有奏效。

Can you show the code you tried?

你能展示你试过的代码吗?

This works for me:

这对我有用:

Dim arData() As Variant
' ... calculate number of rows and columns ...
ReDim arData(1 To numRows, 1 To numCols)

' ... populate arData ...

' Define range with identical dimensions as arData, e.g. insert in second row
Set rng = sh.Range(sh.Cells(2, 1), sh.Cells(numRows + 1, numCols))
' Transfer array to range (this is fast!)
rng.Value = arData

#3

Also agree, Excel isn't really the tool for this.

同意,Excel并不是真正的工具。

If you're stuck with it then try the following:

如果您坚持使用它,请尝试以下方法:

Read all lookup sheets just once into collections using class objects as your data structures. For example, create a class called Employee and add the appropriate properties.

使用类对象作为数据结构,只需将所有查找表读入集合中一次。例如,创建一个名为Employee的类并添加适当的属性。

Public ID As Long
Public Age As Integer
Public Name As String

To read them, you'd code it like this...

要阅读它们,你可以像这样编码......

    Private mEmployeeCol As Collection


    Dim ws As Worksheet
    Dim empData As Employee
    Dim v As Variant
    Dim r As Long

    Set ws = ThisWorkbook.Worksheets("employee stuff")
    v = ws.UsedRange.Value2
    Set mEmployeeCol = New Collection
    For r = LBound(v, 1) To UBound(v, 1)
        Set empData = New Employee
        empData.ID = v(r, 1)
        empData.Name = v(r, 2)
        empData.Age = v(r, 3)
        mEmployeeCol.Add empData, Key:=CStr(empData.ID)
    Next

To look up the values, do it so ...

要查找值,请这样做......

    Set empData = mEmployeeCol(CStr(ID))
    v(r, [your col]) = empData.ID

Then, definitely, DEFINITELY populate the final sheet with an array of variants. It's pretty straight forward ...

然后,肯定的是,DEFINITELY使用一系列变体填充最终工作表。这很直接......

    Dim v(1 To 10000, 1 To 50) As Variant
    ws.Range("A1").Resize(UBound(v, 1), UBound(v, 2)).Value = v

#1