add macros/vba programmatically

[NuJoizey]

Can someone tell me if the following is possible, and if so give me a general idea of how to accomplish?

I have a procedure that automatically creates a new blank spreadsheet and dumps data into it.

I would like the blank sheet to contain a button that fires a previously written subroutine/macro.

Is this possible to do programmatically?

Another way to ask: is there a way to dump a macro into a new workbook generated by vba from an existing workbook?

 

[PHV]

Why not using an .XLT ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NuJoizey]

thought of that, but am fuzzy about how it would work. Are you saying I could programmatically tell my workbook creation procedure to use the .xlt file with the written macro in it as a template to dump the data into?

 

[PHV]

Have a look at the Add method of the Workbooks object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NuJoizey]

thanks. will do

 

[NuJoizey]

ok, workbooks.add adds a workbook, but how do I use that method to create a workbook based on an xlt file that contains my prewritten macro?

 

[PHV]

No F1 nor F2 keys on your keyboard ?
Tip: the Workbooks.Add method admits an argument named Template ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NuJoizey]

i did the f1 - maybe i just missed it
will try again. thanks for the hints

 

[NuJoizey]

ok, when I try to do this way it works:

'Workbooks.Add ("C:\Documents and Settings\person\My Documents\folder\subfolder\myTemplate.xls")

but this way doesn't work - it errors out saying it can't find http:\\myDatabaseServer\Files\Reports\templates\myTemplate.xls

Workbooks.Add ("\\myDatabaseServer\Files\Reports\templates\myTemplate.xls")

what is the synatx to tell it a universal path name and have it not think it's http?

 

[Rofeu]

Hi,

if you still care to write the code programmatically, this is a good place to get some ideas about how to start:

http://www.cpearson.com/excel/vbe.htm

Cheers,

Roel

 

[NuJoizey]

thanks for the link. i figured out the above problem. The path was wrong. silly me.