Applescript for getting data from a spreadsheet

[Bob Sneidar]

This may be a little off topic, but the goal is to create an app that looks up mac addresses from a telnet session into a series of procurve switches, and then compare that with a known list of mac addresses in an excel spreadsheet, and trace them through a network to find where the device is at. I do this regularly by hand, but I want to make a stack that can do it.

I read through the dictionary of Microsoft Excel to try to find a way to get the data from a range of cells, but as usual, nothing works in Applescript like you would expect, and of course the dictionary provides no examples of how the command should be written. All the help forums are useless because the advice people give there is usually either outdated, or simply wrong on it's face.

So does anyone have any experience using Applescript to select a range of cells in a certain sheet of a certain workbook? If I could get that far, I think I could just copy/paste the data into a datagrid and go from there. BTW I am using Office 2008 (for Mac of course). This is important because apparently they rewrote the dictionary between 2004 and 2008 and a lot of things that used to work in 2004 do not anymore.

Bob
_______________________________________________
use-livecode mailing list
use-li...@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

[zryip theSlug]

On Fri, Dec 9, 2011 at 5:49 PM, Bob Sneidar <bo...@twft.com> wrote:
> This may be a little off topic, but the goal is to create an app that looks up mac addresses from a telnet session into a series of procurve switches, and then compare that with a known list of mac addresses in an excel spreadsheet, and trace them through a network to find where the device is at. I do this regularly by hand, but I want to make a stack that can do it.
>
> I read through the dictionary of Microsoft Excel to try to find a way to get the data from a range of cells, but as usual, nothing works in Applescript like you would expect, and of course the dictionary provides no examples of how the command should be written. All the help forums are useless because the advice people give there is usually either outdated, or simply wrong on it's face.
>
> So does anyone have any experience using Applescript to select a range of cells in a certain sheet of a certain workbook? If I could get that far, I think I could just copy/paste the data into a datagrid and go from there. BTW I am using Office 2008 (for Mac of course). This is important because apparently they rewrote the dictionary between 2004 and 2008 and a lot of things that used to work in 2004 do not anymore.

Hi Bob,

Did you try something like this?

tell application "Microsoft Excel"
set myRange to range "A1:A5" of sheet "mySheet" of workbook "myWorkbook"
return value of myRange
end tell

This should return a list of values between {} and separated by commas.

Best regards,
--
-Zryip TheSlug- wish you the best! 8)
http://www.aslugontheroad.com

[Bob Sneidar]

Thanks man! Can I come sit at you feet and learn of your greatness?? ;-) Seems though that Excel locked up. It works for small selections, but Excel didn't like it when I did that for 4 columns of 4000+ rows. Also, I wonder if referencing entire columns might try to get all the data in all the *possible* rows instead of all the rows that have data in them? That is why I was trying to get Excel to actually SELECT the columns and then copy them to the clipboard.

Maybe I should move my Excel data to a Livecode stack. Hmmm...

Bob

[zryip theSlug]

On Fri, Dec 9, 2011 at 9:06 PM, Bob Sneidar <bo...@twft.com> wrote:
> Thanks man! Can I come sit at you feet and learn of your greatness?? ;-) Seems though that Excel locked up. It works for small selections, but Excel didn't like it when I did that for 4 columns of 4000+ rows. Also, I wonder if referencing entire columns might try to get all the data in all the *possible* rows instead of all the rows that have data in them? That is why I was trying to get Excel to actually SELECT the columns and then copy them to the clipboard.
>
> Maybe I should move my Excel data to a Livecode stack. Hmmm...

No need to select the columns, you can copy them directly in the clipboard.

An example for copying columns A to D:

tell application "Microsoft Excel"
activate
COPY RANGE range "A:D" of sheet "mySheet" of workbook "myWorkBook"
end tell

Note that the command is named COPY RANGE, so don't forget to repeat
the word "Range" two times.

Maybe you will be happy to know, I'm actually working on an Excel
Library for Livecode.
The library will covered the following topics:

- Excel Application
- Window
- Workbook
- Sheet
- Range
- Chart
- Shape
- Printing

The library will come for mac and windows.

[Bob Sneidar]

Very cool! I guess what I was doing wrong before is that I was not specifying the sheet and workbook name. It kept saying in it's cryptic way that it didn't understand the copy range command. <sigh> Applescript. I love it. I hate it.

Bob

[Bob Sneidar]

Ok now my problem is I am trying to open the file via Applescript. The dictionary says:
open workbook v : Opens a workbook.

Hence this appleScript:

tell application "Microsoft Excel"
activate

set the default file path to "Volumes/data/LogosAdmin/TechData/Tech Documents/"
open workbook "DHCP Clients.xlsx"
copy range range "B:E" of sheet "DHCP Clients.xls" of workbook "DHCP Clients.xlsx"
end tell

It stops at open workbook "DHCP Clients.xlsx":
error "Microsoft Excel got an error: \"DHCP Clients.xlsx\" doesn’t understand the open workbook message." number -1708 from "DHCP Clients.xlsx"

I feel like an ignorant putz, but shouldn't I be able to open a frickin workbook like this?? With Applescript I feel like if I type two spaces where it expected one the computer will shut down and never restart again just to punish me for being such an imbecile.

Bob

On Dec 9, 2011, at 12:54 PM, zryip theSlug wrote:

[zryip theSlug]

On Fri, Dec 9, 2011 at 11:32 PM, Bob Sneidar <bo...@twft.com> wrote:
> Ok now my problem is I am trying to open the file via Applescript. The dictionary says:
> open workbook v : Opens a workbook.
>
> Hence this appleScript:
> tell application "Microsoft Excel"
>        activate
>        set the default file path to "Volumes/data/LogosAdmin/TechData/Tech Documents/"
>        open workbook "DHCP Clients.xlsx"
>        copy range range "B:E" of sheet "DHCP Clients.xls" of workbook "DHCP Clients.xlsx"
> end tell
>
> It stops at open workbook "DHCP Clients.xlsx":
> error "Microsoft Excel got an error: \"DHCP Clients.xlsx\" doesn’t understand the open workbook message." number -1708 from "DHCP Clients.xlsx"
>
>
> I feel like an ignorant putz, but shouldn't I be able to open a frickin workbook like this?? With Applescript I feel like if I type two spaces where it expected one the computer will shut down and never restart again just to punish me for being such an imbecile.

8-)

The Excel documentation has numerous traps. Sometimes the syntax is
natural and sometimes... surnatural. ;-)

Try:
open workbook workbook file name "DHCP Clients.xlsx"

Best regards,
--
-Zryip TheSlug- wish you the best! 8)
http://www.aslugontheroad.com

_______________________________________________

[Bob Sneidar]

> 8-)
>
> The Excel documentation has numerous traps. Sometimes the syntax is
> natural and sometimes... surnatural. ;-)
>
> Try:
> open workbook workbook file name "DHCP Clients.xlsx"
>
>
> Best regards,
> --
> -Zryip TheSlug- wish you the best! 8)
> http://www.aslugontheroad.com

I get: error "Microsoft Excel got an error: Can’t continue open workbook." number -1708

By the way I know the path is correct.

I tried with the full path:
open workbook workbook file name "/Volumes/data/LogosAdmin/TechData/Tech Documents/DHCP Clients.xlsx"

I tried the short name:

open workbook workbook file name"DHCP Clients.xlsx"

I tried setting the default path:
set default file path to "/Volumes/data/LogosAdmin/TechData/Tech Documents/"

open workbook workbook file name "DHCP Clients.xlsx"

Thanks for trying zryip. This has reminded me why after great hopes that Applescript could replace Hypercard (in some sense), why I was driven to despair and went looking for some kind of hypercard-like software. That was when I found Revolution/Livecode. Applescripters are at the mercy of the people in the software developers employ concerning how they implement their dictionary, and how they document it. Places where enumeration makes all the sense in the world, you might find they didn't implement it. Places where a simple argument like the full path to a file, as in "open workbook" simple does not work like that, and there is NOTHING but NOTHING in the way of developer documentation to explain how the hell they want you to do things! That is insanity waiting to happen, and I am already crazy so there is no new ground to cover here.

By the way, I know the path is correct. I copy/pasted it from the Get Info window.

Bob

[stephen barncard]

have you used

revUnixFromMacPath(*macPathname*[,*convertOSX*])

to clean up the pathname for AS?

--

Stephen Barncard
San Francisco Ca. USA

more about sqb <http://www.google.com/profiles/sbarncar>

[zryip theSlug]

On Sat, Dec 10, 2011 at 1:03 AM, Bob Sneidar <bo...@twft.com> wrote:
>> 8-)
>>
>> The Excel documentation has numerous traps. Sometimes the syntax is
>> natural and sometimes... surnatural. ;-)
>>
>> Try:
>> open workbook workbook file name "DHCP Clients.xlsx"
>>
>>
>> Best regards,
>> --
>> -Zryip TheSlug- wish you the best! 8)
>> http://www.aslugontheroad.com
>
> I get: error "Microsoft Excel got an error: Can’t continue open workbook." number -1708
>
> By the way I know the path is correct.
>
> I tried with the full path:
> open workbook workbook file name "/Volumes/data/LogosAdmin/TechData/Tech Documents/DHCP Clients.xlsx"
>
> I tried the short name:
> open workbook workbook file name"DHCP Clients.xlsx"
>
> I tried setting the default path:
> set default file path to "/Volumes/data/LogosAdmin/TechData/Tech Documents/"
> open workbook workbook file name "DHCP Clients.xlsx"

The problem should be in the unix path.

Try:
set myPath to POSIX file "/Volumes/data/LogosAdmin/TechData/Tech
Documents/DHCP Clients.xlsx"
open workbook workbook file name myPath

Best regards,
--
-Zryip TheSlug- wish you the best! 8)

http://www.aslugontheroad.co.cc

[Bob Sneidar]

No. Right now I am just trying to put the script together in the Applescript Editor.

Bob

On Dec 9, 2011, at 4:29 PM, stephen barncard wrote:

> have you used
>
> revUnixFromMacPath(*macPathname*[,*convertOSX*])
>
>
> to clean up the pathname for AS?

[Bob Sneidar]

Hey! That did the trick! Now how would anyone know that? Let's say I was the first person ever to begin working with Applescript. How would I have ever come up with that solution? But thank you. Now I can get on with my uber utility.

Bob

[Bob Sneidar]

Thanks to all who offered help on Applescript and Shell access. I now have a functioning stack that will query a Procurve switch for mac addresses, and compare it to my Spreadsheet data to show me what device is using that mac address, and on which port on the switch it is.

Next phase: Go through a list of Procurve switches and plot the path to get to the endpoint. At some point I can add the functionality of querying different kinds of switches. Each switch *can* use a different CLI syntax to get the addresses, and some switches (even managed ones) do not even have a command for that. Some Dell switches come to mind. Some do not allow telnet, so it is not going to be a perfect system, but it will work for my purposes at least, and I will give anyone who wants it a copy of the stack.

Bob

On Dec 9, 2011, at 4:35 PM, zryip theSlug wrote: