Error when changing file extension VBA

Question

I have a VBA code that opens a .csv file, changes its name and extension and then closes it saving the changes (as a summary). The code works fine, but when I try to open the new file, I get a message that says that the file cannot be opened because the format or the file extension are not valid. I know that the problem is generated by the code, because if I do everything manually, I don't get any errors. This is the code:

Sub test2()

    Dim nombre As String
    Dim base As Workbook
    Dim filtros() As Variant
    Dim archivo As Workbook

    Set archivo = ActiveWorkbook

    Application.ScreenUpdating = False

    nombre = InputBox("Ingrese el nombre de la base con los nuevos clientes")

    Workbooks.Open ("C:\Users\npavon\Desktop\Bases de Datos Asistencia\" & nombre & ".csv")
    Set base = Workbooks(nombre & ".csv")

    base.Sheets(1).Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:= _
        True

    base.SaveAs ("C:\Users\npavon\Desktop\Bases de Datos Asistencia\" & Left(base.Name, InStr(base.Name, ".") - 1) & " ordenado.xlsb")
    base.SendMail Recipients:="xx@xx.cl", Subject:="Base de Datos " & Date, returnreceipt:=False
    base.Close savechanges:=True

    Application.ScreenUpdating = True

End Sub

Any suggestions?

have you tested the value of "C:\Users\npavon\Desktop\Bases de Datos Asistencia\" & Left(base.Name, InStr(base.Name, ".") - 1) & " ordenado.xlsb" . If you put a stop before this and ?"C:\Users\npavon\Desktop\Bases de Datos Asistencia\" & Left(base.Name, InStr(base.Name, ".") - 1) & " ordenado.xlsb" in the immediate window — QHarr, Feb 23 '18 at 14:08

score 2 · Accepted Answer · 2018-02-23T14:13:14.260

Forget the extension and use the fileformat parameter.

base.SaveAs filename:="C:\Users\npavon\Desktop\Bases de Datos Asistencia\" & Left(base.Name, InStr(base.Name, ".") - 1) & " ordenado", fileformat:=xlExcel12

Without specifying the file format, Excel tries to save as the default from File, Options, Save, Save files in this format. Adding a different extension does nothing to change this.

More information at Workbook.SaveAs Method and XlFileFormat Enumeration .

Error when changing file extension VBA

1 Answers1