更新时间:2023-02-07 10:11:01
关于ADO:
''Reference Microsoft ActiveX Data Objects x.x Library
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim scn As String
Dim sSQL As String
Dim sFullName As String
''Probably not the best way to get the name
''but useful for testing
sFullName = ActiveWorkbook.FullName
scn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& sFullName _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
cn.Open scn
sSQL = "SELECT IIf(c.Material=t.Material,'ok', " _
& "IIf(c.Material<>t.Material,c.Material,'nok')) " _
& "FROM [Sheet1$] t " _
& "LEFT JOIN [ODBC;Description=TEST;DRIVER=SQL Server;" _
& "SERVER=Server;Trusted_Connection=Yes;" _
& "DATABASE=test].CheckTable c " _
& "ON t.SerialNumber=c.SerialNumber "
rs.Open sSQL, cn
''Might be problems with order of entries
Worksheets("Sheet1").Range("c2").CopyFromRecordset rs
以上使用SQL Express的连接字符串,您可以从以下列表中获取更多字符串: http://www.connectionstrings.com/
The above uses a connection string for SQL Express, you can get more strings from: http://www.connectionstrings.com/