Using AppleScript to search an excel doc

Discussion in 'Mac Programming' started by MrWoofus, Dec 5, 2016.

  1. MrWoofus macrumors member

    MrWoofus

    Joined:
    Jun 20, 2016
    Location:
    Grubville, MO
    #1
    Need a hand for what seems (to me at least) to be a simple problem but, I can't seem to get it worked out.

    To date I've built one AS. Yes, a bit of a noob here... ;)

    Here's the scenario: I have an excel sheet that has basic info in it, I need to search for the info and have it return the result plus 3 cells next to it.

    For example:

    Col A Col B Col C Col D
    ------- ------- -------- -------
    WXYZ 239.12.1.1 123.45.1.2 1001

    1) Run a script that opens a basic search box for a specific sheet,
    2) Enter info (Will always be in Column A) to search
    3) Dialog box of some sort returns info in Col A, B, C, D

    (like)

    WXYZ
    -------
    239.12.1.1
    123.45.1.2
    1001



    The excel sheet could easily become a text doc, if that would be easier.

    Would this be doable with an AppleScript?

    (Thus far, I've got the script to open the file...that's it) :p

    Thanks for any help.
     
  2. superscape, Dec 6, 2016
    Last edited: Dec 13, 2016

    superscape macrumors 6502a

    superscape

    Joined:
    Feb 12, 2008
    Location:
    East Riding of Yorkshire, UK
    #2
    Hi there,

    Welcome to the world of AppleScript! I'll give you a couple of general pointers:

    1) Don't try to replicate what you do in the user interface e.g. "open a basic search box". Instead, look in the scripting dictionary for the app in question and try to figure it out from there.
    2) If you don't have it already, get a copy of Script Debugger. It'll make the above (and lots of other stuff) much easier.

    Now onto your specific problem. Although Excel is scriptable, personally I think scripting it is pretty horrible. I'd always rather export to CSV (or something text based) and use that as my input.

    I'd probably use something like this (on a CSV):

    Code:
    set theResult to do shell script "grep \"^WXYZ,\" " & quoted form of (POSIX path of (choose file))
    
    
    ...then split the parts of the returned file out using AppleScript's text item delimiters.

    Hope that helps set you off in the right direction!
     
  3. Efrem macrumors regular

    Joined:
    Jul 30, 2009
    #3
    Alternatively, Excel includes the Visual Basic for Applications language. It was designed to work with Excel and lets you trigger scripts by buttons on the worksheet and all sorts of stuff like that. Since you don't have a lot invested in AS at this point, why not use VBA?
     
  4. tkermit, Dec 13, 2016
    Last edited: Dec 13, 2016

    tkermit macrumors 68040

    tkermit

    Joined:
    Feb 20, 2004
    #4
    Something like this would work if you still wanted to use AppleScript:

    Code:
    use application "Microsoft Excel"
    use scripting additions
    
    display dialog "Enter search term" default answer ""
    try
        set i to first row index of (find (first column of used range of first worksheet) what text returned of result look at whole)
    on error
        display dialog "No match"
        return
    end try
    (get value of first worksheet's range ("A" & i & ":D" & i))'s first item
    display dialog (result's item 1) & return & "-------" & return & (result's item 2) & return & (result's item 3) & return & (result's item 4)
     
  5. MrWoofus thread starter macrumors member

    MrWoofus

    Joined:
    Jun 20, 2016
    Location:
    Grubville, MO
    #5
    Thanks all for the great replies. Sorry it's taken me so long to respond but, I've been trolling along trying AS & Python (Knowing little or nothing about either) ;)

    Ironically, I ended up using VBA to do it. Made it SO much easier with all of the corporate lockdowns on using anything out of the ordinary. Later today, I'm going to try and do a call to an API from the Excel sheet. (Prayers appreciated!) :p

    But I'm still going to try the AS that was posted, just because I'm a glutton for punishment.

    I WILL learn and become proficient at something, or die trying! :D
     

Share This Page