|
回复 7楼 riverqin 的帖子
Sub Test()
Dim conn As Object
Dim sql1 As String
Dim r1, r2 As Long
[A2:D65536].ClearContents
Set conn = CreateObject("adodb.connection")
conn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;Data Source=" & ThisWorkbook.FullName
r1 = Sheets("用户").[A65536].End(xlUp).Row
r2 = Sheets("角色").[A65536].End(xlUp).Row
Sql = "select a.姓名,a.区域,a.范围,b.角色 from " & _
"(select 姓名,区域,范围 from [用户$A1:C" & r1 & "]) a " & _
"right join [角色$A1:C" & r2 & "] b on a.范围=b.范围 order by a.姓名, a.区域"
Sheets("分配").Range("A2").CopyFromRecordset conn.Execute(Sql)
conn.Close
Set conn = Nothing
MsgBox "OK!", vbInformation
End Sub |
|