Microsoft Excel 2003-复制无序的描述 [英] Microsoft Excel 2003 - copying out-of-order descriptions

问题描述

我已经使用Microsoft Excel 2003进行了彻底的搜索，但是找不到针对我特定问题的答案.

I have done a thorough search but cannot find an answer for my specific issue, using Microsoft Excel 2003.

我需要将一个Xls电子表格(我们称其为ssA)中的R列描述(字母数字)复制到L&列中M在另一个电子表格(ssB)中，由svc_itm_cde(服务项目代码)提供.大约有svc_itm_cdes 300个.

I need to copy Column R descriptions (alphanumerical) from one xls spreadsheet (let's call it ssA) to columns L & M in another spreadsheet (ssB), by svc_itm_cde (service item code). There are about 300 svc_itm_cdes.

三种并发症:

1. ssA中的svc_itm_cde列与ssB中的列顺序不同.
2. L&的某些行ssB中的M已经包含描述，必须跳过.
3. ssB中的某些svc_item_cdes没有出现在ssA中，反之亦然.

一个朋友帮助我导出到cvs并开始执行Python脚本，但这太冗长了.有什么方法可以用vba代码做到这一点(最好)吗?

A friend helped me export to cvs and begin a Python script, but that was too longwinded. Is there any way to do this with vba code (preferably)?

非常感谢.

推荐答案

对于300条记录，这可能是一种过于复杂的方法，但是对于较大的数据集，这是一种有用的技术...

This might be an over-complicated way of doing this for 300 records but it's a useful technique for larger datasets...

如果只需要将所有数据放在一个地方，以便可以确定要保留的描述和丢失的描述，则可以使用ADO并将两个数据集结合在一起.

If you just need to get all of the data together in one place so you can work out which descriptions to keep and which to lose then you could use ADO and join the two data sets together.

首先转到Visual Basic编辑器(按Alt + F11).在那里，请使用工具">引用"添加对"Microsoft ActiveX数据对象2.8库"的引用

Start by going to the Visual Basic Editor (press Alt+F11). Once there use Tools > References to add a reference to "Microsoft ActiveX Data Objects 2.8 Library"

现在插入>模块并粘贴以下代码:

Now Insert > Module and paste in this code:

Option Explicit

Sub master_list()

    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection

    ' This is the connection string for .xlsx files (Excel 2007 and later)
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
            "Extended Properties=Excel 12.0 Xml;"
        .Open
    End With

    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    rs.Open "SELECT * FROM [ssA$] LEFT JOIN [ssB$] ON [ssA$].[svc_itm_cde] = " & _
    "[ssB$].[svc_itm_cde] UNION ALL SELECT * FROM [ssA$] RIGHT JOIN [ssB$] ON " & _
    "[ssA$].[svc_itm_cde] = [ssB$].[svc_itm_cde] " & _
    "WHERE [ssA$].[svc_itm_cde] IS NULL;", cn

    Dim i As Integer
    Dim fld As ADODB.Field
    i = 0

    ' Sheet3 should be a blank sheet that we can output the results to
    With Worksheets("Sheet3")
         For Each fld In rs.Fields
             i = i + 1
             .Cells(1, i).Value = fld.Name
         Next fld

        .Cells(2, 1).CopyFromRecordset rs
    End With

    rs.Close
    cn.Close

    End Sub

如果您使用的是Excel 2003或更早版本，则连接字符串部分应为:

If you are using Excel 2003 or earlier then the connection string part should be:

With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
        "Extended Properties=Excel 8.0;"
    .Open
End With

这篇关于Microsoft Excel 2003-复制无序的描述的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！