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")
精彩评论