NULL values from Recordset are not being evaluated by VBA code

Question

I am pulling a stored procedure as my recordset, then after the data has been imported into excel, running a conditional statement to evaluate the data.

Although the stored procedure returns NULL values for a few of the columns in the SQL results grid, when the data is pulled as part of a recordset in excel, the null values appear as blanks in the workbook.

The issue is that it does not appear that excel is correctly identifying blank cells for the statement when the data added to the workbook as part of an ado recordset

if (value) <> “” then
    ROWS.hidden
else 
   Value= “string” 
end if

However, when I copy and paste the data from the results grid in sql server, and replace all the NULL with BLANKS in excel the code executes perfectly.

Can anyone advise to why this is occurring?

Adding on to this, the code looks something like this

Set cnPubs = New ADODB.Connection
Dim sql As String
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
Dim strConn As String
sql = " set nocount on IF OBJECT_ID('tempdb.dbo.#Temp', 'U') IS NOT NULL "
sql = sql + "   DROP TABLE #Temp;  "
sql = sql + "  "
sql = sql + " CREATE TABLE #Temp "
sql = sql + " ( "
sql = sql + "    COL1 Varchar(100), "
sql = sql + "    COL2 smalldatetime, "
sql = sql + "    COL3 Varchar(100), "
sql = sql + "    COL4 Varchar(100), "
sql = sql + "    COL5 Varchar(100), "
sql = sql + "    COL6 Varchar(100), "
sql = sql + "    COL7 INT, "
sql = sql + "    COL8 INT, "
sql = sql + "    COL9 INT, "
sql = sql + "    COL10 INT, "
sql = sql + "    COL11 NVARCHAR(100), "
sql = sql + "    COL12 Varchar(800), "
sql = sql + "    COL13 Varchar(100), "
sql = sql + "    COL14 Varchar(100), "
sql = sql + "    COL15 Varchar(100), "
sql = sql + "    COL16 Varchar(100), "
sql = sql + "    COL17 Varchar(100), "
sql = sql + "    COL18 Varchar(100), "
sql = sql + "    COL19 INT, "
sql = sql + "    COL20 Varchar(100), "
sql = sql + "    COL21 Varchar(100), "
sql = sql + "    COL22 Varchar(100), "
sql = sql + "    COL23 Varchar(100), "
sql = sql + "    COL24 Varchar(100), "
sql = sql + "    COL25 Varchar(100), "
sql = sql + "    COL26 INT, "
sql = sql + "    COL27 INT, "
sql = sql + "    COL28 INT, "
sql = sql + "    COL29 smalldatetime, "
sql = sql + "    COL30 Varchar(100), "
sql = sql + "    COL31 Varchar(100), "
sql = sql + "    COL32 Varchar(100), "
sql = sql + "    COL33 Varchar(100), "
sql = sql + "    COL34 Varchar(100), "
sql = sql + "    COL35 Varchar(100), "
sql = sql + "    COL36 INT, "
sql = sql + "    COL37 INT "
sql = sql + " ) "
sql = sql + "  "
sql = sql + " INSERT INTO #Temp "
sql = sql + " Exec dbo  " 'stored procedure here'
sql = sql + "  "
sql = sql + " Select "
sql = sql + " * "
sql = sql + " from "
sql = sql + " #temp  set nocount off"


cnPubs.Open strConn

cnPubs.CommandTimeout = 360

With rsPubs
    .ActiveConnection = cnPubs
    .Open sql
    ActiveSheet.Select
    Range("A2:AK100000").ClearContents
    Range("A2").CopyFromRecordset rsPubs
    
    
End With
    
rsPubs.Close




cnPubs.Close

Set rsPubs = Nothing
Set cnPubs = Nothing


Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

Worksheets("Data").Cells.Copy
Worksheets("Data").Cells.PasteSpecial xlPasteValues

Theres parts that are left out, but the temp table successfully is updated into the workbook, followed up by the sub to evaulate the audit findings

Sub Get_Audit_items()



'Checking for audits'

Dim lrow As Integer


lrow = Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row







For i = 0 To lrow

If Cells(i + 2, "J") <> "" Or Cells(i + 2, "C") = "" Or Cells(i + 2, "D") = "" Or Val(Cells(i + 2, "H")) = Int(Cells(i + 2, "H").value) Or InStr(Cells(i + 2, "B"), "C/S") > 0 Or InStr(Cells(i + 2, "B"), "T/L") > 0 Or InStr(Cells(i + 2, "B"), "Equity") > 0 Or InStr(Cells(i + 2, "B"), "P/S") Or InStr(Cells(i + 2, "B"), "Warrant") > 0 Or Left(Cells(i + 2, "D"), 3) = "96M" Or Left(Cells(i + 2, "D"), 3) = "97M" Or Left(Cells(i + 2, "D"), 3) = "8AM" Or Cells(i + 2, "C") <> Cells(i + 2, "D") Then
    Rows(i + 2).EntireRow.Hidden = True
Else
    Cells(i + 2, "J").value = "Researching"
End If
Next i

Columns(10).Insert Shift:=xlToRight
Workbooks("DailyProof.xlsm").Worksheets("Data").Range(Cells(1, "A"), Cells(lrow, "M")).AutoFilter Field:=11, Criteria1:=Array("Researching", "Submitted Reports to TSP"), Operator:=xlFilterValues

Workbooks("DailyProof.xlsm").Worksheets("Data").Range(Cells(1, "A"), Cells(lrow, "M")).Copy
Workbooks("DailyProof.xlsm").Worksheets("Audit Results").Activate
Workbooks("DailyProof.xlsm").Worksheets("Audit Results").Range("A1").PasteSpecial







End Sub

NULL values from Recordset are not being evaluated by VBA code

0 Answers