Excel macro Auto start only in XLT file and not in XLS file.

Question

I am using and XLT that contains macros, it creates an XLS by open, and save it in a choosen directory, the problem is, the saved file contains the same macros as the XLT. Is there any chance to modify the proc to make it save the generated file without the macros?

best regards,

Thomas

Answer 1

There are a couple of ways of doing this. If you only need the worksheets and none of the code, then the simplest method is probably just to copy out the worksheets to a new workbook then save that:

Private Sub workbook_open()
    Dim wb As Workbook
    Dim saveName As String

    ThisWorkbook.Sheets.Copy
    Set wb = ActiveWorkbook

    saveName = Application.GetSaveAsFilename(fileFilter:="Excel Workbook (*.xls),     *.xls")

    If Not saveName = "False" Then
        wb.SaveAs saveName
    End If
End Sub

If some code is still required in the XLS then you would need to manipulate the VB environment directly to remove the parts you didn't need. This is a bit more involved and has some important restrictions/caveats, but it sounds like the simpler method illustrated above may suit your needs anyway.

Answer 2

If your question is about how to autostart a macro only when it is called from XLT and not when it is called from the generated XLS, then maybe this can help

Private Sub Workbook_Open()
  If ThisWorkbook.Path = "" Then
     Call myMacro
  End If
End Sub

myMacro will only be called if the opened file is XLT, not XLS.