Macro for saving active work books to new file type

[nicholas_c1]

Hi All,

I'm quite new to creating macros in excel. I currently have to open numerous files in .XLSM format and save them to .XLS format. I have a macro that works I am just trying to make some adjustments to it but can't seem to get it working. Currently when the macro runs, it changes the file type successfully, saves a copy of the file and closes the work book. This isn't a huge deal because I can always go back and delete the old file but I want it to be more efficient.

My questions are: based on the macro below, is there a way to adjust it so that A. It does not save a copy and saves the current file to a new extension? & B. Is there a way to make it so that it saves ALL open/active work books?

This is what I'm currently using. I apologize in advance if I don't know what I'm talking about, I'm a new macro user :crying:


Sub saver()
Dim fn As String
Dim l As Long
Dim wb As Workbook

Set wb = ActiveWorkbook
fn = wb.FullName
l = InStrRev(fn, ".")
fn = Left(fn, l)
fn = fn & "xls"

wb.SaveAs Filename:=fn, FileFormat:=51
wb.Close

End Sub

[Paul_Hossler]

I'd have a control WB with the macro

The macro below lets you select the WBs to convert, then it opens them one by one, saves as xls, closes the xlsx, and finally deletes the original xlsx file

Also I think you wanted FileFormat 56, not 51. Just changing the extension doesn't change the underlying file type/structure




Option Explicit


Sub SaveAsXLS()
Dim vFiles As Variant
Dim iFile As Long
Dim wb As Workbook
Dim sNewName As String




vFiles = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx", , "Select file(s)", , True)

'was [Cancel] clicked
If VarType(vFiles) = vbBoolean Then
If vFiles = False Then
Exit Sub
End If
End If


Application.ScreenUpdating = False


For iFile = LBound(vFiles) To UBound(vFiles)
sNewName = Left(vFiles(iFile), InStrRev(vFiles(iFile), ".")) & "xls"


Workbooks.Open vFiles(iFile)
Set wb = ActiveWorkbook

wb.SaveAs sNewName, xlExcel8
wb.Close False

Kill vFiles(iFile)

Next iFile


Application.ScreenUpdating = True

MsgBox "Done"


End Sub


I also added CODE tags to the macro in your post. Take a minute and look at the FAQ link in my sig

[nicholas_c1]

That's amazing, it works fantastically! The only thing I had to change was xlsx to xlsm as they're macro enabled work books I'm converting to XLS.

This is going to save me a ton of time as I have to do this for 100's of files. Really appreciate it!

Thanks!!

[Paul_Hossler]

That's amazing, it works fantastically! The only thing I had to change was xlsx to xlsm as they're macro enabled work books I'm converting to XLS.

This is going to save me a ton of time as I have to do this for 100's of files. Really appreciate it!

Thanks!!

If all the xlsm files to be converted are in a folder, it's easy enough to do a 'hands free' conversion of all files in the folder

just put this wb in the same folder and run the maco




Option Explicit


Sub SaveAsXLS()
Dim vFiles As Variant
Dim iFile As Long, i As Long
Dim wb As Workbook
Dim sOldName As String, sNewName As String, sPath As String


'don't update screen (faster) and no prompts when opeing files
Application.ScreenUpdating = False
Application.EnableEvents = False

i = InStrRev(ThisWorkbook.FullName, "\")
sPath = Left(ThisWorkbook.FullName, i)


sOldName = Dir(sPath & "*.xlsm")

Do While Len(sOldName) <> 0
If sOldName <> ThisWorkbook.Name Then
' MsgBox sOldFileName
sNewName = Left(sOldName, InStrRev(sOldName, ".")) & "xls"

Workbooks.Open sPath & sOldName
Set wb = ActiveWorkbook

'no compatible error messages
Application.DisplayAlerts = False

'delete possible xls leftover
On Error Resume Next
Kill sPath & sNewName
On Error GoTo 0

wb.SaveAs sPath & sNewName, xlExcel8
wb.Close False
Application.DisplayAlerts = True

Kill sPath & sOldName

End If

sOldName = Dir
Loop

Application.EnableEvents = True
Application.ScreenUpdating = True


MsgBox "Done"


End Sub

[nicholas_c1]

A new folder is created each time a new set of files comes in, so I think the first one you sent that allows to pick certain files from a certain folder is perfect. I appreciate the suggestion though.

Thanks again.

[Paul_Hossler]

Well, if you're going to convert all the XLSM files in the new folder that is created, the second approach just requires putting the Master_1 WB in that folder and running the macro

Another approach would be use a dialog box to select the Folder, and do all files in that folder. That way Master_x.xlsm doesn't have to be in the folder

[dleister]

I have an excel file that is a template, when a user opens the file and updates it I want to force them to save the file in a macro-enabled format and automatically saved to a cell (this is where the filename is created). The code I used works 99% of the time but the 1% is of concern since it is saving it without a file extension so the file is not usable. Can anyone see what I am doing wrong?

Here is my code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


Dim xFileName As String




If SaveAsUI <> False Then
Cancel = True
ThisFile = Range("W2").Value
xFileName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As xlsm file")
If xFileName <> "False" Then
Application.EnableEvents = False
ActiveWorkbook.SaveAs Filename:=ThisFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled

Application.EnableEvents = True
Else
MsgBox "Action Cancelled"
Cancel = True
Exit Sub
End If
End If
End Sub

[dleister]

A little more information that I noticed when saving the file:
I noticed that sometimes when I am saving (save as) the file type shows macro-enabled however the filename is blank, this is when the saved file has no extension when saved. Other times the filename will show PR Template1 when saving, this is when the file saves correctly.


The way this works is that they can save it with the given name which was downloaded when they open the template "PR Template1"; or even if they change the filename it will override when saving to be the filename within cell W2.


Somehow the blank filename during saving is what is causing the problem.