CODEWITHSUNDEEP
sqlexcelvba
Koosh
Koosh

Reputation: 896

Referencing an excel spreadsheet cause an issue in VBA macro code

I have 2 tabs in my Spreadsheet.

Tab 1 - this tab contains a Worksheet_Change macro, that grabs the value from Target Cell, and is later used in my Select query against a table.

Tab 2 - I have a table in this tab that has a connection to SQL Database, it basically displays all data from a SQL Table.

The code looks like this...

If Not Intersect(Target, [A:A]) Is Nothing Then
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim Val1 As String
    Dim strSQL As String
    
    
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    'Check if there's value in A, if not set to No Fille
    Val1 = Trim(Target.Value)
    If IsEmpty(Val1) = True Or Val1= "" Then
        Target.Interior.ColorIndex = 0
    Exit Sub
    End If
    
    'Now open the connection.
    conn.Open strConn
    
    strSQL = "SELECT 1 FROM table1 where ID= '" & Val1 & "'"
    Debug.Print strSQL
    
    rs.Open strSQL, conn
    
    If rs.BOF And rs.EOF Then
        Target.Interior.ColorIndex = 3  
    Else
        Target.Interior.ColorIndex = 4
    End If
    
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End If

Here's what's happening. I go into Tab 2 and grab an ID, let's say Test123 (which is valid, because I've checked it prior to testing), and I go into Tab 1 and paste it in A2. My macro executes, however colors the cell as if the value is INVALID.

Essentially, this part of code is executed because EOF and BOF are both true (coming back with value of -1)

If rs.BOF And rs.EOF Then
    Target.Interior.ColorIndex = 3

I added a Debug.Print to the strSQL to check the query and this is what it looks like (notice the single quote on the 2nd line)

SELECT 1 FROM table1  where ID= 'Test123
'

ANy idea why this is happening and how it can be addressed? Any other value that I enter into any of the Cells in Column A looks properly in the SQL query like so:

 SELECT 1 FROM table1  where ID= 'Test1'

Is there any way why the single quote line is appearing on the 2nd line, I have a feelings that's exactly why it is causing the issue.

@TimWilliams

I did as you instructued and here's what it looks like: Exact results from immediate window.

B              66 
G              71 
4              52 

               13 

               10

Upvotes: 0

Views: 49

Answers (1)

Tragamor
Tragamor

Reputation: 3634

If you think there is whitespace interfering with Val1, there is a function posted here which may work:

https://excelfox.com/forum/showthread.php/155-Trim-all-Cells-in-a-Worksheet-VBA#post1092

Code courtesy of Rick Rothstein

Function CleanTrim(ByVal S As String, Optional ConvertNonBreakingSpace As Boolean = True) As String
    Dim X As Long, CodesToClean As Variant
    CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
         21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)
    If ConvertNonBreakingSpace Then S = Replace(S, Chr(160), " ")
    For X = LBound(CodesToClean) To UBound(CodesToClean)
        If InStr(S, Chr(CodesToClean(X))) Then S = Replace(S, Chr(CodesToClean(X)), "")
    Next
    CleanTrim = WorksheetFunction.Trim(S)
End Function

Upvotes: 1

Related Questions