Code converting xlsm to xls

bamaisgreat

Well-known Member
Joined
Jan 23, 2012
Messages
821
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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"
 
Upvote 0
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
 
Upvote 0
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 ?
 
Upvote 0
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
 
Upvote 0
Re: Need help with Code converting xlsm to xls

My pleasure & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top