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 followinghttp://www.pcreview.co.uk/forums/reading-excel-tables-via-oledb-net-returns-null-values-t1238839.htmlhttp://www.tek-tips.com/viewthread.cfm?qid=1677707http://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
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
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
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
'// 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
ReDim m_strSheetDetails(0) As SheetDetails
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
If Err.Number = 94 Then '// Invalid use of Null.
ErrorMessenger Err.Number, Err.Description, "lvbProcess_Click"
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
m_strWorksheetNames = m_strWorksheetNames & "|" & totalWorkSheets.Name
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?
If at first you don't succeed, then sky diving wasn't meant for you!