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

 

[Cpreston]

Hi

Followed everything you both have said.

When I run the code I am getting a Run Time Error saying Application-defined or object defined error

Thanks

 

[Cpreston]

Sorry, I closed the sheet and on open it try's to run and then gives error showing on this line,

rs.Filter = "ProductCode = " & Cells(intRow, 2).Value

 

[Andrzejek]

And the error says....?

And could you show a few examples of your ProductCode values from your Product table?

---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

Hi

Ok on open a MS Visual Basic box appears with run time error 3001, Arguments of the wrong type are out of acceptable range, or are in conflict with one another.

I go to debug and after clicking Ok on Cant execute code in break mode many times it leaves the code with the line rs.Filter = "ProductCode = " & Cells(intRow, 2).Value
in Yellow.

Example codes in column B are

p050125A1
p050150PU
s019075CP1
s019100CP2
xBU1056/0011
xBU1056/0012



Thanks

 

[Andrzejek]

In my previous post I said:

...
Do While Cells(intRow, 1).Value <> ""[green]
    [highlight #FCE94F]'If your field is Text
[/highlight]    'rst.Filter = "ProdCode = '" & Cells(intRow, 1).Value & "'"
    [highlight #FCE94F]'If your field is a number[/highlight][/green]
    rst.Filter = "ProdCode = " & 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
...

So you use the part of the code that deals with NUMBERS.
But your ProductCodes are TEXT.

Change:
[tt]rs.Filter = "ProductCode = " & Cells(intRow, 2).Value[/tt]
tp
[tt]rs.Filter = "ProductCode = [highlight #FCE94F]'[/highlight]" & Cells(intRow, 2).Value [highlight #FCE94F]& "'"[/highlight][/tt]



---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

Hi

Perfect it is now working. I will test it on some more codes that are know are incorrect, but looking good. Thanks for the help.

One more cheeky question, if I wanted it to run from a button rather than on Open, what would I need to change. Basically I am getting users to check their own codes before price list imports, so need a way they can cut and paste into the sheet and then check. I may need to do it this way but not 100% sure yet.

 

[Andrzejek]

Great

BTW – if you run your code by closing and re-opening your Excel (because you have your code in Workbook_Open event), you may want to place the cursor anywhere in the line “Private Sub Workbook_Open()” and hit F5 to run your code. Or – better yet – hit F8, and keep hitting F8 to see how your code is working.

If your users know how to run a macro in Excel, you can have your code in Macro enabled Excel file where they would paste the ProductCodes and run the Macro from the macro list in Excel. No Button needed.


---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

Yes good ideas as always, I will do that.

Big thanks to you both for your patience through this I really appreciate it. Thank you

 

[Andrzejek]

So, do you want your code to run on opening of your Excel file AND be able to run it as a Macro?
If so, do you know how to do it?


---- Andy

There is a great need for a sarcasm font.