A表格,A列与B.csv表格匹配比较赋值

aosmmas

如图,A表A列与B.CSV匹配比较 ,把值赋值到C列对应表格,求各位老师,VBA代码写法,谢谢.一直没搞懂,,附带表格

feilanga

1. Sub 提取()
   
2.     Application.ScreenUpdating = False
   
3.     Dim dic As Object
   
4.     Set dic = CreateObject("scripting.dictionary")
   
5.     r = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
   
6.     arr = Sheet1.Range("a1:d" & r)
   
7.     f = Dir(ThisWorkbook.Path & "" & "*.csv*")
   
8.     If f <> "" Then
   
9.         Set wb = Workbooks.Open(ThisWorkbook.Path & "" & f, 0)
   
10.         brr = wb.Sheets("b").UsedRange
    
11.         wb.Close False
    
12.     End If
    
13.     For i = 2 To UBound(brr)
    
14.         If brr(i, 1) <> "" Then
    
15.             Key = brr(i, 1)
    
16.             dic(Key) = brr(i, 2)
    
17.         End If
    
18.     Next
    
19.     For i = 1 To UBound(arr)
    
20.         If arr(i, 1) <> "" Then
    
21.             Key = arr(i, 1)
    
22.         End If
    
23.         If dic.Exists(Key) Then
    
24.             arr(i, 3) = dic(Key)
    
25.         End If
    
26.     Next
    
27.     Sheet1.Range("a1").Resize(UBound(arr), 3) = arr
    
28.     Application.ScreenUpdating = True
    
29. End Sub

复制代码

shiruiqiang

fzxba

1. Sub test1()
   
2.   Dim Conn As Object, SQL As String, CSVFile As String
   
3.   
   
4.   CSVFile = "B.csv"
   
5.   Set Conn = CreateObject("ADODB.Connection")
   
6.   Conn.Open "provider=Microsoft.ACE.OLEDB.12.0;Extended Properties ='Excel 12.0;HDR=NO';Data Source=" & ThisWorkbook.FullName
   
7.   
   
8.   SQL = "SELECT * FROM [Text;FMT=CSVDelimited;HDR=YES;Database=" & ThisWorkbook.Path & "].[" & CSVFile & "]"
   
9.   SQL = "SELECT a.F1,'',b.值 FROM [Sheet1$] a LEFT JOIN (" & SQL & ") b ON a.F1=b.对比"
   
10.   Range("A1").CopyFromRecordset Conn.Execute(SQL)
    
11.   
    
12.   Conn.Close
    
13.   Set Conn = Nothing
    
14.   Beep
    
15. End Sub

复制代码