Macro to open template

[aa2000]

Hi again (Last question, I promise....maybe)

I am currently have a macro which opens a new workbook and puts in sheets depending on certain conditions, using the code below:

Application.SheetsInNewWorkbook = 1
Set Newbook = Workbooks.Add

With Newbook
                    .SaveAs Filename:=ThisWorkbook.Path & "\" & ActiveWorkbook.Sheets.Item(10).name & "_to_" & ActiveWorkbook.Sheets.Item(1).name & ".xls"
                    End With
'^line to save book

How can this be modified to open a specific template rather than the newbook?

Additionally the template contains some macros. How can i get the above macro to run these once the file has been saved.

The macro to run is just:

Sub Compare ()
Call comp1
Call comp2
Call comp3
End Sub

Thanks!

 

[perco754]

I'm not 100% sure of what exactly you're asking for but never the less here are some thoughts.

1. Open a specific "template" by simply provide the filename and setting the object Newbook to point to that template instead of a new workbook
2. You can e.g. call the macros using your Newbook object.

In my example I have two workbooks Book1.xls and Book2.xls. In Book1 there's a macro Macro1 and in Book2 there's another macro Macro2.

I simply call Macro1 in Book1.xls through the object Newbook as:

Dim NewBook As Workbook

Sub Macro2()
    Set Newbook = Workbooks(1)
    Application.Run (Newbook.Name & "!Macro1")
End Sub

You'll have to replace the opening procedure to anyone suitable to your needs e.g. by passing a filepath etc.

BR,
Perco

 

[aa2000]

Thank you Perco, that was what I was asking, and I'll try to run this to see if it works.

Cheers

 

[aa2000]

Ok so I have set NewBook to point to the particular template like so:

Set NewBook = Workbooks(ThisWorkbook.Path & "\" & "ImportTemplate.xlt").Add

However I get Runtime error 9 "Subscript out of range". Whats wrong with this code now?

If I try it with no "" around the .xlt file I get and Object required error instead.

Cheers

 

[aa2000]

Anybody got any ideas on this?

Cheers

 

[aa2000]

Nevermind, guys I found another way to achieve what I wanted. Thanks

Cheers

 

[perco754]

Use this code:

    Dim NewBook As Workbook
    Dim fileName As String
    
        fileName = ThisWorkbook.Path & "\" & "ImportTemplate.xlt"
        Set NewBook = Application.Workbooks.Add(fileName)

Ok so I have set NewBook to point to the particular template like so:

Set NewBook = Workbooks(ThisWorkbook.Path & "\" & "ImportTemplate.xlt").Add

However I get Runtime error 9 "Subscript out of range". Whats wrong with this code now?

If I try it with no "" around the .xlt file I get and Object required error instead.

Cheers