CODEWITHSUNDEEP
excelvbaxlsx
kodamasenpai
kodamasenpai

Reputation: 3

Excel cannot open .xlsx file when file is saved via VBA macro

I receive an error when trying to open a .xlsx file that wat saved using a VBA macro. I receive a message box saying "Excel cannot open the file 'filename.xlsx' because the file format or file extension is not valid". But if I use "save as..." manually it opens correctly. Also if I save the file via VBA macro but with .xls format it opens correctly as well.

This is the code I tried:

    Sub EMAIL()

    ActiveWorkbook.Save
    Dim Fecha As String
    Dim Ruta_archivo As String
    Dim Ruta As String
    Dim Nombre_archivo As String

    Fecha = Right$(Year(Date), 4) + Right$(100 + Month(Date), 2) + 
    Right$(100 + Day(Date), 2)
    Ruta = "path"
    Nombre_archivo = "filename"

    Ruta_archivo = Ruta + "\" + CStr(Year(Date)) + "\" + 
    MonthName(Month(Date), False) + "\" + Nombre_archivo + Fecha + ".xlsx"

    'Crear copia
    ActiveWorkbook.SaveCopyAs Filename:=Ruta_archivo
 
 

    End Sub

I expect the file to open correctly.

Upvotes: 0

Views: 564

Answers (1)

Paul
Paul

Reputation: 286

The original file format is .xlsm

That is the reason for your problem.

You have a .xlsm file, which you save a copy of with a .xlsx extension. That doesn't work. You can change the filename to end in .xlsm, or save it explicitly as a .xlsx file by making a copy first:

ActiveWorkbook.Sheets.Copy ' Creates a new workbook with a copy of all sheets
ActiveWorkbook.SaveAs FileName:=Ruta_archivo, FileFormat:=xlWorkbookDefault ' Saves it as .xlsx

Upvotes: 0

Related Questions