Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using ado to query the contents of a spreadsheet
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import New Database Query (Union Query) in Spreadsheet | Excel Discussion (Misc queries) | |||
Excel cell contents to query criteria | Excel Discussion (Misc queries) | |||
How do I use the ActiveCell's contents as a string for a web query | Excel Programming | |||
Web query - using cell contents as part of URL | Excel Programming | |||
CONTENTS OF TEXTBOX TO BE THE CRITERIA ON A DATABASE QUERY | Excel Programming |