Post reply

DTS & vb Script

  • December 30, 2004 at 8:10 am

    #61205

    This column DTS Source("system") contains 3 rows.  How do I get each row value from the column?

    I want to Export row values from DTS Source("system") in different cells in Excel:

    osheet.range("A20").value = DTS Source("system") 

    osheet.range("C21").value = DTS Source("system")

    osheet.range("A25").value = DTS Source("system")

    But this way doesn't work. Do anyone know another way to do this?

     

    Thanks

     

  • December 31, 2004 at 3:54 am

    #535492

    Not as easy as it looks. If you only have 3 rows to export across, the easiest way is to do a select to return one row, then use the code you have to populate xcl, select the next row in another step and use your code to populate xcl....etc.

    If you want to do it dynamically, you need to build a recordset. Search "CreateObject("ADODB.Recordset")" for tips in http://www.sqldts.com. You basically loop through the recordset one record at a time and then populate xcl with the records. Can be tricky. I have used it before to loop through a recordset to produce different spreadsheets for each salesmen.

    Another way, export them to an xcl file and then use vb within xcl to copy the values in your destination xcl. A little messy though.

    All depends really on how many rows you envisage dealing with.

    ------------------------------
    The Users are always right - when I'm not wrong!

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply