Reading excel cells with ADO returns null value 1

[Bluejay07]

Hello,

I've created a program that reads an excel file using ado. My problem is that several cells are returning a null value when then cell is not empty. I'm not sure what I'm doing wrong.

While trying to research this issue I came across several sites that haven't seem to help, including the following

http://www.pcreview.co.uk/forums/re...a-oledb-net-returns-null-values-t1238839.html

http://www.tek-tips.com/viewthread.cfm?qid=1677707

http://support.microsoft.com/kb/209805

I did try adding the IMEX=1 and received the ISAM error. Manually registering msexcl40.dll, didn't help.

Attached is a small sample of the file. All seems fine until I am processing cell B7. I should point out that all cells have not been formatted and are currently listed as 'General'.

I am using a very basic form. A text box that displays the full path of the file, a browse button to obtain the file and a process button.
Here is the basic code I am using. I've tried to include all relavant sections

Private m_strWorksheetNames As String
Private m_strSheetDetails() As SheetDetails
'// Spreadsheet information.
Public Type SheetDetails
   InvDate As Date
   CustID As String
   InvNum As String
   Description As String
End Type

Private Sub lvbProcess_Click()
   '// Process the spreadsheet and import into Accpac.
   
   Dim rs As ADODB.Recordset
   Dim Conn As ADODB.Connection
   Dim i As Integer
   Dim j As Integer
   Dim k As Integer
   Dim l_strSheetNamesArr() As String
   
   On Error GoTo ERR_Handler
   
   Get_WorksheetNames
   
   Set rs = New ADODB.Recordset
   Set Conn = New ADODB.Connection
   Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Trim$(pzeSource.Text) & ";Extended Properties=Excel 8.0;Persist Security Info=False"
   'Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Trim$(pzeSource.Text) & ";Extended Properties=Excel 8.0;IMEX=1"
   Conn.ConnectionTimeout = 40
   Conn.Open
   
   l_strSheetNamesArr = Split(m_strWorksheetNames, "|")
   For i = 0 To UBound(l_strSheetNamesArr)
   
      rs.Open "SELECT * FROM [" & l_strSheetNamesArr(i) & "$]", Conn, adOpenStatic, adLockOptimistic
      If Not (rs.BOF And rs.EOF) Then
         rs.MoveFirst
         
         Erase m_strSheetDetails
         
         '// Iterate through the rows.
         For j = 0 To rs.RecordCount - 1
            '// Iterate through the columns.
            For k = 0 To rs.Fields.Count - 1

               If IsDate(rs.Fields(k).Value) Then
                  If ArrayHasElements(ArrPtr(m_strSheetDetails())) Then
                     ReDim Preserve m_strSheetDetails(UBound(m_strSheetDetails) + 1) As SheetDetails
                  Else
                     ReDim m_strSheetDetails(0) As SheetDetails
                  End If
               
                  m_strSheetDetails(UBound(m_strSheetDetails)).InvDate = rs.Fields(k).Value

                  '// This entry is sometimes blank.
                  m_strSheetDetails(UBound(m_strSheetDetails)).CustID = rs.Fields(k + 1).Value
                  m_strSheetDetails(UBound(m_strSheetDetails)).InvNum = rs.Fields(k + 2).Value
                  m_strSheetDetails(UBound(m_strSheetDetails)).Description = rs.Fields(k + 3).Value
                  
                  Exit For
               End If
            Next k
            rs.MoveNext
         Next j
      End If
   Next i
   Exit Sub
    
ERR_Handler:
   If Err.Number = 94 Then    '// Invalid use of Null.
      Resume Next
   Else
      ErrorMessenger Err.Number, Err.Description, "lvbProcess_Click"
   End If
End Sub

Private Sub Get_WorksheetNames()
'// Worksheet may have multiple tabs.
    Dim objExcel As Excel.Application
    Dim objWorkBook As Excel.Workbook
    Dim totalWorkSheets As Excel.Worksheet
    Dim objWorkSheets As Excel.Worksheet
    'Dim ExcelSheetName As String

    Set objExcel = CreateObject("Excel.Application")
    Set objWorkBook = objExcel.Workbooks.Open(pzeSource.Text)

    ' this code gets the names off all the worksheets
    For Each totalWorkSheets In objExcel.ActiveWorkbook.Worksheets
      'ExcelSheetName = totalWorkSheets.Name
      If Trim$(m_strWorksheetNames) = "" Then
         m_strWorksheetNames = totalWorkSheets.Name
      Else
         m_strWorksheetNames = m_strWorksheetNames & "|" & totalWorkSheets.Name
      End If
    Next totalWorkSheets
    
    objWorkBook.Close
    objExcel.Quit
End Sub

I have a lot of experience with VB6 and recordsets, but very little with VB6, recordsets and excel.

Could anyone help me determine why a null value is being returned when there is an actual value?

Thanks.



If at first you don't succeed, then sky diving wasn't meant for you!

 

[BobRodes]

You might want to try reposting in the VBA forum (forum707), where there are people that have more direct experience with Excel.

An unforeseen consequence of the information revolution has been the exponential propagation of human error.

 

[Bluejay07]

Hi Bob,
Thanks for the response. I've been thinking of doing that although the code is for VB6 and not VBA. I've often gotten great responses from this forum so I wanted to try this forum first.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Swi]

Should you not have quotes around the Extended Properties properties of the connection string?

Not sure if that is your issue but check out the following link.

http://www.connectionstrings.com/excel#p21

Swi

 

[Bluejay07]

Sorry for a really late reply (I thought I replied. Apparently I didn't).
It does seem like I required the quotations for this to work for me. Thanks for pointing that out Swi.

If at first you don't succeed, then sky diving wasn't meant for you!