如何将两个不同工作表中的两个表合并成一个具有不同列的最终表？ [英] How to merge two tables from two different work sheets into one final table with different columns?

问题描述

在工作场所，我有以下要做：

$ b $我有表A
B）我有表B
C）我需要输出表C（如何得到它）？

我在这里详细描述：

输入表A和B：

输出表C：

我必须为许多文件获取输出表C，因此使用副本很难匹配表中的订单和订单1和过去的选项在excel。

感谢一下，看看这个。

如果问题不清楚，抱歉。

如果您需要任何有关此信息的进一步信息，请通知我

解决方案

这是SQL的Vba。练习sub myQuery。

  Dim Ws As Worksheet 
 Dim strSQL As String 
 
 Sub myQuery （）
 
 Set Ws = Sheets（C）
 
 strSQL =SELECT Time，Type，User，as [Order]，[Order-1] Urea
 strSQL = strSQL& FROM [A $] where not isnull（Time）
 strSQL = strSQL& Union All
 strSQL = strSQL& SELECT Time，User，Order，Order-1，Urea
 strSQL = strSQL& FROM [B $] where not isnull（time）
 strSQL = strSQL& ORDER BY Time
 
 DoSQL 
 
 End Sub 
 Sub DoSQL（）
 
 Dim Rs As Object 
 Dim strConn As String 
 Dim i As Integer 
 
 strConn =Provider = Microsoft.ACE.OLEDB.12.0; &安培; _ 
Data Source =& ThisWorkbook.FullName& ; &安培; _ 
扩展属性= Excel 12.0; 
 
 
设置Rs = CreateObject（ADODB.Recordset）
 Rs.Open strSQL，strConn 
 
如果不是Rs.EOF然后
 With Ws 
 .Range（a1）。CurrentRegion.Clear 
 For i = 0 To Rs.Fields.Count  -  1 
 .Cells（1，i + 1）.Value = Rs.Fields（i）.Name 
下一个
 .Range（a& 2）.CopyFromRecordset Rs 
 .Columns（1）.NumberFormatLocal =[$ -409] mm / dd / yy h：mm AM / PM; @
 End With 
 End If 
 Rs.Close 
设置Rs = Nothing 
 End Sub 
  

I am very new to coding (a newbie).

At work place, I have following to do:

A) I have Table A B) I have Table B C) I need output Table C (how do I get it?)

I am describing in details here:

Input Tables A and B:

Output Table C:

I have to get Output table C for many many files and thus will be very difficult to match up Order and Order-1 in the Tables using copy and past option in excel.

Thanks a ton for looking into this.

Apologies if the question is not clear.

Please let me know if you need any further information regarding this.

解决方案

this is Vba of SQL. practice sub myQuery.

Dim Ws As Worksheet
Dim strSQL As String

Sub myQuery()

Set Ws = Sheets("C")

    strSQL = "SELECT Time, Type, User, '' as [Order], [Order-1], Urea"
    strSQL = strSQL & " FROM [A$] where not isnull(Time) "
    strSQL = strSQL & " Union All  "
    strSQL = strSQL & "SELECT Time, '', User, [Order], [Order-1], Urea "
    strSQL = strSQL & "FROM [B$] where not isnull(time) "
    strSQL = strSQL & "ORDER BY Time "

    DoSQL

End Sub
Sub DoSQL()

    Dim Rs As Object
    Dim strConn As String
    Dim i As Integer

    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & ThisWorkbook.FullName & ";" & _
            "Extended Properties=Excel 12.0;"


    Set Rs = CreateObject("ADODB.Recordset")
    Rs.Open strSQL, strConn

    If Not Rs.EOF Then
         With Ws
            .Range("a1").CurrentRegion.Clear
            For i = 0 To Rs.Fields.Count - 1
               .Cells(1, i + 1).Value = Rs.Fields(i).Name
            Next
            .Range("a" & 2).CopyFromRecordset Rs
            .Columns(1).NumberFormatLocal = "[$-409]mm/dd/yy h:mm AM/PM;@"
        End With
    End If
    Rs.Close
    Set Rs = Nothing
End Sub

这篇关于如何将两个不同工作表中的两个表合并成一个具有不同列的最终表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！