none
Force Excel to Save As to non-Excel File Format using VBA RRS feed

  • Question

  • Hi,

    I want to save an Excel 2007 file from ".xlsm" to ".ini" using the SaveAs method in VBA. Unfortunately, .ini is not a supported file format for the FileFormat parameter in the SaveAs method, so I get a "Error 9: Subscript is out of range" when running the code. My goal is to mimick when the user surrounds the file name with double quotes in the Save As dialog to force Excel to save it with a different file extension from the Type specified in the dialog.

    I know saving an Excel file to .ini is weird, but my scenario involves saving the file to a nonstandard Windows file version. The non-standard file type is a third-party file.

    I've checked online forums and newsgroups and could not find an answer to this question. Everything I found was to force Excel to save from .xls to .xlsm or .xlsx to .csv.

    Thanks,

    Paul

    Friday, December 28, 2012 3:52 PM

Answers

All replies

  • A .ini file is a text file with a very specific layout. If you save an Excel workbook with extension .ini, it won't be a text file and it won't have the specific layout required for a .ini file...

    Regards, Hans Vogelaar

    Friday, December 28, 2012 4:07 PM
  • Hi Hans,

    Thanks for the reply. I wasn't truthful with the actual file. Maybe that will help with finding an answer. The actual file extension is ".xnv". It is used by a third-party. Thoughts on my situation?

    Thanks,

    Paul

    Friday, December 28, 2012 4:22 PM
  • From a Google search, I get the impression that the .xnv extension is used by nVision, but that it's basically the same as .xlsx/.xlsm.

    Code like this should work:

    ActiveWorkbook.SaveAs Filename:="C:\Export\Test.xnv", FileFormat:=xlOpenXMLWorkbookMacroEnabled
    


    Regards, Hans Vogelaar

    Friday, December 28, 2012 4:45 PM
  • Still got Error 9: Subscript is out of range. Here is my code:

    NewFileName = "2_FX_PROOF.xnv"
    With Application
            .DisplayAlerts = False
            .Workbooks(pCurName).SaveAs Filename:=NewFileName, _
                FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
                Password:="", _
                WriteResPassword:="", _
                ReadOnlyRecommended:=False, _
                CreateBackup:=False
            .DisplayAlerts = True
    End With
    Friday, December 28, 2012 4:57 PM
  • Are you sure that Workbooks(pCurName) exists?


    Regards, Hans Vogelaar

    • Marked as answer by Paul LTE Friday, December 28, 2012 6:13 PM
    Friday, December 28, 2012 5:07 PM
  • That was it! I had the pCurName and the NewFileName variables set to the same value. Thanks for the help Hans!

    Thanks,

    Paul

    Friday, December 28, 2012 6:13 PM