4

My sub opens up a .xlsm file in a new Excel instance, makes changes to the file, and saves the changes.

However, despite the fact that the file being opened is .xlsm, upon workbook.save I get the alert message "the following features cannot be saved in macro-free workbooks..."

This doesn't make sense to me because the file is .xlsm. Any ideas?

Edit: I believe I found the source of the problem. Although the variable "path" contains C:\file.xlsm, wkb.path is empty. (see Debug.print's below). Why is the wkb.path empty?

    Set XL = New Excel.Application
    XL.Visible = False

    For Each Path In XLSMPaths

        Set wkb = XL.Workbooks.Add(Path)

        Debug.Print Path      ' "C:\file.xlsm" 
        Debug.Print wkb.path  ' ""
        Debug.print wkb.name  ' "file"      

        wkb.Save   '<- alert message "The following features cannot be saved in macro-free workbooks..."

    Next Path
Sayjota
  • 128
  • 1
  • 1
  • 8

1 Answers1

9

By default, the .save method will save the file as .xls or .xlsx (dependant on your version of excel), you need to force this with .saveas

wkb.SaveAs Filename:=MyNewPathFilename, FileFormat:=xlOpenXMLWorkbookMacroEnabled

obviously change the variable 'MyNewPathFilename' to whatever you want to save the file as, probably want to take the Path and check it ends in .xlsm then pass it into this variable

Darren H
  • 910
  • 8
  • 24