macOS Apple/MacScript Help

[lkm_MAC]

Hi, i am trying to write a function in excel to copy a file from 1 location to another but seem unable to get it to work. here is my code, what am i missing

Any help or advise would be greatly appreciated.

Function CopySourceImages(ProductCode As String)
Dim sourceFile As String
Dim destinationFile As String
    sourceFile = "/Users/admin/Desktop/Quidzin/Master Images/" & ProductCode & ".psd"
    destinationFile = "/Users/admin/Desktop/Quidzin/Monthly Images/" & ProductCode & ".psd"
    If FileOrFolderExists(sourceFile) Then
        If Not FileOrFolderExists(destinationFile) Then
        Dim ScriptToCopyFile As String
            ScriptToCopyFile = "set sourceFileAlias" & " to " & Chr(34) & sourceFile & Chr(34) & " as alias" & Chr(13) & _
                                            "set destinationFileAlias" & " to " & Chr(34) & destinationFile & Chr(34) & " as alias" & Chr(13) & _
                                            "tell application " & Chr(34) & "finder" & Chr(34) & " duplicate " & sourceFileAlias & " to " & destinationFileAlias & " end tell"
            MacScript (ScriptToCopyFile)
            If FileOrFolderExists(destinationFile) Then
                CopySourceImages = "Source File Exists, File Copied"
            Else
                CopySourceImages = "Source File Exists, Error Copying File"
            End If
        Else
                CopySourceImages = "Destination File Exists, Skipped"
        End If
    Else
        CopySourceImages = "Photo Needs Taking"
    End If
                                                               
End Function

Function FileOrFolderExists(FileOrFolderstr As String) As Boolean
Dim ScriptToCheckFileFolder As String
Dim TestStr As String
    If Val(Application.Version) < 15 Then
        ScriptToCheckFileFolder = "tell application " & Chr(34) & "System Events" & Chr(34) & "to return exists disk item (" & Chr(34) & FileOrFolderstr & Chr(34) & " as string)"
        FileOrFolderExists = MacScript(ScriptToCheckFileFolder)
    Else
        On Error Resume Next
        TestStr = Dir(FileOrFolderstr, vbDirectory)
        On Error GoTo 0
        If Not TestStr = vbNullString Then FileOrFolderExists = True
    End If
End Function

Regards
Lee

 

[superscape]

Maybe I'm asking a dumb question, but does it definitely need to be part of an Excel spreadsheet?

 

[lkm_MAC]

Hi Superscape,

there are a list of product codes in an excel sheet, i need to run a formula to see if we have an image for it (all images are stored in a single folder and named by the product code ie: ABCDEFGHI001.psd) if the file exists then copy the file to a working folder to be put in this months brochure and if it doesn't exist then advise the designer that a photo needs taking.

Regards
Lee

 

[chown33]

I suggest breaking it down, trying individual lines in AppleScript Editor, and then working out exactly where the specific problems are.

Right now, your entire VBA and AppleScript function has to work all at once. You don't have separable parts. You also haven't posted any error messages or other clues that might be useful guides. I have some guesses about where the problems may lie, but the approach will still be Break It Down and Test Each Part.


For example, the first "line" of what will eventually become the AppleScript is this:

"set sourceFileAlias" & " to " & Chr(34) & sourceFile & Chr(34) & " as alias" & Chr(13) & _

Obviously, you have to know what the value of the VBA variable sourceFile is, so let's get that:

sourceFile = "/Users/gadmin/Desktop/Quidzin/Master Images/" & ProductCode & ".psd"

This contains yet another VBA variable (ProductCode), so you'll have to substitute a literal value, for a file that exists. I can't guess what that would be, so I'm going to use "PLUGH" because a hollow voice told me to.

Next, manually perform all the Excel/VBA string concatenation to get the actual line of AppleScript. It should look like this:

set sourceFileAlias to "/Users/admin/Desktop/Quidzin/Master Images/PLUGE.psd" as alias

This looks like reasonable AppleScript, so you should copy and paste it into an AppleScript Editor window. Then run it. What happens? If you get an error message, post it. Error messages often contain valuable clues, so posting them helps others to help you.


The next step is to start working out what the cause of the error is. To do that requires some AppleScript knowledge.

First, change the as alias to as string and see if the resulting AppleScript works.

It does, so the basic syntax is correct, so the problem may lie in how aliases are named. You should look that up (search for applescript aliases), and it'll look something like this:

"MacHD:Users:admin:Desktop:Quidzin:Master Images:PLUGE.psd"​

This is the "classical" way of naming aliases.

For using Posix-style filenames there's a separate AppleScript class: Posix file. You can look that up, too (search for applescript posix file). Since it's a class, you can try it like this:

set sourceFileAlias to "/Users/admin/Desktop/Quidzin/Master Images/PLUGE.psd" as POSIX file

or like this:

set sourceFileAlias to Posix file "/Users/admin/Desktop/Quidzin/Master Images/PLUGE.psd"

When you run either of those, what's the output in AppleScript Editor? Is it an error message? If so post it. Is it a valid AppleScript result? Again, post it.

If it worked, then you have the solution for the first error in your AppleScript. Apply the same process to the second line. Then apply it to the third line. And so on.

If you get an error you can't figure out, post the complete AppleScript code that caused the error, and the complete error message.


Since the errors are in the AppleScript, not the VBA (so far as I can tell), you should test in AppleScript Editor, at least to the point after copying the file.

In short, break it down into parts, and run the parts in AppleScript Editor. Once those are working, you can go back to the VBA code and try them there. There's no reason to thrash around in VBA if the problem is one step removed in AppleScript.

 

[lkm_MAC]

Hi Chown33,

Many thanks for taking the amount of time it must have taken to go through and explain everything to me.

I had tried the POSIX file way as with my limited Mac knowledge i googled (other search engines are available) an awful lot and tried varying solutions.

This was originally Windows VBA code that i wrote and have gradually altered it beyond recognition now, i wrote all of the above code on my PC and emailed it to the designer, i will see if i can access his Mac tomorrow to split it down, i assume the AppleScript Editor is similar to PowerShell ISE?

I know that there are no errors other than the return value is VALUE# if the file exists and it returns the correct result of "Photo Needs Taking" if no file exists so something is working as it should.

Once again, thanks for your time.

Regards
Lee

 

[chown33]

I had tried the POSIX file way as with my limited Mac knowledge i googled (other search engines are available) an awful lot and tried varying solutions.

Programming is necessarily specific. That is, it needs a specific program. It's good that you googled and tried things, but without seeing exactly what you tried to run, I can't comment on it or make suggestions, because this description is simply too vague.

This was originally Windows VBA code that i wrote and have gradually altered it beyond recognition now, i wrote all of the above code on my PC and emailed it to the designer, i will see if i can access his Mac tomorrow to split it down, i assume the AppleScript Editor is similar to PowerShell ISE?

I don't use Windows at all, so I don't know what PowerShell ISE is. I can guess, and I could generally find out by looking for examples, but I'm not in a position to state its degree of resemblance to AppleScript Editor.

If you want to know what AppleScript Editor looks and acts like before you get a chance to try it, you should search for applescript editor tutorial or applescript editor examples, and look at their screenshots.

 

[lkm_MAC]

Hi Chown33,


I logged on remotely and got it to work, the first thing wrong was it needed to be to POSIX file.
The second was a silly mistake and that i was trying to duplicate it to a specified file rather than a folder.
3rd and finally was that i had the tell application blah all on 1 line, as soon a i split it down to separate lines it worked.

I really appreciate all your help.

Regards
Lee

 

[chown33]

Hi Chown33,


I logged on remotely and got it to work, the first thing wrong was it needed to be to POSIX file.
The second was a silly mistake and that i was trying to duplicate it to a specified file rather than a folder.
3rd and finally was that i had the tell application blah all on 1 line, as soon a i split it down to separate lines it worked.

I really appreciate all your help.

Regards
Lee

Glad to help.

Resolved?
https://forums.macrumors.com/threads/forum-feature-resolved-thread-prefix.1966177/