[SOLVED] Activating Excel Template .xlt

I am trying to figure out how to get a template workbook to activate using macros.

I have used this in the past...



Workbooks("filename.xls").Activate

...but this does not work because the .xlt file always opens up with a new name, ie. MacroTemplate.xlt would open as MacroTemplate1.xlt if opened the first time, and the number would go up each time it is opened until Excel has been restarted.

Is there a different code that can be used or a way that I can type in the filename that it will always recognize the workbook, no matter what number is after "MacroTemplate" ...?

I did my research but couldn't find anything, so maybe someone on here has the solution I am looking for.

Thanks.

-Amy Kate

Something like this

'Callback for btnNew onAction
Sub Open_Template()

    Select Case Application.Version
        Case Is >= 12: sExt = ".xltm"
        Case Else: sExt = ".xlt"
    End Select

    Dim sWb    As String
   Const sAppName as String ="YourFile"
'you need to make sure the Path is correct
    sWb = ThisWorkbook.Path & Application.PathSeparator & sAppname & sExt
    Set wb = Workbooks.Open(sWb)
    frmSetUp.Show
End Sub

This will always open the Template,not files created from it

You need to change the name to that of your template

So how have you used the code?

That defines the Path not the name,what is the file called?

Set wb = Workbooks.Open("C:\Documents and Settings\amy\Desktop\MacroTemplate" & sExt)

Never used a Mac. Reckon it would if that were the path to your desktop, and the path separator were correct.

Try this

Option Explicit


Sub Open_Template()
    Dim sWb As String
    Const sAppName As String = "MacroTemplate"
    Const sPath As String = "Lorencz/Users/amykate/Desktop"
    sWb = sPath & Application.PathSeparator & sAppName & sExt

    Select Case Application.Version
    Case Is >= 12: sExt = ".xltm"
    Case Else: sExt = ".xlt"
    End Select

    Set wb = Workbooks.Open(sWb)
    frmSetUp.Show
End Sub

Originally Posted by royUK

Try this

Option Explicit


Sub Open_Template()
    Dim sWb As String
    Const sAppName As String = "MacroTemplate"
    Const sPath As String = "Lorencz/Users/amykate/Desktop"
    sWb = sPath & Application.PathSeparator & sAppName & sExt

    Select Case Application.Version
    Case Is >= 12: sExt = ".xltm"
    Case Else: sExt = ".xlt"
    End Select

    Set wb = Workbooks.Open(sWb)
    frmSetUp.Show
End Sub

I used this exactly, but I got an error "Variable not defined"

Take a breath, Amy.

How about posting the code you have, and explain where it errors?

I got an error "Variable not defined"

Because you don't declare your variables, and Option Explicit requires that you do (and you should).

Sorry, I am not trying to come off as ungrateful, I am just simply frustrated...not at you or anyone on here, but excel in general has me quite flustered right about now.

I did edit that reply about the error to include the quote of the code I was given that is exactly how I placed it in my module.

What code are you using now, Amy?

Option Explicit


Sub Open_Template()
    Dim sWb As String
    Const sAppName As String = "MacroTemplate"
    Const sPath As String = "Lorencz/Users/amykate/Desktop"
    sWb = sPath & Application.PathSeparator & sAppName & sExt

    Select Case Application.Version
    Case Is >= 12: sExt = ".xltm"
    Case Else: sExt = ".xlt"
    End Select

    Set wb = Workbooks.Open(sWb)
    frmSetUp.Show
End Sub

'Tis true, VBA is different on Mac than PC.

First, you haven't declared variable sExt.

Second, you use it before you initialize it.

Try this (which wouldn't work on a PC, because a path requires a drive letter):

Sub OpenTemplate()
    Const sFile As String = "Lorencz/Users/amykate/Desktop/MacroTemplate"
    Dim wkb     As Workbook
    Dim sExt    As String
    
    sExt = IIf(Application.Version >= 12, ".xltm", ".xlt")
    Set wkb = Workbooks.Open(sFile & sExt)
    
    frmSetUp.Show
End Sub

Back to the fact that I've never used a Mac ...

Paste the path Lorencz/Users/amykate/Desktop/MacroTemplate.xlt verbatim into a file browser -- does the file open?

Sorry, Amy, you need a Mac person to help.

Jump in here, T.

Don't give up. mikerickson or romperstomper can give you an answer.

Might help if you had stated the the fact that you are using a Mac. This might work,but I haven't used a mac

Option Explicit
Sub Open_Template()
    Dim wb As Workbook
    Dim sWb As String
    Dim sExt As String
    Const sAppName As String = "MacroTemplate"
    Const sPath As String = "Lorencz/Users/amykate/Desktop"
    sWb = sPath & Application.PathSeparator & sAppName & sExt

    Select Case Application.Version
    Case Is >= 12: sExt = ".xltm"
    Case Else: sExt = ".xlt"
    End Select

    Set wb = Workbooks.Open(sWb)
End Sub

One problem is that the routine uses sExt to build the path string before sExt has been assigned a value.

This worked on my Mac

Sub Open_Template()
    Dim wb As Workbook
    Dim sWb As String
    Dim sExt As String
    Const sAppName As String = "MacTemplate"
    Const sPath As String = "Macintosh HD:Users:merickson:Desktop:trying"
   
    Select Case Application.Version
        Case Is >= 12: sExt = ".xltm"
        Case Else: sExt = ".xlt"
    End Select
    
     sWb = sPath & Application.PathSeparator & sAppName & sExt
     
    Set wb = Workbooks.Open(sWb)
End Sub

To find your sPath constant, you can use

Range("a1") = MacScript("choose folder")

and remove the last ":"

Or you could try

Sub trial()
    Workbooks.Open ("MacTemplate.xlt")
End Sub

Are you sure that MacroTemplate.xlt is on the Desktop. Mine are automaticly put in the Template folder, unless I specify a different location.

Also, have you tried

Sub makeOpen()
   Dim wb as Workbook
   
   Set wb = Workbooks.Open("MacroTemplate.xlt")
End Sub

(The failed example in the OP used .xls extention)

Try this,

Sub FindTemplateToOpen()
    Dim templatePath as String
    templatePath = Application.GetOpenFileName
    If templatePath = "False" Then
        MsgBox "Cancel pressed"
    Else
        Range("A1").Value = templatePath
        Workbooks.Open(templatePath)
    End If
End Sub

Then, if it opens the template properly, you can copy from A1 to paste into

Sub OpenMyTemplate()
    Workbooks.Open("The Path copied from A1")
End Sub