Excel VBA says cell value is null when it is not.

[erickamiller143]

I am working on a spreadsheet that uses a sql code. Everything goes well for all information with the exception of one column of data that does not want to pull through. The column is formatted as text. Every cell in the column has a value that can be a number, letters, or letters/numbers combo. I have no control over the formatting of the data in this column. The other 4 columns pull in just fine but the mixed formatting one does not. The column is formatted as text. What can I check to see where my error lies? I cannot paste the actual code but the line in questions reads as follows. Please note this is oversimplified as there are many columns of data. I'm trying to just gather a checklist of items to check like column formatting in the spreadsheet itself or having to call the column in some other way. Right now I am creating the new excel document through this code and checking for empty cells then using a vlookup to get the missing data. This is a cumbersome task and would rather fix the code then continuing this backwards document creation. I appreciate any advice. Thank you.

(this is part of a loop through a collection)

Dim CurRow as long
Dim start as range
Dim i as variant
Dim coll as new collection
Dim partdata as new ADODB.recordset
Dim iTxt as string

CurRow=1
Set Start="A1"

For each i in coll
    iTxt=i

'code to open connection to self
    With sqlData
            .ActiveConnection = cnn
            .Source = "SELECT DISTINCT [Part_Data$].[Category], [Part_Data$].[PartID], [Part_Data$].[PartDescription] FROM [Part_Data$] WHERE [Part_Data$].[Category] = '" & iTxt & "'"
            .LockType = adLockOptimistic
            .CursorType = adOpenKeyset
            .Open
     End With
 
    Do While Not sqlData.EOF

    Start.offset(CurRow,4).value=sqlData("PartID").value
CurRow=CurRow+1

   sqlData.movenext
   sqlData.close
Next i

 

[WassimN]

Hello erickamiller143,

"What can I check to see where my error lies?"
Are you 100% certain that the SQL code is working the way it should? Is the database table where the SQL code is looking populated with the right data?

Excel formatting will not and should not impact any SQL query. Even if the fornt is set wrong, SQL should get something into the cells under normal conditions.

The fact that you say "The other 4 columns pull in just fine but the mixed formatting one does not." tells me that things are not on the Excel side but on the SQL side.

Are you using an old workbook that may have gotten corupted somewhere somehow?

Can you copy the SQL code into a fresh workbook and execute it and see what happens?

Best of luck figuring this one out.

 

[erickamiller143]

Hello erickamiller143,

"What can I check to see where my error lies?"
Are you 100% certain that the SQL code is working the way it should? Is the database table where the SQL code is looking populated with the right data?

Excel formatting will not and should not impact any SQL query. Even if the fornt is set wrong, SQL should get something into the cells under normal conditions.

The fact that you say "The other 4 columns pull in just fine but the mixed formatting one does not." tells me that things are not on the Excel side but on the SQL side.

Are you using an old workbook that may have gotten corupted somewhere somehow?

Can you copy the SQL code into a fresh workbook and execute it and see what happens?

Best of luck figuring this one out.

Following some of your suggestions, it turned out to be corrupted data. I copied and pasted the data into a new worksheet. The part numbers are pulling through perfectly now. Thank you.