saving .xlsx as .xlsx or .xlsm the file format or extension is not valid

[jordanburch]

hello,

I have the below. It basically saves the file to get rid of digital signatures and the file being marked as final. I need to be able to access the file with vba so im trying to save it to get rid of the permissions restrictions. The cod runs however when I try and open the saved file it give the file format or extension is not valid error message. I have tried to save as .xls .xlsx .xlsm and non seem to work. Any thoughts?

Sub ChangeFileFormat()
    Dim Folder As String, FileName As String, folder2 As String
    Dim wb As Workbook
    Application.DisplayAlerts = False
    Folder = "C:\Users\jordan.burch.ctr\Desktop\CERT STATEMENTS 3\"
     folder2 = "C:\Users\jordan.burch.ctr\Desktop\CERT STATEMENTS\"
   
    FileName = Dir(Folder & "*.xls*", vbNormal)
   
    On Error GoTo exitsub
    With Application
        .EnableEvents = False: .ScreenUpdating = False
    End With
    Do While FileName <> ""
       
        Set wb = Workbooks.Open(Folder & FileName)
        FileName = Replace(FileName, ".xls*", ".xlsm")
        If FileName <> ThisWorkbook.Name Then
            wb.SaveAs FileName:=folder2 & FileName, FileFormat:= _
            xlExcel8, Password:="", WriteResPassword:="", _
            ReadOnlyRecommended:=False, CreateBackup:=False
        End If
       
        wb.Close False
       
        FileName = Dir
        Set wb = Nothing
    Loop
exitsub:
    With Application
        .EnableEvents = True: .ScreenUpdating = True
    End With
    If Err > 0 Then MsgBox (Error(Err)), 48, "error"
    Application.DisplayAlerts = True
End Sub

Jordan

 

[Fluff]

Do you want to save as an xlsm file?

 

[jordanburch]

Do you want to save as an xlsm file?

Hi Fluff,

I dont really care the file extension I just want it to resave correctly so I can access the data with VBA.

Thanks,

Jordan

 

[Fluff]

In that case use

 wb.SaveAs FileName:=folder2 & FileName, FileFormat:= _
            52, Password:="", WriteResPassword:="", _
            ReadOnlyRecommended:=False, CreateBackup:=False

Also in future please post your code in code tags How to Post Your VBA Code

 

[jordanburch]

ADVERTISEMENT

In that case use

 wb.SaveAs FileName:=folder2 & FileName, FileFormat:= _
            52, Password:="", WriteResPassword:="", _
            ReadOnlyRecommended:=False, CreateBackup:=False

Also in future please post your code in code tags How to Post Your VBA Code

Thanks Fluff will do. I tried your code and now it says the extension cannot be used with the selected file type.

Jordan

 

[Fluff]

Also use this

FileName = Left(FileName, InStrRev(FileName, ".")) & "xlsm"

you cannot use wildcards with replace

 

[jordanburch]

worked like a charm you are the best! what was wrong with the original code?

thanks

Jordan

 

[Fluff]

Partly what I said in post#6 & partly you were saving it as an xls fileformat with xlExcel8,