Greetings,

I am trying to access an excel spreadsheet using ado and one of the
fields have a zipcode in it. There is a number of tabs or worksheets in
this workbook and I am seeing some strange results that I need some help on.


In some instances the column in question for the zipcode has 10
characters or 5 characters (i.e. xxxxx-xxxx or xxxxx). On one worksheet
when I look at the datasource it shows up as null for a zipcode entered
in as xxxxx-xxxx, and then another worksheet it will show up perfectly fine.

This is the query I am using

"SELECT * FROM [" & aStateNames(iStateLoop) & "$]

where aStateNames refers to the name of the worksheets.




And this is the way I am calling the query

Function DbQuery(strSql As String, strFile As String, Optional
bolConnect As Boolean = False) As Variant

'Handle any errors found
On Error GoTo ErrorHandler
'Local variables
Dim objAdoRs As ADODB.Recordset
Dim strConn As String
'Create the reference to the ado recordset
Set objAdoRs = New ADODB.Recordset
'Ado recordset object
With objAdoRs
'SEt the object properties
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockBatchOptimistic
'Set the connection string
If Not bolConnect Then
strConn = "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ="
& strFile
Else
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"""
End If
'Open the recordset
.Open strSql, strConn
'.Save "c:\testjrd.xml", adPersistXML

'Check to see if the recordset brought back a list of records
If Not .EOF And Not .BOF Then
'Return the recordset as a 2D array
DbQuery = .GetRows
Else
'Nothing was found, so return nothing
DbQuery = ""
End If
End With
'Free the object
Set objAdoRs = Nothing
ExitFunc:
'Exit the function
Exit Function
ErrorHandler:
'Print out the error
Debug.Print strSql + " " + Err.Description + " " + _
Err.Source, vbCritical, "Import"
Err.Clear
Resume ExitFunc
End Function


Does any one have any idea on what is causing this weird behaviour and
what the solution could be to resolve it.