VBA 导入 Excel 工作表、追加新行和更新现有行

VBA to import Excel worksheet, append new rows, and update existing rows

提问by Andy Entrekin

I'm using Excel to produce reports for a support ticketing system, and I'd like to use VBA to simplify the process of updating the report data. What I want to do is import an Excel file dumped from the ticketing system into the Excel file I'm using for reporting, but with a twist. I need to use the value in one column to identify whether the ticket is new or existing. If it's new, I want to add it to the reporting file. If it's existing, I want to overwrite the matching row (based on the matching column value, which is the ticket number) with the imported data. So the basic process would be:

我正在使用 Excel 为支持票务系统生成报告，并且我想使用 VBA 来简化更新报告数据的过程。我想要做的是将从票务系统转储的 Excel 文件导入到我用于报告的 Excel 文件中，但有一点不同。我需要使用一列中的值来识别票证是新的还是现有的。如果它是新的，我想将它添加到报告文件中。如果它存在，我想用导入的数据覆盖匹配的行（基于匹配的列值，即票号）。所以基本流程是：

1. Open exported file (I know how to do this)
2. For each row in exported file
3. Read ticket number (column A)
4. Search existing sheet column for ticket number (also column A)
5. If found then replace information with imported data (columns B-X)
6. Else append imported data as a new row (columns A-X)
7. Next row

1. 打开导出的文件（我知道怎么做）
2. 对于导出文件中的每一行
3. 读取票号（A 列）
4. 在现有工作表列中搜索票号（也是 A 列）
5. 如果找到，则用导入的数据替换信息（BX 列）
6. 否则将导入的数据附加为新行（AX 列）
7. 下一行

Steps 4-6 above are what I'd like help with. I can use a formula such as =NOT(ISNA(MATCH([imported ticket ID],[array of existing ticket IDs],0))) to return TRUE if the ticket ID exists and FALSE if it doesn't, but would like to find a more elegant solution if one exists.

上面的步骤 4-6 是我想要帮助的。我可以使用诸如 =NOT(ISNA(MATCH([ imported ticket ID],[ array of existing ticket IDs],0))) 之类的公式，如果票证 ID 存在则返回 TRUE，如果不存在则返回 FALSE，但会如果存在，喜欢找到更优雅的解决方案。

Does anyone here have experience with doing this and/or some VBA code I might be able to tweak to suit my purposes? Thanks in advance.

这里有没有人有这样做的经验和/或我可以调整一些 VBA 代码以满足我的目的？提前致谢。

EDIT: Here is the code I have so far. It's not much.

编辑：这是我到目前为止的代码。这并不多。

Sub UpdateTickets()
'Specify data export file
Dim fNameAndPath As Variant
fNameAndPath = Application.GetOpenFilename(Title:="Select File To Be Processed")
If fNameAndPath = False Then Exit Sub
'Open data export file
Workbooks.Open Filename:=fNameAndPath
'For each row in data export file, starting at Row 2
'Check master data file (column A) for ticket number
'If ticket number exists, update information in columns B through P
'Else add new ticket row and place information in columns A through P
'Next row
End Sub

回答by chrono

I just wrote this, and it worked:

我刚刚写了这个，它奏效了：

    Sub import_tickets()
        'run this when the active file is the main ticket list and the active sheet is the ticket list
        'exported file must be open already, and the ticket list must be the active sheet
        Dim exported_file As String
        exported_file = "exported file.xlsx"
        header_exists = True 'if exported file doesn't have a header, set this to false!
        starting_row = 1
        If header_exists Then starting_row = 2

        Dim first_blank_row As Long
        first_blank_row = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0).Row

        Dim r As Long
        r = starting_row
        Dim found As Range
        cur_ticket_num = Workbooks(exported_file).ActiveSheet.Range("a" & r).Value
        Do While Not cur_ticket_num = ""
            'look for current ticket number in main file
            Set found = Columns("a:a").Find(what:=cur_ticket_num, LookIn:=xlValues, lookat:=xlWhole)
            If found Is Nothing Then
                'add info to end of main file
                write_line_from_export exported_file, r, first_blank_row
                first_blank_row = first_blank_row + 1
            Else
                'overwrite existing line of main file
                write_line_from_export exported_file, r, found.Row
            End If
            r = r + 1
            cur_ticket_num = Workbooks(exported_file).ActiveSheet.Range("a" & r).Value
        Loop
    End Sub

    Sub write_line_from_export(src_filename As String, src_r As Long, dest_r As Long)
        For c = 1 To 24
            Cells(dest_r, c).Value = Workbooks(src_filename).ActiveSheet.Cells(src_r, c).Value
        Next c
    End Sub

I hope it helps. I referenced this pagefor the first blank row code and this pagefor the find code. I wrote the code in the main ticket file's module.

我希望它有帮助。我参考了这个页面的第一个空白行代码和这个页面的查找代码。我在主工单文件的模块中编写了代码。