extract data from sql server to Excel and Excel ADO connection

this is a 2 part question. as an automated job, i need to extract data from sql server and insert into an existing worksheet "SEC_RPT_MIR_EXT_DETAIL_FINAL" in an excel template.  the excel template contains 10 sheets each with tables that drive embedded charts.

The data from the "SEC_RPT_MIR_EXT_DETAIL_FINAL" sheet will be the source (copied to via vba) of the data in the 10 sheets.
i know there are workarounds but i want an elegant solution.

q1) if i use dts data pump to that worksheet in the excel file, i get an error when i try to clear the worksheet via execute sql task "deleting data from a linked table is NOT supported by this ISAM"
so i tried to delete the sheet (table) via the same task but got another error.

so i tried to insert data into the sheet via active x with the statement:

INSERT INTO [SEC_RPT_MIR_EXT_DETAIL_FINAL$] IN '[Excel 8.0;Database=E:\MIS_OUTPUT\SECURITISATION\REPORTS\SEC_MIR_EXTERNAL_TEMPLATE.XLS]' SELECT * FROM SECURITISATION_RPT_MIR_EXTERNAL_DETAIL_FINAL

but also get errors. errors like "not updateable query", "error in the IN clause" etc
i used variations of single/double quotes etc but still error.

can anyone get the above statement working?

q2) once the sql data is inserted into that sheet, i need to get relevant data from the "SEC_RPT_MIR_EXT_DETAIL_FINAL" sheet into the appropriate excel sheets that drive their respective charts.
because there are some many ranges involved my first thought was to use ado connection. i couldn't readily find code that uses ADO connection to a particular sheet within the same excel file (rather than to an excel file specified with a path and file name). In Access this would be something like currentproject.connection etc.

is there an equivalent to currentproject.connection etc in Excel?

thanksStart Free Trial