Results 1 to 8 of 8
  1. #1

    Not able to Retrieve value from Excel sheet

    Hi ,

    I am connecting to Excel as a database using ADODB object.

    My Excel sheet has 10 columns in which 1 column has date values and all other columns as number values

    I am able to connect to Excel ,execute Query get values.
    But if use date value in where condition
    the Query is not executing,giving an error as Data type mismatch error.

    can someone help me in soving this issue

  2. #2
    When you retrieving data from excel it retrieves as text, so you have to convert....

    Ravi Konka -

    ***** Help others it will help you *****

  3. #3
    To give you accurate answer to your problem, post the piece of code where you are getting the problem, and sample test data.

    So that we can find out where exactly you are going wrong.

  4. #4
    In excel field use single quote in front of date as below
    Today is always better than Tomorrow
    My Blog
    My LinkedIn

  5. #5
    Hi udaynem,

    Thank you for the Reply.
    PFB for the code i used.
    In wages22345.xls sheet 7 columns are there,6 columns has values as numbers and once column has date values

    Set sn = CreateObject("adodb.connection")
    Set rs = CreateObject("adodb.recordset")
    sn.Open "provider=microsoft.jet.oledb.4.0;data source = C:\BPM-KOM\MANual\wages22345.xls;extended properties =Excel 8.0;"

    sqlstr = "select * from [wages22345.xls$] where col1_head not in (701,345,445) and date_val='01/01/2012';"
    rs.Open sqlstr, sn
    Range("a45").CopyFromRecordset rs

    Set rs = Nothing
    Set cn = Nothing

    the above code is giving an error datatype mismatch in criteria mismatch at line rs.Open sqlstr, sn

    PLease let me know how to handle this

  6. #6
    Hi venuqtp,

    Thank u for the Reply.I tried with single quote .It is not working still.

    Thanks & Regards,

  7. #7
    couple of points to mention.
    recordset.open method needs different parameters than the parameters you are passing.

    recordset.Open Source, ActiveConnection, CursorType, LockType, Options

    sql query statement need not include ";" at the end of the statement

    I have a sample code in my blog, with connection, command and recordset objects.

  8. #8
    I tried the following code and it worked for me. Give a try:

    Set oCmd=createobject("ADODB.Command")
    Set oRS=createobject("ADODB.RecordSet")

    'Either of the connection stings below works.

    sCon="Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=D:\Programming Samples\QTP\SampleXL.xls;"
    'sCon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Programming Samples\QTP\SampleXL.xls;Extended Properties=""Excel 8.0;"""
    sQry="select * from [Sheet1$]"

    oCmd.ActiveConnection = sCon
    Set oRS=oCmd.Execute
    While not oRS.EOF
    print oRS.Fields(1).Value


    Set oCmd=nothing
    Set oRS=nothing



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
BetaSoft Inc.
All times are GMT -8. The time now is 07:41 PM.

Copyright BetaSoft Inc.