I need to get a recordset into an Excel worksheet as follows.
Open the Workbook Template from my VB6 app as a new Workbook.
Insert into the pre-formatted worksheet the recordset with this exception.
The worksheet has 3 sections with the same format and the recordset data needs to be inserted to different areas depending on a column in the SQL db that can have 3 values, hence the 3 sections. So I need to insert rows based on that criteria. I also need to copy the formula if the insert function does not handle it. The VB6 app will have an ADODB recordset already open that I can call. Also the worksheet has columns as dividers so I must account for placing the SQL columns accordingly.
The worksheet template, SQL db and VB6 app are all built and functioning, just need to get the data to the spreadsheet.
That is a lot to ask help on I know
Thanks In Advance!

check out http://www.greggriffiths.org/webdev/both/excel/ which covers this kind of thing - form a VBScript -> Excel point of view - although some of the links may also be of interest and assistance.

You can drive this process from SQL Server provided you have a suitable driver installed (all you need is the Jet Access driver - it understands Excel files too). You can set-up a query to insert data into named ranges in a spreadsheet directly from a SQL Server stored procedure or DTS task.

Check out this recent thread in the SQL Server forum http://p2p.wrox.com/topic.asp?TOPIC_ID=7104 (it talks about transferring data the other way - from Excel to SQL - but the principle is the same).

If you want to do it via DTS you can set-up the Excel sheet as the destination and write a SQL query as the source.

Not sure about this bit "I also need to copy the formula if the insert function does not handle it." That may be something you have to do in your VB app after the data transfer.

hth
Phil

Guy, you can open a new Excel file, click Tools/Macro/Record New Macro£¬and open Tools/Macro/Visual Basic Editor to monitor your action. now back Excel worksheet, do what you want to do! at the end check sourcecode in VB editor, you will find all sourcecode what you want to get! I usually do VBA developement using this way, it's great!