Forum Discussion

teemu_selkala's avatar
teemu_selkala
Occasional Contributor
13 years ago

Unable to store database tablet values to excel

Hi,



I have (again) a problem :) - sorry for flooding the forums. I have a scenario where I have a large set of SQL queries stored in an excel. I would like to run through them and store the values to a new excel but I feel quite dump since I seemingly cannot get a working solution how to get all those tables from DB stored up for saving. Code follows... code runs but I don't get anything when I try to log the results  - any hints, should i resturcture completely or anything really is appriachiated a lot :).




function checkout()



{



var Excel = Sys.OleObject("Excel.Application")



var searchSQL



var result =""



Excel.Workbooks.Open("c:\\MyExcel.xlsx")



var RowsCount = Excel.ActiveSheet.UsedRange.Rows.Count;



Log.Message(RowsCount)



for(i=1;i<RowsCount;i++)



{



searchSQL = Excel.Cells(i,1);



result = result + RunQuery(searchSQL);



}



RecSet = result;



RecSet.Save("c:\\MyAnother.xml",1)



Excel.Quit()



}



function RunQuery(aSQL)



{



var Conn;



Conn= ADO.CreateConnection()



Conn.ConnectionString="my connection string";



Conn.Open();



Cmd = ADO.CreateCommand();



Cmd.ActiveConnection = Conn;



Cmd.CommandType = adCmdText;



Cmd.CommandText = VarToStr(aSQL)



RecSet = Cmd.Execute();



var Result = RecSet



Log.Message(RecSet)



Conn.Close();



return(Result);



}


3 Replies

  • teemu_selkala's avatar
    teemu_selkala
    Occasional Contributor
    13 years ago
    As a workaround I create file from each SQL query but if someone has hints how to get them stored for one file that would be great :)
  • JavierCollRodri's avatar
    JavierCollRodri
    Contributor
    13 years ago
    Hi Teelmu,



    Let's see if I can help there. Here is my suggestion:



    In your function, you open a Excel file and execute a SQL query for each one of them. You add the results to a variable and once you are finished, you save it to a XML file.



    What you want is to write the results to an excel file, each result on a row, right?



    Please try this: In your function, open another Excel file at the same time*. Then, in the for loop, for each result, just write in the same coordinates cell the result (I'm asuming you want to save DB results in a single row)



    ...

    var excelQuerys = Excel.Workbooks.Open("c:\\MyExcel.xlsx");

    var excelResults = Excel.Workbooks.Open("c:\\Results.xlsx");

    ...


     for (i = 1; i < RowsCount; i++) {


       searchSQL = excelQuerys.ActiveSheet.Cells(i, 1);


       result = RunQuery(searchSQL);

       excelResults.ActiveSheet.Cells(i, 1).Value = VarToStr(result);


     }


     

     I haven't tested it, but that's the general idea. If you want to store DB results as a table, not as a string, it will be a bit more tricky, but it can be done.

     

     I hope it helped!

     

     *If you can't open thow excel files at the same time, just open one, store the results to an array, close it, open a new one and store them later in a for loop.

  • teemu_selkala's avatar
    teemu_selkala
    Occasional Contributor
    13 years ago
    Thanks a million :) - I'll be sure to try it and will post here how it goes.



    Cheers,



    Teemu