excel - linkto cell reference

Scripting Forums AppleScript | Mac OS X
, ,
#1

Hey All,

Wondering if anyone knows how to go about doing this or has some code to provide some direction

Overview:

  1. Excel has the option to provide a linkto function, essentially you can make the cell contents a hyperlink or what I’m interested in is a cell reference.
    A. current GUI workflow is:
    1. select a cell
    2. command+k (access the interface)
    3. Enter the cell reference ex. “#‘Sheet1’!A1”
    4. Click Ok to enable

Question:

  1. Does anyone have some code that can do this?

Coding:

  1. How do you copy a string into the clipboard?
set the clipboard to strgVal

Pseudocode for above would be:
datastructure:
loop until end of list
List: 1,2,3,4,5

  1. select cell refercence A1

  2. copy list entry 1 to clipboard

  3. system events to press command k

  4. system event paste copyboard content

  5. system event enter

set strgVal to "asfasf"
	set the clipboard to strgVal
	
	tell application "System Events"
		keystroke "v" using {command down}
		keystroke return
	end tell
	delay 2
  1. move to cell A2

any help would be appreciated.
TIA

#2

Hi,

why are you poking around with GUI scripting and the clipboard?
Microsoft Excel is extraordinarily scriptable


tell application "Microsoft Excel"
	make new hyperlink of active cell at active sheet with properties {address:"Sheet1!A1"}
end tell

Take a look at the great Excel 2004 AppleScript Reference

#3

Thanks for the reference.

problems:


tell application "Microsoft Excel"
	activate
	make new hyperlink of active cell at active sheet with properties {address:"'Sheet2!A1'"}
end tell

– doesn’t work when clicked
– error occurs, it’s expecting an URL to work


	make new hyperlink of active cell at active sheet with properties {address:"'#Sheet2!A1'"}

– adds ‘%23Sheet2!A1’#Sheet2!A1’ to the address

question:
How do i get the input #Sheet2!A1? any help would be appreciated.

#4

solution:

tell application "Microsoft Excel"
	activate
	make new hyperlink of active cell at active sheet with properties {sub address:"Sheet2!A1", address:""}
end tell