macOS Using AppleScript to search an excel doc

[MrWoofus]

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)

Thanks for any help.

 

[superscape]

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):

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!

 

[Efrem]

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?

 

[tkermit]

Something like this would work if you still wanted to use AppleScript:

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)

 

[MrWoofus]

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!)

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!