Compare Column In Excel to a Column in SQL table 2

[Cpreston]

Hi

I have a column of product codes in an Excel sheet. I want to be able to compare the codes in the sheet to a Product database in SQL.
If one of the codes in the Excel sheet are not in the product table in SQL, I somehow want to be alerted on the excel sheet.

I cannot find a way to compare the two. I have found a lot of inserts ideas but I just want to compare and find the codes that do not exist.

Any ideas please.

Thanks

 

[Andrzejek]

Do you want to connect - from Excel - to your SQL DB and retrieve the product codes from there, and then compare them?
Or are you open to: copy (by hand) the product codes from SQL DB to your Excel where you keep the product codes and compare them?

And how do you "want to be alerted" about the outcome? Highlight the difference, maybe?


---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

Hi

I want to to connect - from Excel - to your SQL DB and retrieve the product codes from there, and then compare them? I don't want the codes from SQL in Excel, just only to compare.

Maybe the code that does not match change colour text or fill, or a list of codes not matching.

Thanks

 

[Andrzejek]

To connect from Excel to an SQL DB - see this link and this link.

After you successfully connect to your DB and can create a record set (with product codes) in Excel VBA, we can proceed with comparing logic.


---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

Hi

Not very good with code, but I sued the first link and I am getting the error message below in brackets on this line.

Dim conn As ADODB.Connection (Compile Error, User defined type not defined)

I opened a new Excel sheet, went into developer tab then into Visual basic and pasted the code. I changed the data source to server\localhost

Any ideas

 

[SkipVought]

I don't want the codes from SQL in Excel, just only to compare.

Well that would be the easiest way!

On a separate sheet, Data > Get External Data > From Other Sources > From Microsoft Query... and select your data source. If it’s not there, then you’ll have to set up a driver for the database in the ODBC Data Source Administrator.

Query to return a DISTINCT list.

Then use a MATCH() formula in Conditional Formatting to highlight missing values...
[tt]
=ISNA(MATCH(A2,YourSQLlist,0))
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Cpreston]

Hi

Skip I don't want to return any data from the sql table, just compare. I tired using the MsQuery route but could not compare it again the column in Excel.

Andy - does the code ADODB.Connection mean a connection to Access?

Thanks to all

 

[SkipVought]

ADODB=
ActiveX
Data
Objects
Data
Base

Of course you can compare two lists using MATCH() in Excel!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Cpreston]

But how do I compare 2 lists without briging some 40,000 codes back to an excel sheet from the sql database?

 

[SkipVought]

compare

But how do I compare 2 lists without briging some 40,000 codes back to an excel sheet from the sql database?

Is that REALLY a problem? I think not. Its a nit on a flea! I've done stuff like this often. Its a convenience, compared to other solutions IMNSHO.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

In the second link there is a hint (unfortunately missing in the first link):[tt]
Tools -> References -> Microsoft ActiveX Data Objects X.X Library[/tt]
do this in Excel VBA IDE I would choose the latest version of the Library.

"does the code ADODB.Connection mean a connection to Access? "
You can connect to Access, or SQL Server, or Oracle, or any other DB you want.


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Another approach might be a UDF. I've used 100 or so similar functions to access data from remote databases. It can also be used to verify existing data. Here's an example using ADODB where NOTHING is returned if no data in remote DB. In this particular example, my DB was another workbook, but it could have been Oracle, SQL Server, Access, DB2...

Function Nomen(PN As String) As String
    Dim sPath As String, sDB As String, sConn As String, sSQL As String
    Dim cnn As ADODB.Connection, rst As ADODB.Recordset
    
    sPath = "C:\Users\Skip\Documents"
    sDB = "TT_DB.xlsm"
'[URL unfurl="true"]https://www.connectionstrings.com/excel-2013/[/URL]
    sConn = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ";"
    
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    
    cnn.Open sConn
    
    sSQL = "Select Distinct "
    sSQL = sSQL & " [Nomenclature] "
    sSQL = sSQL & "From [Part Master$] "
    sSQL = sSQL & "Where [Part Number] = '" & PN & "'"
    
    rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
    
    On Error Resume Next
    
    rst.MoveFirst
    
    If Err.Number = 0 Then
        Nomen = rst(0).Value
    Else        'no Part Number
        Nomen = ""
        Err.Clear
    End If
    
    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Function

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Cpreston]

Hi Skip

Tried using the Match function using MSquery again, not quite what I want but the forumal I have is

=ISNA(MATCH(A1,Sheet2!A:A,0))

Sheet 2 is the data form the MSquery result.

In sheet 1 the result gives False for the codes that match and True for the ones that don't.

I will play with the coe you and Andy have sent, will take me some time, I get back to you.

Once gain thanks to all for the replies.

 

[SkipVought]

In sheet 1 the result gives False for the codes that match and True for the ones that don't.

YES!!! In the CF, the TRUE results will result in a CFormat, like a shaded cell. That how my uploaded example workbook performs.

You can use a formula loke this in a CF or on the sheet, copy/pasted through all rows of data.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

Cpreston,

Were you able to successfully compare the codes from Excel and the Data Base?


---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

Hi

I managed to get the link code working. However this just brings back the whole list of 41,000 + codes back. Which then means I still have to sue the Match formula or something like it.

Also got Skips way working with the Msquery and Match. So I think I have 2 ways which work.

Basically we get sent some 100 plus sheets every quarter of codes to import into a SQL database. We have to format them and then check validity in Access before importing.
I was trying to get something in place where the people who send the sheets put a sheet in a certain format and then check if the codes exist in our SQL database, if they don't they can fix the issue before4 sending them in. This would save time here when we get them.

But I have 2 solutions so I need to think how they can use it without to much effort or thinking on their own.

Thanks

 

[Cpreston]

In the VBA code, I want it to run when I open the spreadsheet. How do I get it to do this

I have created workbook_open command but cannot get it to call the code

Private Sub Workbook_Open()

MsgBox "say hello"

End Sub

I can get a MsgBox to work, but cannot find a way for it to run the VBA code. The VBA cod starts with Sub ConnectSqlServer()
I have tried to do the below but this keeps failing. What do I have to put to call the code to run on open. Thanks

Private Sub Workbook_Open()

RUN ConnectSqlServer

End Sub

 

[SkipVought]

I have created workbook_open command

WHY??? There is a Workbook_Open event built into Excel. There is nothing to create.

In the VB Editor, View > Project Explorer.

In the Project Explorer, you will see your VBAProject. If it is not already open (-), then open (+).

Open Microsoft Excel Objects.

In Microsoft Excel Objects, double-click on ThisWorkbook. [ThisWorkbook (Code)] code sheet opens.

Above the code sheet are two drop-downs. One displays (General), the Object drop-Down and the other (Declarations), the Procedure drop-down.

In the Object drop-down, select Workbook. The Workbook_Open event code skeleton appears.

This event will run every time the workbook opens if macros are enabled.

Make sure you have no other procedure named Workbook_Open!!!

BTW, having selected Workbook in the Object drop-down, you can make visible a number of other Events by selecting in the Procedure drop-down.

...AND for each Sheet, selecting in the Project Explorer, you can find sheet events similarly.

Here’s all you need...

Private Sub Workbook_Open()

    ConnectSqlServer

End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

This is what I did.

My Product Codes in Excel are in column A starting in cell A2 going down with no empty cells in between. So the logic is done when first empty cell is hit.

You need to establish your [red]connection string[/red] to your data base, plus [blue]your SQL[/blue] to get the list of production codes from the table in your DB.

If a Product Code from Excel is NOT found in the data base, the cell with that code is [highlight #FCE94F]highlighted in Yellow[/highlight] in Excel.

In VBA IDE:[tt]
Tools -> References -> Microsoft ActiveX Data Objects X.X Library[/tt]

Option Explicit

Private Sub Workbook_Open()
Dim Cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim intRow As Integer

Columns("A:A").Interior.Color = vbWhite

Cn.ConnectionString = [red]from [url=https://www.connectionstrings.com/]ConnectionStrings.com[/red]
[/url]Cn.CursorLocation = adUseClient
Cn.Open

strSQL = [blue]"SELECT ProdCode FROM MyTable"[/blue]

rst.Open strSQL, Cn

intRow = 2

Do While Cells(intRow, 1).Value <> ""[green]
    'If your field is Text
    'rst.Filter = "ProdCode = '" & Cells(intRow, 1).Value & "'"
    'If your field is a number[/green]
    rst.Filter = "[blue]ProdCode =[/blue] " & Cells(intRow, 1).Value
    If rst.RecordCount = 0 Then[green]
        'No match[/green]
        Cells(intRow, 1).Interior.Color = vbYellow
    End If
    
    intRow = intRow + 1
Loop

rst.Close
Set rst = Nothing

Cn.Close
Set Cn = Nothing

End Sub

Then I saved my Excel file as Macro Enabled Workbook (*.xlsm)
This logic runs when I open the Excel file and the Production Codes NOT in the data base are highlighted.


---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

Hi

Skip - I have put in the solution to use the built in function. It is not complaining about anything on open, but need to wait for a new codes to be added to see if it works, thanks

Andy - Trying out the code to mark in yellow. I am getting stuck on a compile error, Variable not found. It is happening on the row below and highlight the ret part.

rst.Filter = "ProductCode = " & Cells(intRow, 1).Value

Any ideas please.

Also the codes are likely to be in column B and start at B1, and could be from 10 to 10000 rows.

Thanks to both for your great help.