Excel recordset returns Null value

[shahid09]

Hi All,

I have created recordset from excel but i have noticed one weird thing. Excell cell has some value Like"6L0" but it returns NULL.
Sometimes if cell is empty it return NULL which i am taking care using following code, but if cell has some value and it returns value how should i handle this situation ?
Here is how i handle that situation when cell is empty and returns NULL

If IsNull(objRecordSet(intCounter)) Then
     ActualArray(intRowCount, intCounter )= ""
Else
   ActualArray(intRowCount, intCounter ) = objRecordSet(intCounter)
End If

My question here is objRecordSet(intCounter) has some value in table but returns NULL how should i handle this situation ?

Thanks,
Shahid

[Atom290]

Are you using ADO to pull back the values of the Excel sheet?

Sometimes the values are in as values, and sometimes functions. Using ADO to pull back the values is a great way, but sometimes flawed, especially trying to put values into the sheet for the first time, as it will assume the formatting of the cell above...so if it is a number you are ok, but if you have put a title then it will assume a text field.

Let me know the method and I will try and help further

[shahid09]

Thank u atom.
Here is the full code

Thanks
Shahid

Call ReadDataFromExcel("H:\Book2.xls", "sheet1")
Dim ExpectedArray
Function ReadDataFromExcel(sFileNameAndPath, sSheetName)
 
 
Dim conn, rs
Dim sSQL,vRows, strtext
Dim intColumnCount, intRowCount, intCounter
' Array to store records

Set conn = CreateObject("ADODB.Connection")
With conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = _
"Data Source=" & sFileNameAndPath & ";" & _
"Extended Properties=Excel 8.0;"
.Open
End With

Set rs = CreateObject("ADODB.Recordset")
sSQL = "SELECT * FROM [" & sSheetName & "$]"

rs.open sSQL, conn

intColumnCount = rs.Fields.Count
 msgbox  intColumnCount
While not rs.Eof
   intRowCount = intRowCount +1
   rs.MoveNext
Wend
msgbox intRowCount
 If intRowCount > 0 Then
    rs.ReQuery
    ReDim ExpectedArray(intRowCount ,intColumnCount )
 intRowCount = 0

'i=0
While not rs.Eof
 
For intCounter = 0 to intColumnCount -1
   
 If IsNull(rs(intCounter)) Then
     ExpectedArray(intRowCount, intCounter ) = "ddddd"
 Else
        ExpectedArray(intRowCount, intCounter ) = rs(intCounter)
 End If

       print  intRowCount  & "  " & intCounter & "  " &  ExpectedArray(intRowCount, intCounter )

Next

intRowCount = intRowCount +1
       rs.MoveNext
   Wend
     End If
  
  rs.close
  Set rs = Nothing
  Conn.Close
  Set Conn = Nothing
 
  If intRowCount > 0 Then
   ReadDataFromExcel = ExpectedArray
  Else
   ReadDataFromExcel = -1
  End If
'do while not rs.eof

 End Function

[Atom290]

Hiya Shahid

Are you using any filtering?

I like the idea of pulling the data out of the spreadsheet, especially for using the full extent of a select statement and filtering.

But if you are only doing a Select * is there any point?

Personally I found the pros were well outweighed by the cons with problems of data being returned in different formats. Why not use Access instead? At least then you can stipulate the field formats.

The worst thing though...and not sure if anyone else came across this...is that I tried to use ADO to pull data from within VBA for Excel. This was the worst thing going as no matter what I did it wouldnt kill the object. The computer ended up on its knees!! it was going that slow!

Could you not use an array to pull the data back? Even using the ADO I think it still physically loads the xl app.

Ive looked through the code and dont see any problems with it

[dee-u]

I haven't used a recordset to retrieve items from an excel, what I did was to loop through the rows and columns to get the values, as such I am guaranteed that I get all the values...

[anhn]

Without specifying data types, when reading an Excel table, JET will use some top rows of data to guess and set data type for each column.
If a column was given a Double data type and later in a row the value in that column is not numeric, that value will be ignored (set to Null).

Why use a too long and hard way to get data from an Excel sheet like that while you can have another way to do the job much simpler.

Try this:

Function ExcelData(sFileNameAndPath As String, sSheetName As String) As Variant
   '-- return a 2-D array if no error happens
   Dim xlApp As Object
   Dim wb As Object
   
   '-- may need error handler
   Set xlApp = CreateObject("Excel.Application")
   Set wb = xlApp.Workbooks.Open(sFileNameAndPath)
   ExcelData = wb.Worksheets(sSheetName).UsedRange
   wb.Close False
   Set wb = Nothing
   xlApp.Quit
   Set xlApp = Nothing
End Function

Sub ReadDataFromExcel()
   Dim r As Long, c As Integer
   Dim ExpectedArray As Variant
   
   ExpectedArray = ExcelData("H:\Book2.xls", "sheet1")
   For r = 1 To UBound(ExpectedArray, 1)
      For c = 1 To UBound(ExpectedArray, 2)
         Debug.Print ExpectedArray(r, c),
      Next
      Debug.Print
   Next
End Sub

[ColorBLIND]

Hi All,

i came across this forum, i have a similar problem. the code below almost works perfectly, except that i get a null value sometimes even though the cell has a valid value. Unfortunately, i have to use recordset to retrieve values from xls.

what can i do to retrieve the valid data instead of null value when the cell contains a valid data ie text, and numeric.

thanks.

With cn
.Provider = "MSDASQL"
.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=" & strPath & " ; ReadOnly=false;MaxScanRows= 0;"
.Open
End With
' Specify Sheet Name and Cell Range
strSQL = "SELECT * FROM [Sheet1$]"
rs.Open strSQL, cn

[shahid09]

you can check something like this

If IsNull(CellValue) Then
dicAct(Val) = ""
Else
dicAct(Val) = CellValue
End If

[ColorBLIND]

thanks shahid09 but
my problem is,
i still get a null value in my recordset even though i expect to get an integer or double. The top most row is a column title which is a text.
i read that the top cell's data type will used and values other than the data type will be given a null value.
i would have been OK if the all datatypes will be text like the column title.