VBA How to replace excel files in folder as Macro Enabled workbooks

Question

Trying to figure out how to save all excel files in a selected folder as Macro enabled workbooks. If possible, I want to just save down the macro enabled workbooks to replace all the excel files in the folder. at the moment i only have code to open one excel file in a folder - i can't figure out how to save the open workbook down as a macro enabled workbook, never mind looping through a whole folder. This is the code i have, it works on one file if i use an if statement rather than a do while loop for opening one file. It says there's an error with file = dir in the do while loop:

Sub SaveAllAsMacroWkbks()


Dim wb As Workbook
Dim myPath As String
Dim myFile As String, macFile As String
Dim myExtension As String, macExt As String
Dim FldrPicker As FileDialog

'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual

'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

With FldrPicker
  .Title = "Select A Target Folder"
  .AllowMultiSelect = False
    If .Show <> -1 Then GoTo NextCode
    myPath = .SelectedItems(1) & "\"
End With

'In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings

'Target File Extension (must include wildcard "*")
  myExtension = "*.xls*"
  macExt = "*.xlsxm"

'Target Path with Ending Extention
  myFile = Dir(myPath & myExtension)
  macFile = Dir(myPath & macExt)
'Loop through each Excel file in folder
  Do While myFile <> ""
      Set wb = Workbooks.Open(Filename:=myPath & myFile)
      'wb.saveAs FileName:=macFile, FileFormat:=52
      'wb.Close SaveChanges:=True
   'Get next file name
      myFile = Dir
  Loop

ResetSettings:
  'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

Variatus Variatus · Accepted Answer · 2017-10-27T05:03:50

The code below should help you on your way.

Sub SaveAllAsXLSM()
    ' 27 Oct 2017

    Dim FldrPicker As FileDialog
    Dim myPath As String
    Dim myFile As String, newFile As String
    Dim Fn() As String
    Dim i As Long
    Dim Wb As Workbook

    ' Optimize Macro Speed
    Application.ScreenUpdating = False
    ' You aren't making any changes that trigger calculations
    ' nor do you have event procedures in your VB Project
    ' Therefore these commands do nothing but to take their own time to execute
'    Application.EnableEvents = False
'    Application.Calculation = xlCalculationManual

    ' User selects Target Folder Path
    Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
    With FldrPicker
        .Title = "Select A Target Folder"
        .AllowMultiSelect = False
        If .Show Then myPath = .SelectedItems(1) & "\"
    End With

    If Len(myPath) Then
        myFile = Dir(myPath)
        Do While Len(myFile)
            Fn = Split(myFile, ".")
            i = UBound(Fn)
            If StrComp(Fn(i), "xlsx", vbTextCompare) = 0 Then
                myFile = myPath & myFile
                Debug.Print myFile
                Set Wb = Workbooks.Open(Filename:=myFile)
                Fn(i) = "xlsm"
                newFile = myPath & Join(Fn, ".")
                Debug.Print newFile
                Wb.SaveAs Filename:=newFile, FileFormat:=52
                Wb.Close SaveChanges:=False

                Do
                    ' let the hard disc catch up with the VBA code
                    DoEvents
                Loop While IsOpen(myFile)
                Kill myFile                 ' delete the original
            End If

            myFile = Dir
        Loop
    End If

    Application.ScreenUpdating = True
End Sub

Private Function IsOpen(Fn As String) As Boolean
    ' 27 Oct 2017

    Dim i As Integer

    With Workbooks
        For i = 1 To .Count
            If StrComp(Fn, .Item(i).FullName, vbTextCompare) = 0 Then
                IsOpen = True
                Exit For
            End If
        Next i
    End With
End Function

I don't think that you can handle Mac files on a PC and v.v. However, if it is possible you could easily tweak my code. You could do the same for files with xls extension.

I have some doubt about the different speeds with which VBA and the hard disc operate. The loop for DoEvents is supposed to slow down the code. It definitely will slow down the code execution, but I'm not so certain that the DoEvents will work as intended. If it doesn't, the code will still be too fast.

VBA How to replace excel files in folder as Macro Enabled workbooks

2 Answers