Register FAQ / Rules Forum Spy Search Today's Posts Mark Forums Read
Go Back   MacRumors Forums > Apple Systems and Services > Mac Basics and Help

Reply
 
Thread Tools Search this Thread Display Modes
Old Nov 17, 2012, 12:35 PM   #1
oyl2008
macrumors newbie
 
Join Date: Nov 2012
Location: London UK
How to create the list of worksheets in an excel file? 2008

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
__________________
NAYIO
oyl2008 is offline   0 Reply With Quote
Old Nov 17, 2012, 07:20 PM   #2
kryten2
macrumors 6502a
 
Join Date: Mar 2012
Location: Belgium
Quote:
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
Attached Thumbnails
Click image for larger version

Name:	Picture 5.png
Views:	7
Size:	91.2 KB
ID:	377991   Click image for larger version

Name:	Picture 6.png
Views:	9
Size:	97.1 KB
ID:	377992  
__________________
Space Corps Directive 34124
kryten2 is offline   0 Reply With Quote

Reply
MacRumors Forums > Apple Systems and Services > Mac Basics and Help

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -5. The time now is 02:39 AM.

Mac Rumors | Mac | iPhone | iPhone Game Reviews | iPhone Apps

Mobile Version | Fixed | Fluid | Fluid HD
Copyright 2002-2013, MacRumors.com, LLC