Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Haaksbergen, Netherlands

    Question Unanswered: How to script an exisiting Excel sheet into VBA?


    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.

  2. #2
    Join Date
    Feb 2004
    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"
    Set targWS = ActiveWorksheet
    ' Write some headers
    For i = 1 to 4
    targWS.Cells(1, i).Value = "Fields" & i


  3. #3
    Join Date
    Oct 2003
    Excellent advice, Bill! Same goes for data setup within Excel itself. Makes for much more flexible and powerful approach.
    old, slow, and confused
    but at least I'm inconsistent!

    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts