Modify multiple excel files - No mistakes, no results

Question

I just need to change the contents of two cells, as you can see, to all the files in a certain folder but nothing happens when the script starts. No mistakes, no results.

Sub ModifyAllFiles()

    On Error Resume Next
    MyPath = "Macintosh HD:Users:Danespola:Desktop:test"
    If MyPath = "" Then Exit Sub
    On Error GoTo 0

    If Right(MyPath, 1) <> Application.PathSeparator Then
        MyPath = MyPath & Application.PathSeparator
    End If

    FilesInPath = Dir(MyPath, MacID("XLSX"))
    If FilesInPath = "" Then
        MsgBox "No files found"
        Exit Sub
    End If

    Fnum = 0
     Do While FilesInPath <> ""
     Fnum = Fnum + 1
     ReDim Preserve MyFiles(1 To Fnum)
     MyFiles(Fnum) = FilesInPath
     FilesInPath = Dir()
    Loop

    If Fnum > 0 Then

    Do While Filename <> ""
      Application.ScreenUpdating = False
        Workbooks(FilesInPath).Open
        Range("A5").Value = "ca1"
        Range("A6").Value = "ca2"
        Workbooks(FilesInPath).Save
        Workbooks(FilesInPath).Close
        Filename = Dir()
    Loop
  Application.ScreenUpdating = True

    End If

End Sub

Thanks!

'nothing happens' is probably because Fnum is 0.. Which sheet do you want to update, sheet1 ? — CDP1802, Mar 16 '20 at 11:02
you are right, Fnum is 0, but even after adding a block related to Fnum (I have no doubts but you can see it above, I modified the code), it gives me the error of "variable not defined". However, there is also a second viable way, that of obtaining a list of files and whose code you always find above, but I don't know where and how to tell the script to modify that list of files, edit ScriptToRun in relation to my macro. @CDP1802 — Dantino Esposito, Mar 16 '20 at 18:30
Which sheet do you want to update on the workbooks, do they have a nume ? — CDP1802, Mar 16 '20 at 20:38

CDP1802 · Accepted Answer · 2020-03-17T11:06:32.270

0

No need to count the files or build an array just update them as they are found.

Sub ModifyAllFiles()

    Dim Filename As String, MyPath As String, count As Integer
    Dim wb As Workbook, t0 As Single
    t0 = Timer

    MyPath = "Macintosh HD:Users:Danespola:Desktop:test"
    If Right(MyPath, 1) <> Application.PathSeparator Then
        MyPath = MyPath & Application.PathSeparator
    End If

    ' this may not work for excel files created with windows
    'Filename = Dir(MyPath, MacID("XLSX"))

    Filename = Dir(MyPath)
    If Filename = "" Then
        MsgBox "No files found"
        Exit Sub
    Else
        Application.ScreenUpdating = False

        Do While Filename <> ""
            If LCase(Right(Filename, 5)) = ".xlsx" Then
                count = count + 1
                Set wb = Workbooks.Open(MyPath & Filename)
                With wb.Sheets(1)
                   .Range("A5").Value = "ca1"
                   .Range("A6").Value = "ca2"
                End With
                wb.Save
                wb.Close
            End If
            Filename = Dir()
        Loop
        Application.ScreenUpdating = True
    End If
    MsgBox count & " files updated", vbInformation, "Finished in " & Int(Timer - t0) & " secs"

End Sub

edited Mar 17 '20 at 11:06

answered Mar 16 '20 at 21:06

CDP1802

13,871
2
7
17

Changed MyPath & "\" & Filename in MyPath & Filename because it gives me error 1004 "impossible to find ..", now it runs the script but after just 2 seconds it ends saying that it has modified 2 files when the file folder contains many more. it seems obvious to me to specify that all the files are of the same extension, ie xlsx. I don't understand where the problem lies. – Dantino Esposito Mar 16 '20 at 21:27
@Dantino I don't have a Mac so can't help much more. You can add debug.print wb.name before wb.save to see which 2 have been updated. Are all the files in that folder or are there sub folders. – CDP1802 Mar 16 '20 at 21:33
All files in a folder. I tried with another folder and this time 50 results processed, but even here the files were many more. I honestly don't know if it is due to the fact of working on Mac or what because some files actually updates them, others don't. @CDP1802 – Dantino Esposito Mar 16 '20 at 23:17
However, taking into consideration also the second way, the one that processes all the names of the files in a list and that therefore works, the basic explanation says that it is enough that you use MySplit(FileInMyFiles) or that it is also foreseen to open the files, with Workbooks.Open(MySplit (FileInMyFiles)), so I readjusted your code a bit and added it before End Sub but I get "variable not defined". Maybe I'm wrong in something small, but where. @CDP1802 – Dantino Esposito Mar 16 '20 at 23:17
@Dantino I found this https://stackoverflow.com/questions/13292376/what-are-macid-filetypes-in-excel-vba so I have updated my answer. – CDP1802 Mar 17 '20 at 11:07
Your code is OK, it works. But it requires - at least in my case - that all the files are open and saved (or as long as they are modified to date today, I don't know why or for what reason this occurs but it does not work for files that have not been modified recently, it is an oddity ). Anyway, thank you, you have given me a good hand. – Dantino Esposito Mar 18 '20 at 14:50

Modify multiple excel files - No mistakes, no results

1 Answers1