Resolved Apple/MacScript Help

Discussion in 'Mac Programming' started by lkm_MAC, Jun 2, 2016.

  1. lkm_MAC macrumors newbie

    Joined:
    Jun 2, 2016
    Location:
    Derby, UK
    #1
    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.

    Code:
    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
     
  2. superscape macrumors 6502a

    superscape

    Joined:
    Feb 12, 2008
    Location:
    East Riding of Yorkshire, UK
    #2
    Maybe I'm asking a dumb question, but does it definitely need to be part of an Excel spreadsheet?
     
  3. lkm_MAC thread starter macrumors newbie

    Joined:
    Jun 2, 2016
    Location:
    Derby, UK
    #3
    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
     
  4. chown33, Jun 2, 2016
    Last edited: Jun 2, 2016

    chown33 macrumors 604

    Joined:
    Aug 9, 2009
    #4
    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:
    Code:
    "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:
    Code:
    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:
    Code:
    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:
    Code:
    set sourceFileAlias to "/Users/admin/Desktop/Quidzin/Master Images/PLUGE.psd" as POSIX file
    
    or like this:
    Code:
    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.
     
  5. lkm_MAC thread starter macrumors newbie

    Joined:
    Jun 2, 2016
    Location:
    Derby, UK
    #5
    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
     
  6. chown33 macrumors 604

    Joined:
    Aug 9, 2009
    #6
    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.

    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.
     
  7. lkm_MAC thread starter macrumors newbie

    Joined:
    Jun 2, 2016
    Location:
    Derby, UK
    #7
    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
     
  8. chown33 macrumors 604

    Joined:
    Aug 9, 2009
    #8
    Glad to help.

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

Share This Page