Code converting xlsm to xls

bamaisgreat

Well-known Member
Joined
Jan 23, 2012
Messages
784
As you can probably tell by looking at the code below, I simply run the code when I have a xlsm file open and it saves it as a xls. A problem Im having is if i accidentally run the code on a xls I will get error at the Kill Filename.
Is there something that could be added at the beginning where a user form would pop up saying the file is a xlsm would you like to convert to xls yes or no. Or if the file is already an xls a userform would pop up saying the file is a xls and contains No Macros and then you could just x out of the userform.

Code:
Sub bomaisgrat()
   Dim Fname As String
   With ActiveWorkbook
      If ActiveWorkbook.FileFormat = 52 Then
         Application.DisplayAlerts = False
         Fname = .FullName
         .SaveAs Replace(.FullName, ".xlsm", ""), 51
         Application.DisplayAlerts = True
      End If
   End With
   Kill File name
End Sub
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,147
Office Version
2013
Platform
Windows
Re: Need help with Code converting xlsm to xls

Hi,
untested but see if this update to your code solves the issue

Code:
Sub bomaisgrat()
   Dim Fname As String
   Application.DisplayAlerts = False
   With ActiveWorkbook
'xlOpenXMLWorkbookMacroEnabled (Open XML Workbook Macro Enabled (52))
      If .FileFormat = 52 Then
         Fname = .FullName
'xlExcel8 (Excel 97-2003 Workbook (56))
         .SaveAs Replace(.FullName, ".xlsm", ".xls"), 56
         Kill Fname
      End If
   End With
   Application.DisplayAlerts = True
End Sub
Dave
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,432
Re: Need help with Code converting xlsm to xls

This macro will run only if the workbook has an "xlsm" extension.
Code:
Sub bomaisgrat()
    If Mid(ThisWorkbook.Name, WorksheetFunction.Find(".", ThisWorkbook.Name, 1) + 1) = "xlsm" Then
        Dim Fname As String
        With ActiveWorkbook
            Application.DisplayAlerts = False
            Fname = .FullName
            .SaveAs Replace(.FullName, ".xlsm", ""), 51
            Application.DisplayAlerts = True
        End With
    End If
   Kill Fname
End Sub
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,378
Office Version
365
Platform
Windows
Re: Need help with Code converting xlsm to xls

How about
Code:
Sub bamaisgreat()
   Dim Fname As String
   With ActiveWorkbook
      If .FileFormat = 52 Then
         Application.DisplayAlerts = False
         Fname = .FullName
         .SaveAs Replace(.FullName, ".xlsm", ""), 51
         Kill Fname
         Application.DisplayAlerts = True
      End If
   End With
End Sub
 

bamaisgreat

Well-known Member
Joined
Jan 23, 2012
Messages
784
Re: Need help with Code converting xlsm to xls

Worked Great. Is there something that I can add to personal workbook so when an xlsm workbook is opened a dialog will open will say "This workbook is an xlsm. It may need converted to email"
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,378
Office Version
365
Platform
Windows
Re: Need help with Code converting xlsm to xls

Put this in the ThisWorkbook module of your Personal.xlsb
Code:
Private WithEvents XlApp As Application

Private Sub Workbook_Open()
    Set XlApp = Application
End Sub

Private Sub xlapp_workbookopen(ByVal Wb As Workbook)
    If Wb.FileFormat = 52 Then MsgBox "This workbook is an xlsm. It may need converted to email"
End Sub
 

bamaisgreat

Well-known Member
Joined
Jan 23, 2012
Messages
784
Re: Need help with Code converting xlsm to xls

Thanks for the help. I have the formats a little mixed up. Can you change where if its a xls or xlsm then it will save as an xlsx ?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,378
Office Version
365
Platform
Windows
Re: Need help with Code converting xlsm to xls

How about
Code:
Sub bamaisgreat()
   Dim Fname As String
   With ActiveWorkbook
      If .FileFormat = 52 Or .FileFormat = 51 Then
         Application.DisplayAlerts = False
         Fname = .FullName
         .SaveAs Left(.FullName, InStrRev(.FullName, ".xls") - 1), 51
         Kill Fname
         Application.DisplayAlerts = True
      End If
   End With
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,378
Office Version
365
Platform
Windows
Re: Need help with Code converting xlsm to xls

My pleasure & thanks for the feedback
 

Forum statistics

Threads
1,078,134
Messages
5,338,428
Members
399,232
Latest member
stevenmoritz

Some videos you may like

This Week's Hot Topics

Top