[Wim Venema]

Hi,

I often use macros in Excel and have many sheets that I use as template to
fill-in automatically figures from a MSSQL database.
I want to integrate the templates into the macro itself.
This makes the deployment of the application easier by just updating one Excel add-in.
Does anyone know how automatically script an exsisting Excel Sheet into VBA (including, formulas, graphs etc)?
This will save me a lot of work converting the templates into VBA.

[savbill]

The best approach to this is to separate your VB Procedures from the data and the templates, then refer to the template in the code as a workbook.Worksheet or use ActiveSheet ActiveWorkbook references in your code. What you will end up with is a standard Template that will be used to create each report, a code file may be an add-in, and your database which you are already referencing. You may have to do some jockying around depending on how you are calling the data if you are using GetExternal Data with MS Query. But the idea is to separate the code / data / display template into 3 parts. This will make it much easier to maintian your process and distribute it.

Here's an example of code reference to the target worksheet where the data will be written:

strTemplate = Thisworkbook.Path & "MyCoolTemplate.xls"
Workbooks.Open(strTemlate)
ActiveWorkbook.Sheets(1).Activate
Set targWS = ActiveWorksheet

' Write some headers
For i = 1 to 4
targWS.Cells(1, i).Value = "Fields" & i
Next

[shades]

Excellent advice, Bill! Same goes for data setup within Excel itself. Makes for much more flexible and powerful approach.