Applescripting for Excel 2004:mac

[Bubbleboy]

I am a relative novice at Applescripting and even less experienced at
scripting Excel. I am attempting to write Applescript that will
manipulate Excel to do the following:

paste a webpage, which I copied to the clipboard, to a certain cell in
a worksheet
then increment the location of the active cell in that worksheet such
that I can paste the next webpage I will copy to the clipboard into a
different cell on the same worksheet

PS I would like to paste the clipboard items such that they keep their
HTML formatting

Here is my pathetic attempt at the script language which results in
the clipboard contents being pasted over the preceding paste.

FYI: I set x to 1, earlier in the script

tell application "Microsoft Excel"
activate
activate object workbook "A.xls"
activate object sheet "Sheet1"
goto cell {1, x + 14}
paste worksheet active sheet
delay 3
set x to x + 14
..........................
end tell

Any help would result in saving my family and our cat from the
collateral damage caused by my tirades.

Thanks,
Monkeyboy

 

[Bubbleboy]

I am a relative novice at Applescripting and even less experienced at
scripting Excel. I am attempting to write Applescript that will
manipulate Excel to do the following:

paste a webpage, which I copied to the clipboard, to a certain cell in
a worksheet
then increment the location of the active cell in that worksheet such
that I can paste the next webpage I will copy to the clipboard into a
different cell on the same worksheet

PS I would like to paste the clipboard items such that they keep their
HTML formatting

Here is my pathetic attempt at the script language which results in
the clipboard contents being pasted over the preceding paste.

FYI: I set x to 1, earlier in the script

tell application "Microsoft Excel"

activate
activate object workbook "A.xls"
activate object sheet "Sheet1"
paste worksheet active sheet destination (cell {"1", x})
delay 3
activate object window "B.xls"
activate object sheet "Electric Only"
delete row 2
delay 2
cut range cell "a2"
set closer to value of cell "a2"
end tell
set x to x + 3

 

[Paul Berkowitz]

OK< there are a few issues here.

1) The way to refer to cells is by their "A1" addresses. The 'paste
worksheet' commands gives you 3 ways to refer to the range you're pasting
to:

[destination - range object/A1-style range reference/named range]

Named ranges are special set-ups, a range object would presumably mean a
range previously copied to a variable (since 'range object' doesn't even
exist as a data type in the dictionary), so let's stick with A1-style range
reference.

That would mean

cell "A1"
cell "D1"

For example. Those strange references you're giving in list brackets {"1",
x} are causing the errors.

So this works for me:

tell application "Microsoft Excel"
paste worksheet destination cell "D1"
end tell


You could find various ways to increment column by column. For example:

tell application "Microsoft Excel"
set x to 1
set currentCell to get address of cell x of row 1 of active sheet
paste worksheet active sheet destination (cell currentCell of active
sheet)
set x to x + 1
end tell


(You need the 'get address' because just getting the cell and trying to
re-use a variable set to it is buggy. The result is a 'row' and it just
defaults to cell A1 every time).

That's actually using a 'range object'.


2) That does not preserve HTML - it pastes as text if you have a Safari
webpage on the clipboard.

It looks as though 'paste special on worksheet' _ought_ to be able to paste
HTML (although it doesn't allow you to pick a cell as destination). After
all, it even has a 'no HTML formatting' parameter, whose default is false,
which should paste HTML automatically, or so it seems. But it doesn't, Using
the 'format' parameter this way doesn't help either:

paste special on worksheet active sheet format "HTML"

Perhaps there's some other way. But I very much doubt it. The reason I say
so is that there is no way I can find to paste as HTML from the clipboard in
the UI either. Do you know any way? Using Edit/Paste gets just text, and
Edit/Paste Special... Offers only two options: Unicode Text or Text. Both
end up as just text, no HTML.

Contrast that with pasting into TextEdit, which pastes just about perfectly
as HTML. (Word is weird: you get a bit of the formatting but not much. Paste
Special offers RTF but not HTML, which gets a bit more. You'd have to save
the webpage and Open as WebPage. I don't think Excel offers anything like
that.)

So whatever HTML format content is saved to the clipboard from Safari, it's
not accessible to Excel, although it is to TextEdit (maybe it's saved in
some sort of Cocoa way). You're out of luck here. But at least you know how
to refer to cells now...


--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.