开发者

Lookup serial numbers in excel through SQL Server database

开发者 https://www.devze.com 2023-01-24 06:35 出处:网络
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 S

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 ;

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

  • 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

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

Thank you for your help; Regards,

Before Running macro, my Excel Sheet;

Material SerialNumber Check
MTR5100 1 
MTR5100 2 
MTR4100 3 
MTR4100 4

Checktable in checkdb on sql-sever;

Material SerialNumber
MTR5100 1 
MTR5100 2 
MTR6100 3 
MTR4100 5

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


How about 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

The above uses a connection string for SQL Express, you can get more strings from: http://www.connectionstrings.com/


One way to do this in an Excel centric fashion is to import the entire table into a sheet. In EXcel 2010, you can do that with Data -> From Other Sources -> SQL Server.

Then, in the existing sheet, you add a column labelled Material from DB, and fill it with vlookup:

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

Then in the 4th column, you can compare the material from the sheet with the material from the database like:

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

精彩评论

暂无评论...
验证码 换一张
取 消