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

Question

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

because I dynamically load sheets into this and it's not always the same thing being used. I just need everything in the sheet(table) to be loaded into my array. — Johnson Jason
– Johnson Jason, Commented Dec 17, 2015 at 21:38
I guess I could dynamically load my select statement.. ugh, so much more work though. — Johnson Jason
– Johnson Jason, Commented Dec 17, 2015 at 21:41
okay, turns out even specific fields in select statement won't help because there are blanks in the table. ACCCK.. still need help on this one. — Johnson Jason
– Johnson Jason, Commented Dec 17, 2015 at 21:49
I need something like this ISNULL(myColumn, 0 ) in my select statement but that doesn't work apparently — Johnson Jason
– Johnson Jason, Commented Dec 17, 2015 at 22:21

Parfait · Accepted Answer · 2015-12-18 01:15:30Z

1

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$];"

answered Dec 18, 2015 at 1:15

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 10:33:53Z

@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 ...."

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related