将唯一的数据从源工作表添加到主工作表 [英] Adding unique data from Source Worksheet to Master Worksheet

问题描述

源列表工作表( SLW )列(1、2和3)中的一行需要粘贴到主列表工作表( MLW )列(3，4和5)[相同的顺序]如果唯一的 ID 号(SLW1 = MLW3)在<主列表"(相同的工作簿)中确实已经存在 NOT .我的第一个Excel VBA项目.因此，所有建议/建议/更正/捷径都是不错的选择.这段代码是我摸索创建的.如您所知，它不起作用.

A row from the Source List Worksheet (SLW) columns (1, 2 & 3) needs pasted into the Master List Worksheet (MLW) columns (3, 4 & 5) [same order] if the unique ID number (SLW1 = MLW3) does NOT already exists in the "Master List" (same workbook). My first Excel VBA project ever. So any and all advice/suggestions/corrections/short cuts would be great. This code is what I have fumbled creating. As you know, its not working.

Sub Transfer()

    Dim SLR As Integer 'SourceList's Woksheets Last Row
    Dim MLR As Integer 'MasterList's Woksheets Last Row
    Dim SC As Integer 'SourceList Counting through the loop (ROW NUMBER)
    Dim SR As Range 'SourceList A-C Row data
                    '(Source information 3 rows to be transfered)
    Dim ID As Integer 'Unique code of Projects
    Dim Found As Range

    Sheets("SourceList").Activate
    SLR = Cells(Rows.Count, "A").End(xlUp).Row

    'Start loop to go through SourceList unique ID numbers
    For SC = 2 To SLR
        'Copy SourceList ID number into Variable "ID"
        ID = Sheets("SourceList").Range(1, SC)

        'Also, Save Range into Variable so it doesn't have to
        'go back and forth between Worksheets
        Set SR = Range(Cells(1, SC), Cells(3, SC))

        Sheets("MasterList").Activate
        Found = Columns("C:C").Find(What:=ID, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
        If Found Is Nothing Then
            MLR = Cells(Rows.Count, "C").End(xlUp).Row + 1
            Range(Cells(3, MLR)) = SR
            SR.ClearContents
        End If
        Sheets("SourceList").Activate
    Next SC
End Sub

推荐答案

尽管我已经发布了一个链接供您签出，但我将发布我之前使用过的此解决方案.

Although I've posted a link for you to check out, I will post this solution which I've used before.

Sub ject()
    Dim con As Object: Set con = CreateObject("ADODB.Connection")
    Dim rec As Object: Set rec = CreateObject("ADODB.Recordset")
    
    Dim datasource As String
    datasource = ThisWorkbook.FullName ' returns the fullpath

    Dim sconnect As String
    sconnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & datasource & ";" & _
                "Extended Properties=""Excel 12.0;HDR=YES"";"
    con.Open sconnect
    
    Dim sqlstr As String
    ' This basically executes anti-join if you know SQL
    sqlstr = "SELECT * "
    sqlstr = sqlstr & "FROM [SWL$] e "
    sqlstr = sqlstr & "LEFT JOIN [MWL$] u "
    sqlstr = sqlstr & "ON e.ID = u.ID "
    sqlstr = sqlstr & "WHERE u.ID IS NULL "
    sqlstr = sqlstr & "AND e.ID IS NOT NULL;"

    rec.Open sqlstr, con, 3, 1
    
    ' Dump data that meets your requirement
    With Sheets("MWL")
        Dim lr As Long
        lr = .Range("D" & .Rows.Count).End(xlUp).Row + 1
        .Range("D" & lr).CopyFromRecordset rec
    End With
End Sub

注意事项:

1. 您的 SWL 和 MWL 工作表数据应从具有标题的第1行开始.
2. 两者都应具有标题名称 ID ，其中包含唯一的标识符.如果没有，您可以调整上面的代码.

1. Your SWL and MWL sheet data should start at Row 1 with headers.
2. Both should have the header name ID which contains the unique identifier. If not, you can adjust the code above.

因此，代码要做的是访问 ADO(活动数据对象)，以便能够使用SQL命令执行数据比较.它比常规的范围到范围"比较(循环)要快得多.我不确定它是否比Array到Array的比较要快，但是一旦掌握了它，肯定会更容易阅读和调整.无论如何，目前这可能有点太多(因为您说这是您的第一个项目)，但这已经过尝试和测试，并且肯定可以工作.

So what the code does is access ADO (Active Data Objects) to be able to execute data comparison using SQL commands. It is way faster than the conventional Range to Range comparison (looping). I'm not sure if it is faster than Array to Array comparison but it is certainly easier to read and adjust once you get the hang of it. Anyways, this maybe a little bit too much at the moment (since you said it is your first project), but this is tried and tested and certainly works.

重要提示:请注意 sconnect 变量.您需要使用正确的 连接字符串 ，具体取决于您的版本Excel.

IMPORTANT: Notice the sconnect variable. You need to use the correct Connection String depending on the version of your Excel.

这篇关于将唯一的数据从源工作表添加到主工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！