Create Excelfile of a template 1

[Sanni1]

I have to export data in a excel file which has a special layout for an other system.

The result excel file should have in the first row the header of the second data row. The second row contains sums.

In the third row is another header row. The rows belows contains the data.

I would like to use an excel template an fill the file below third row.

 

[jhall01]

This can be done using a little ActiveX script.

Take a look at the following link for help:

http://sqljunkies.com/How To/A8CB0AFE-D143-4B49-B865-4FBBFEDFCCD7.scuk

 

[Sanni1]

Thanks

I will test this suggestion.

 

[Sanni1]

Now i can create a excel file with Active X in DTS. But i have still the problem, that the output to excel should start in the thrird row.

 

[jhall01]

putting your results on the 3rd row will be a little more difficult. Have you considered moving your results to a seperate sheet and updating the formula's to look at the seperate sheet. This would be the easiest solution.

Another possibility is to go the other direction. Code in VBA for excel to pull from the SQL server and display the results in the spreadsheet. This can be done using ADO and the offset object of VBA

A third possiblity would be to use a hybrid of what you have and ADO. And where you open the sheet through DTS make a ADO connection fill the sheet and then save the spreadsheet, close and you are done.

Hope this helps.

Jon

 

[Sanni1]

I will use another ActiveX script, which moves the data two row below. Than i set the other values.

With my question i wanted to prevent, that there is a simple solution i don't know.

Ulrich