How to create the list of worksheets in an excel file? 2008

Discussion in 'Mac Basics and Help' started by oyl2008, Nov 17, 2012.

  1. oyl2008 macrumors newbie

    Nov 17, 2012
    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
  2. kryten2 macrumors 6502a

    Mar 17, 2012
    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

    -- 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"
    	-- 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 Files:

Share This Page