VBA导入Excel工作表，追加新行并更新现有行 [英] VBA to import Excel worksheet, append new rows, and update existing rows

问题描述

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

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:

1. 打开导出的文件（我知道怎么做）


2. 对于导出文件中的每一行


3. 读取票号（A列）


4. 在现有工作表列中搜索票号（也是A列）


5. 如果找到，则用导入的数据替换信息（BX列）


6. 另外将导入的数据作为新行追加（列AX）


7. 下一行

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

上面的步骤4-6是我想要帮助的。我可以使用公式，如= NOT（ISNA（MATCH（[导入的故障单ID ]，[现有故障单ID 的数组]，0）））如果返回TRUE，则返回TRUE票证ID存在，如果不存在则为FALSE，但如果存在，则希望找到更优雅的解决方案。

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.

这里有没有人有这方面的经验和/或者一些VBA代码我可以调整以适应我的目的？提前致谢。

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.

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

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

推荐答案

我刚刚写了这个，它起作用了：

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

我希望它有所帮助。我引用了此页面的第一个空行代码和< a href =http://msdn.microsoft.com/en-us/library/office/ff839746.aspx =nofollow noreferrer>此页面查找代码。我在主票证文件的模块中写了代码。

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

这篇关于VBA导入Excel工作表，追加新行并更新现有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！