通过SQL Server数据库在excel中查找序列号

Question

在我的Excel表中，像下面这样有三个字段。我想连接到Server (myserver)数据库(checkdb)。Checkdb数据库有一个包含两个字段(Material和SerialNumber)的表(checktable)。在这种情况下，我想查找每个SerialNumber和Meterial，并编写一些文本来检查我的Excel表格中的宏；

如果存在

• ，且其材料与Excel表中的材料名称相同，则宏将写入"ok“以检查字段
• ，但其材料与Excel中的材料名称不同，宏将在数据库中写入材料名，如果序列不存在，宏将在数据库中写入”
• “，宏将写入"nok”以检查字段

。

谢谢你的帮助，问候，

在运行宏之前，我的Excel表；

Material SerialNumber Check
MTR5100 1 
MTR5100 2 
MTR4100 3 
MTR4100 4

sql服务器上校验数据库中的检查表；

Material SerialNumber
MTR5100 1 
MTR5100 2 
MTR6100 3 
MTR4100 5

运行宏后，在我的Excel工作表检查字段将如下所示；

Material SerialNumber Check
MTR5100  1   ok
MTR5100  2   ok
MTR4100  3   MTR6100
MTR7100  4   nok

Answer 1

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，您可以从：http://www.connectionstrings.com/获得更多的字符串

Answer 2

以Excel为中心的一种方法是将整个表导入到工作表中。在EXcel 2010中，您可以使用Data -> From Other Sources -> SQL Server来实现这一点。

然后，在现有的工作表中，添加一个标记为Material from DB的列，并将其填充为vlookup

=VLOOKUP(A3,Sheet1!$B$3:$C$5,2,FALSE)

然后，在第4栏中，您可以比较表中的材料和数据库中的材料，如下所示：

=IF(A3=B3,"ok","mismatch")