CODEWITHSUNDEEP
arraysexcelvbaado
Johnson Jason
Johnson Jason

Reputation: 721

Avoid trying to load NULL values into array using VBA and ADO/SQL to pull data from excel sheet

I have some simple code that will load all the data from an excel sheet into an array but I am getting an error 94 inproper use of null due to the fact that my source sheet has some blank columns IE: Q through EA are blank columns but A -P and EB - EF have data. (terrible design for an excel sheet being used as a table I know,.. but I didn't do it) Seeing as I cant redesign the table.. how can I skip the blanks as to avoid causing errors when loading them into my array?

Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset
Dim DBPath As String, sconnect As String

DBPath = "\\MYPATH\MYFILE.xlsm"
sconnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBPath _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

Conn.Open sconnect
sSQLSting = "SELECT * From [log$]" 


    mrs.Open sSQLSting, Conn
        '=>Load the Data into an array
        ReturnArray = mrs.GetRows

    'Close Recordset
    mrs.Close

'Close Connection
Conn.Close

Upvotes: 0

Views: 1168

Answers (2)

Rafa Barragan
Rafa Barragan

Reputation: 610

@JohnsonJason Why do you need it in a Array? You could just filter your data with Advanced Filter like here or just drop it and loop to get the columns you need. If you don't know how many columns will be you can create a clone Recordset and get the columns Name and create your Query based on that.

The clone RecordSet is something like this:

'' Declare Variables
Dim oRst As ADODB.Recordset, oRstVal As ADODB.Recordset, oStrm As ADODB.Stream  
Dim sHeaders as String 

'' Set Variables
Set oRst = New ADODB.Recordset
Set oRstVal = New ADODB.Recordset
Set oStrm = New ADODB.Stream  

.... [Something else]
'' Save your current Recordset in the Stream
oRst.Save oStrm  

'' Assign your Stream to the new Recordset (oRstVal)
oRstVal.Open oStrm

'' Loop trough your Recorset for Columns Name
'' Use an IF or a Select to filter
For iCol = 0 To oRstVal.Fields.Count - 1 
    sHeaders = sHeaders + "," + oRstVal.Fields(iCol).Name
Next

And use sHeaders in your Statement in to get the columns you need.

''Instead of Select * From ...
sQuery = "Select " + sHeaders + _
            "From ...."

Upvotes: 0

Parfait
Parfait

Reputation: 107687

The IsNull() function returns True or False. So include it inside Jet/ACE's conditional logic function IIF()

sSQLString = "SELECT IIF(IsNull(Col1), 0, Col1)," _
              & " IIF(IsNull(Col2), 0, Col2)," _
              & " IIF(IsNull(Col3), 0, Col3)" 
              & " From [log$];"

Upvotes: 1

Related Questions