Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

oyl2008

macrumors newbie
Original poster
Nov 17, 2012
1
0
London UK
Hi to everyone, Is there a way to create a list of all the worksheets in the same excel file in excel 2008 please?
I have tried every way wanting to avoid doing the hyperlink one by one. All I found was the macro for PCs nothing out there for excel 2008 for some reason. I am working with an excel file with at least 100 sheet so I cannot type in the name of every single sheet or create the hyperlink one by one...

Many thanks
 

kryten2

macrumors 65816
Mar 17, 2012
1,114
99
Belgium
Is there a way to create a list of all the worksheets in the same excel file in excel 2008 please?
Don't know if this is what you asked for but you can try this Applescript. The script will create a new worksheet named Index and creates hyperlinks to all the worksheets in your Excel file. Open Script Editor and copy the code into the Script Editor window then compile and run it.

Note : Tested with Microsoft Office 2004

Code:
-- Change the line starting with set theDoc to the location of your Excel file
-- In the example the line translates to "LeopardFirewire:Users:test:Documents:Workbook1.xls"
-- LeopardFirewire is the name of my hard drive.
set theDoc to (path to documents folder as text) & "Workbook1.xls"
tell application "Microsoft Excel"
	activate
	-- open file theDoc
	open file theDoc
	make new worksheet at beginning of active workbook with properties {name:"Index"}
	set worksheetList to every worksheet of active workbook
	set i to 1
	set theCell to "A"
	repeat with aworkSheet in worksheetList
		set aworkSheetName to aworkSheet's name
		if aworkSheetName is not "Index" then
			set theValue to theCell & i
			activate object worksheet "Index"
			activate object range theValue of worksheet "Index"
			make new hyperlink at worksheet "Index" with properties {sub address:aworkSheetName & "!A1", text to display:aworkSheetName, address:""}
			set i to i + 1
		end if
	end repeat
end tell
 

Attachments

  • Picture 5.png
    Picture 5.png
    91.2 KB · Views: 54
  • Picture 6.png
    Picture 6.png
    97.1 KB · Views: 58
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.