通过SQL Server数据库在Excel中查找序列号 [英] Lookup serial numbers in excel through SQL Server database

问题描述

在我的Excel工作表中，如下所示，有三个字段.我想连接到SQL Server(myserver)数据库(checkdb).Checkdb数据库具有一个表(checktable)，该表具有两个字段(Material和SerialNumber).通过这种连接，我想查找每个SerialNumber和Meterial，并用宏;在我的Excel工作表中写一些文本来检查字段；

In my Excel sheet, like below there are three fields. I would like to connect to SQL Server (myserver) database (checkdb). Checkdb database has a table (checktable) that has two fields (Material and SerialNumber). With this connection I want to look up each SerialNumber and Meterial and write some text to check field in my Excel sheet with the macro ;

• 如果存在SerialNumber，并且其Material与材料名称相同在Excel工作表中，宏会写"ok"来检查字段

• if SerialNumber exist and its Material is the same as the material name in the Excel sheet, macro will write "ok" to check field

如果存在SerialNumber，但其Material与Excel中的材料名称不同，宏将在数据库中将材料名称写到我们要查找的序列中.

if SerialNumber exist but its Material is different from the material name in Excel, macro will write the material name in database refer to serial that we are looking for

如果序列号"不存在，宏将写"nok"以检查字段

if Serial does not exist, macro will write "nok" to check field

感谢您的帮助；问候

在运行宏之前，我的Excel工作表是

Before Running macro, my Excel Sheet;

Material SerialNumber Check
MTR5100 1 
MTR5100 2 
MTR4100 3 
MTR4100 4

sqlserver上的checkdb中的Checktable;

Checktable in checkdb on sql-sever;

Material SerialNumber
MTR5100 1 
MTR5100 2 
MTR6100 3 
MTR4100 5

运行宏后，在我的Excel工作表中，检查字段将看起来像这个;

After running macro, In my Excel sheet check field will be look like this;

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

推荐答案

关于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/

这篇关于通过SQL Server数据库在Excel中查找序列号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！