Deploy Template With Vba

  • I recently deployed a single-page spreadsheet intended to be added to existing workbooks (it generates a hiearchical view of pricing in a WBS structure), and it includes some UDFs and subs.


    I was hoping I could save it as a tempate, but when inserted as a template, it didn't bring along its code module. I ended up instructing someone to copy the sheet to the intended workbook, and had them open the VBE to drag the module over. There is surely a better way!


    I would prefer not to train people to use add-ins or mess with Personal.xls.


    Thanks in advance for your suggestions.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: Deploy Template With Vba


    I don't have any thoughts right off but a few questions might help me think.


    Did you put the template in their XLStart folder? Maybe you could have it set to hide at load?


    How are they inserting the template?


    Maybe a method that imports modules might be worth exploring.

  • Re: Deploy Template With Vba


    Quote

    Did you put the template in their XLStart folder?


    I stuck it in my template folder, which has a link from XLSTART. I haven't sent this out as a template to anyone, since I tested it and it didn't work.

    Quote

    How are they inserting the template?


    I inserted it by right-clicking a tab in a spreadsheet, and did Insert >, which brought up a list of things, including my template.

    Quote

    Maybe a method that imports modules might be worth exploring.


    Maybe -- but I was hoping I am just having a senior moment ...

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: Deploy Template With Vba


    Quote

    Maybe only the Sheet1 object is being imported in your scenario?


    Maybe (didn't look), but I need the code module.


    I appreciate you looking, Kenneth.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: Deploy Template With Vba


    I think you would need to use code to import the module because afaik the only code that moves with a sheet would be in the sheet object. Can you create the workbook with the code etc, then set it to import the data sheets from the original, finally delete the original. A bit more complicated but easier than coding to the VBE & the VBA project could be protected.

  • Re: Deploy Template With Vba


    Quote from royUK

    Can you create the workbook with the code etc, then set it to import the data sheets from the original, finally delete the original.


    I don't know the structure of the larger workbooks that this single-sheet will be used in, and I was hoping for it to be more transparent to users than that.

    Quote from Dave Hawley

    Why not an Add-in. How hard can Tools>Add-ins be?


    Not hard, just never deployed one for other people to use. An add-in could readily copy the worksheet to the big workbook (or create it from scratch), but then the add-in would have to stay 'added in' to do the periodic processing. Reckon that's what I'll do.


    I was hoping to have a one-shot procedure that would insert the worksheet and code, without programming to the VBE (makes virus scanners nervous), and without users having to open the VBE to drag modules around (makes users nervous). Looks like that isn't going to happen.


    Kenneth, Roy, Dave, thank you for your suggestions.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: Deploy Template With Vba


    Chip's web site has some code that I was playing with. It might be possible to do something with that code. I may play with that idea sometime. I guess one might put code in the template to trigger on Open or something like that and then export and import the modules if they don't exist. Once the modules are imported, it would check for their existence prior to processing further for the next Open event..