none
Add Workbook in VBA RRS feed

  • Question

  • I am trying to add a work book in VBA based on a template of existing file and then name the new workbook but I cannot get the code to work. he template file is an macro enabled file.

    Path = ActiveWorkbook.Path
    Workbooks.Add (Path & "\CostTracker.xlsm")
    ActiveWorkbook.SaveAs Filename:="testBook.xlsm"

    can anyone help please?

    Wednesday, April 22, 2015 3:51 PM

Answers

  • Hi,

    When you use ActiveWorkbook.SaveAs method to save a workbook, it has a list of parameters, fot the parameter FileFormat, the default is the format of the version of Excel being used.And if you want it to save as a xlsm file, you need to add this parameter, and the value is 52. More detailed information you can get it here.

    SaveAs Method : https://msdn.microsoft.com/en-us/library/office/ff841185.aspx

    XLFileFormat : https://msdn.microsoft.com/en-us/library/office/ff198017.aspx

    You may refer to this code

    ActiveWorkbook.SaveAs Filename:="testBook.xlsm", FileFormat:=52

    Hope this could help you

    Best Regards


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by L.HlModerator Friday, April 24, 2015 2:18 AM
    Thursday, April 23, 2015 10:17 AM
    Moderator
  • Lanlan - thanks - that works!
    • Marked as answer by py1 Thursday, April 23, 2015 12:20 PM
    Thursday, April 23, 2015 12:20 PM

All replies

  • Do you get any errors in the code?

    What file path is passed to the Add method of the Workbooks class?

    Wednesday, April 22, 2015 4:02 PM
  • The workbook that has the code in is in the same folder as the Costtracker template which I am trying to use.

    I explicitly specify the path.

    the code locates the template ok  - but is ignores my saveas filename instruction but creates its own file name - and because of that (I think) it tries to save it as an xlsx file - and therefore I get a message that it is not macro enabled.

    I suspect there is something  wrong with my save statement?

    I hope that helps

    Wednesday, April 22, 2015 4:09 PM
  • Hi,

    When you use ActiveWorkbook.SaveAs method to save a workbook, it has a list of parameters, fot the parameter FileFormat, the default is the format of the version of Excel being used.And if you want it to save as a xlsm file, you need to add this parameter, and the value is 52. More detailed information you can get it here.

    SaveAs Method : https://msdn.microsoft.com/en-us/library/office/ff841185.aspx

    XLFileFormat : https://msdn.microsoft.com/en-us/library/office/ff198017.aspx

    You may refer to this code

    ActiveWorkbook.SaveAs Filename:="testBook.xlsm", FileFormat:=52

    Hope this could help you

    Best Regards


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by L.HlModerator Friday, April 24, 2015 2:18 AM
    Thursday, April 23, 2015 10:17 AM
    Moderator
  • Lanlan - thanks - that works!
    • Marked as answer by py1 Thursday, April 23, 2015 12:20 PM
    Thursday, April 23, 2015 12:20 PM