Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Compare Column In Excel to a Column in SQL table 2

Status
Not open for further replies.
Cpreston

Cpreston

MIS
Mar 4, 2015
972
GB
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
 
Sort by date Sort by votes
Hi Andy

Ok I had taken out the variable so added that back in.

It runs now but as error of Run Time error 3704
Application defined or Object defined error

 
Upvote 0 Downvote
compile error
Click to expand...

Are rst and intRow declared appropriately?

Also, Cells is unqualified as to the sheet.

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Upvote 0 Downvote
Yes my fault with the variable. I now have them all named correctly, but now still getting the error

Run Time error 3704
Application defined or Object defined error

 
Upvote 0 Downvote
Error on what statement and for what code?

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Upvote 0 Downvote
Its the code to make the codes not matching turn Yellow. I have attached my code as I have it. I have managed to clear the error, but it is not turning the codes to Yellow where not matched.

Code: 
Private Sub Workbook_Open()

Dim conn As ADODB.Connection
'Dim Cn As New ADODB.Connection.
Dim rs As ADODB.Recordset
'Dim rst As New ADODB.Recordset.
Dim sConnString As String
Dim intRow As Integer


 ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=ITS3200;" & _
                  "Initial Catalog=HTI LIve;" & _
                  "Integrated Security=SSPI;"

' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
  

'Cn.CursorLocation = adUseClient.
'Cn.Open.

  conn.Open sConnString
    Set rs = conn.Execute("SELECT Productcode From Product ;")

'rst.Open strSQL, Cn.

intRow = 1

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

rs.Close
Set rs = Nothing

conn.Close
Set conn = Nothing

End Sub
 
Upvote 0 Downvote
Your code assumes that each time the workbook is opened, sheet contsining the list in question, will be the Active Sheet.

Rather, I’d suggest...
Code: 
[highlight #FCE94F]Dim ws As Worksheet

Set ws = Worksheets(“YourListSheetName”)[/highlight]
intRow = 1

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

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Upvote 0 Downvote
Try to [blue]add[/blue]:

Code: 
[green]'Cn.CursorLocation = adUseClient.
'Cn.Open.[/green]
[blue]
conn.CursorLocation = adUseClient[/blue]
conn.Open sConnString
Set rs = conn.Execute("SELECT Productcode From Product ;")

"the codes are likely to be in column B and start at B1"
then change:

[tt]Cells(intRow, 1).Value [/tt]
to
[tt]Cells(intRow, [red]2[/red]).Value [/tt]

Skip is right (again...) :)
I was working under the assumption that there is only one Sheet in the Workbook.

---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
Hi Andy

I have added in the line

conn.CursorLocation = adUseClient

No problems with that

Changed this bit to 2 and all numbers with 1 below to 2 and then I get Error 3001. The first code is in B2 under header of productcode which is in B1,

intRow = 1

Do While Cells(intRow, 1).Value <> ""
 
Upvote 0 Downvote
I gave you the code assuming your Codes are in Column A starting in row 2 (header row in row 1)
That's why the code:

Code: 
intRow = 2 [green]'Start in row 2[/green]

 Do While Cells(intRow, 1).Value <> ""  [green]'1 here points to column A[/green]

"the codes are likely to be in column B and start at B1"
OK, so code should be:

Code: 
intRow = 1 [green]'Start in row 1[/green]

 Do While Cells(intRow, 2).Value <> ""  [green]'2 here points to column B[/green]

Now you said: "The first code is in B2 " [ponder]

And "then I get Error 3001" - what does the error say? Which line of code does it point to?


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote



The first code is in [highlight #FCE94F]B2[/highlight](
Click to expand...

Code: 
intRow = [highlight #FCE94F]2[/highlight]

Do While Cells(intRow, [highlight #FCE94F]2[/highlight]).Value <> ""

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Upvote 0 Downvote
By the way,you said:
Cpreston said:
Not very good with code
Click to expand...
I would strongly suggest reading about:
[ul]
[li]Debugging VBA Code: Adding Breakpoints[/li]
[li]Watch Window[/li]
[li]5 Ways to Use the VBA Immediate Window in Excel[/li]
[/ul]
With that information, your life as a VBA Programmer will be a LOT easier


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
@Andy, Good suggestions! ESSENTIAL!

FAQ707-4594

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Upvote 0 Downvote
Cpreston,
Were you able to successfully "Compare Column In Excel to a Column in SQL table"?

[pre]
If so
What working code do you have?
Else
Where are you stuck?
End If [/pre]:)


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
Thanks for the reply's , Not had a chance to try the solutions out yet, soon as I do I will get back

Thanks
 
Upvote 0 Downvote
Hi

Quickly just tried changing your code to the logic you provided

intRow = 1 (looking at row 1)

Do While Cells(intRow, 2).Value <> "" Column 2 which is B)

I am getting an error message saying

Run-time error '3001';
Application-defined or object-defined error.

If I change it back to 2 then it runs without error but obviously is not looking in Column B so nothing happens.

Thanks
 
Upvote 0 Downvote
This is the code as it is that runs ok, but does nothing. I definitely have codes in the column B that do not exist in the SQL Product table


Code: 
Private Sub Workbook_Open()

Dim conn As ADODB.Connection
'Dim Cn As New ADODB.Connection.
Dim rs As ADODB.Recordset
'Dim rst As New ADODB.Recordset.
Dim sConnString As String
Dim intRow As Integer


 ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=ITS3200;" & _
                  "Initial Catalog=HTI LIve;" & _
                  "Integrated Security=SSPI;"

' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
  

'Cn.CursorLocation = adUseClient.
'Cn.Open.
  conn.CursorLocation = adUseClient
  conn.Open sConnString
    Set rs = conn.Execute("SELECT Productcode From Product ;")

'rst.Open strSQL, Cn.

intRow = 1

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

rs.Close
Set rs = Nothing

conn.Close
Set conn = Nothing

End Sub
ct table.
 
Upvote 0 Downvote
If your Production codes start in B1 (no header row), then you need to change:[tt]
Cells(intRow, 1).Value
to
Cells(intRow, [highlight #FCE94F]2[/highlight]).Value [/tt]
so your code whould look like:

Code: 
Option Explicit

Private Sub Workbook_Open()

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String
Dim intRow As Integer
[green]
 ' Create the connection string.[/green]
sConnString = "Provider=SQLOLEDB;Data Source=ITS3200;" & _
            "Initial Catalog=HTI LIve;" & _
            "Integrated Security=SSPI;"
[green]
' Create the Connection and Recordset objects.[/green]
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
  
conn.CursorLocation = adUseClient
conn.Open sConnString
Set rs = conn.Execute("SELECT Productcode From Product ;")

intRow = 1  [green]'Start in row 1[/green]

Do While Cells(intRow, [highlight #FCE94F]2[/highlight]).Value <> ""[green]
    'Number [highlight #FCE94F]2[/highlight] here points to second column, which in column B[/green]
    rs.Filter = "ProductCode = " & Cells(intRow, [highlight #FCE94F]2[/highlight]).Value
    If rs.RecordCount = 0 Then[green]
        'No match[/green]
        Cells(intRow, [highlight #FCE94F]2[/highlight]).Interior.Color = vbYellow
    End If
    
    intRow = intRow + 1
Loop

rs.Close
Set rs = Nothing

conn.Close
Set conn = Nothing

End Sub

You have this issue because you've ignored Skip's suggestions.
One from 10 Aug 18 16:02, and another one of:
Code: 
Dim ws As Worksheet

Set ws = Worksheets(“YourListSheetName”)


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
Sorry I am getting totally lost with what code to use where.

I Created a new sheet completely and save it as a macro enabled sheet. In the sheet, which I have renamed to Imports, are 4 columns with no headers. A as customer code in, B as Product code in, c as length and D as price. I want to validate the Product codes in Column B

When I go into Developer\Visual Basic or ALT F11, what should I select in here and what is the exact code I should copy in there.

Sorry about this , I think going back to the start may help if you have the patience for this.

Thanks
 
Upvote 0 Downvote
Code: 
Private Sub Workbook_Open()

End Sub
...is a built-in event in the ThisWorkbook Object which you can find from the VBA editor in your Project Explorer by double clicking the ThisWorkbook object.

Just above the code sheet for the ThisWorkbook object, are two drop-down boxes.
In the left-hand box select Workbook.
In the tight-hand box select Open. The above code stub appears. Paste Andy’s code OVER the entire stub code.

Make sure you have only one Option Explicit

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Upvote 0 Downvote
And make sure you have this Reference selected:[tt]
Tools -> References -> Microsoft ActiveX Data Objects X.X Library[/tt]


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

IcarusHallsorts
  • Locked
  • Question
I have a column of data in Column A
Replies
3
Views
285
Andrzejek
Andrzejek
sal21
  • Locked
  • Question
scrping in listbox in a web page
Replies
0
Views
402
sal21
sal21
Andrzejek
  • Locked
  • Question
Copy data between SQL and Oracle 1
Replies
8
Views
732
remeng
remeng
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
907
remeng
remeng
Andrzejek
  • Locked
  • Question
Data Type in ADODB.Recordset
Replies
6
Views
661
strongm
strongm

Part and Inventory Search

Sponsor

Back
Top