SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 5 of 5
  1. #1
    Senior Member
    Join Date
    Jun 2005
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    problem with excel recordset

    </font><blockquote><font size="1" face="Verdana, Arial, Helvetica">code:</font><hr /><pre style="font-size:x-small; font-family: monospace;">Function getxlrecordset(xl_file_name,xl_sheetname)
    xl_file_name=trim(xl_file_name)
    xl_sheetname=trim(xl_sheetname)
    strConnDest=&quot;DRIVER={Microsoft Excel Driver (*.xls)};DBQ=&quot;&amp;xl_file_name&amp;&quot;;&q uot;
    set oConnDest =createobject(&quot;adodb.connection&quot
    oConnDest.Open strConnDest
    Set rsXL=oConnDest.Execute(&quot;SELECT * FROM [&quot;&amp;xl_sheetname&amp;&quot;$]&quot
    getxlrecordset=rsXL
    End function



    xl_file_name=&quot;D:\ETL\Trials\test.xls&quot;
    xl_sheetname=&quot;screen&quot;
    set rs=getxlrecordset(xl_file_name,xl_sheetname)
    While not (rs.EOF)
    msgbox rs(&quot;SQL_Query&quot
    msgbox rs(&quot;screen_name&quot
    rs.movenext
    wend</pre><hr /></blockquote><font size="2" face="Verdana, Arial, Helvetica">On execution of the above code I only get the first record and get 2 errors

    </font><blockquote><font size="1" face="Verdana, Arial, Helvetica">code:</font><hr /><pre style="font-size:x-small; font-family: monospace;">Object doesn't support this property or method: 'rs.EOF'
    Line (38): &quot;While not (rs.EOF)&quot;.</pre><hr /></blockquote><font size="2" face="Verdana, Arial, Helvetica">AND

    </font><blockquote><font size="1" face="Verdana, Arial, Helvetica">code:</font><hr /><pre style="font-size:x-small; font-family: monospace;">Object doesn't support this property or method: 'rs.movenext'
    Line (41): &quot;rs.movenext&quot;. </pre><hr /></blockquote><font size="2" face="Verdana, Arial, Helvetica">However if i parse the recordset object that was used to execute the query ie rsXL there is no problem and i can get all the records.

    Any ideas??

  2. #2
    Junior Member
    Join Date
    Apr 2004
    Location
    London
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: problem with excel recordset

    Hello Friend,

    I didnot work with Excel sheet but i have worked with SQL.
    rs.movenext is working fine for me.

    I am trying to get the data from the Datatable and putting it in the Excel Sheet

    Here is the code I have used:

    Dim Con
    Dim MyDSN
    Dim Stmt
    Dim rs
    Dim intStart = 1

    Set con = CreateObject("ADODB.Connection")

    myDSN= "Provider=MSDASQL.1;Password=&lt;pwd&gt;;PersistSe curity Info=True;User ID=&lt;userid&gt;;Data Source=&lt;DSN Name&gt;"

    con.Open myDSN

    stmt= " Your SQL Query "

    set rs = con.execute(stmt)

    Do Until rs.EOF
    DataTable.GetSheet ("Datasheet Name&gt;").SetCurrentRow(intStart)
    Datatable.Value("&lt;Col Name&gt;", "&lt;Datasheet Name&gt;") =rs(0)
    rs.movenext
    intStart = intStart + 1
    Loop
    Best Wishes,
    Kiran

  3. #3
    Senior Member
    Join Date
    Jun 2005
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: problem with excel recordset

    hi kiran,

    I think the problem is when i am returning the recordset from the function. It works when i parse the recordset that i executed the sql on directly.

    Any one knows how to return a recordset from a function?

  4. #4
    Member
    Join Date
    Oct 2002
    Location
    NJ, USA
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: problem with excel recordset

    In the function, to return the recordset, use
    </font><blockquote><font size="1" face="Verdana, Arial, Helvetica">code:</font><hr /><pre style="font-size:x-small; font-family: monospace;">Set getXLRecordset = rsXL</pre><hr /></blockquote><font size="2" face="Verdana, Arial, Helvetica">

  5. #5
    Senior Member
    Join Date
    Jun 2005
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: problem with excel recordset

    Works like a charm, thanks partha

 

 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Search Engine Optimisation provided by DragonByte SEO v2.0.36 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Resources saved on this page: MySQL 10.71%
vBulletin Optimisation provided by vB Optimise v2.6.4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.2.8 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominate (Lite) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 02:17 AM.

Copyright BetaSoft Inc.