modify multiple excel files on mac - no errors but also no results

[dantesp]

Let's see who is strong.

A code that does not give errors but which does not modify not even one of the series of excel files (xlsx, Mac 2011) present in the same folder.

What is the problem?

Option Explicit

    Sub Macro2()

    Dim MyFiles As String
    Dim MyPath As String
    Dim FilesInPath As String
    Dim Filename As String
    Dim Fnum As Long

        On Error Resume Next
        MyFiles = MacScript("choose folder as string")
        MyPath = MacScript("return posix path of (choose folder with prompt ""Select the folder"") as string")
        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 & "XLS#")
        If FilesInPath = "" Then
                 MsgBox "No files found"
                Exit Sub
            End If

            If Fnum > 0 Then

            Filename = Dir(MyPath & "XLS#")

            Do While Filename <> ""

        Workbooks.Open ":" & MyFiles

        MsgBox ActiveWorkbook.Name

            insertc wb:=ActiveWorkbook

        ActiveWorkbook.Close SaveChanges:=True

        Loop

        End If
        End Sub

about insertc

Sub insertc(ByVal wb As Workbook)
        wb.Range("A5").Value = "ca1"
        wb.Range("A6").Value = "ca2"
End Sub

Many thanks

 

[Dermot]

Why not step through the code with F8 and test that each line is doing what it should?

 

[dantesp]

Done. "Sub or function not defined". I don't understand why, the code seem ok.

 

[Dermot]

What is MacScript? Is that a real function?

 

[dantesp]

Dermot, MacScript is a function usable on Mac system, of course.

 

[dantesp]

I have updated the script as follows but nothing changes. 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

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

 

[Dermot]

You need to step through line by line (F8) and see how far it is getting

 

[dantesp]

Nothing happens that is not a highlight of the simple Sub ModifyAllFiles () without any explanation.

 

[dantesp]

This is the solution. Solved.


Option Explicit

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:User:Desktop:test"
If Right(MyPath, 1) <> Application.PathSeparator Then
MyPath = MyPath & Application.PathSeparator
End If

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

Do While Filename <> ""
count = count + 1
Set wb = Workbooks.Open(MyPath & Filename)
With wb.Sheets(1)
Call insertlinea
End With
wb.Save
wb.Close
Filename = Dir()
Loop
Application.ScreenUpdating = True
End If
MsgBox count & " files updated", vbInformation, "Finished in " & Int(Timer - t0) & " secs"

End Sub