Excel recordset returns Null value

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

Code:

---

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

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

Thank u atom.
Here is the full code

Thanks
Shahid

Code:

---


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

---

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 :)

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... :)

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:

Code:

---

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

---

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

you can check something like this

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

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.