Code converting xlsm to xls

[bamaisgreat]

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.

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]

Re: Need help with Code converting xlsm to xls

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

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]

Re: Need help with Code converting xlsm to xls

This macro will run only if the workbook has an "xlsm" extension.

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

 

[Fluff]

Re: Need help with Code converting xlsm to xls

How about

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]

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]

Re: Need help with Code converting xlsm to xls

Put this in the ThisWorkbook module of your Personal.xlsb

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]

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]

Re: Need help with Code converting xlsm to xls

How about

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

 

[bamaisgreat]

Re: Need help with Code converting xlsm to xls

Perfect Thanks.

 

[Fluff]

Re: Need help with Code converting xlsm to xls

My pleasure & thanks for the feedback