Iterate over Excel sheets

Scripting Forums AppleScript | Mac OS X
#1

Hi all,

i am quite new to applescript and i can’t figure out how to iterate over all available sheets within an excel document. I tried the following:

set theDoc to (path to desktop as text) & "spreadsheet.xls"
set resultList to {}

tell application "Microsoft Excel"
	-- open file theDoc
  set sourceBook to open workbook workbook file name (theDoc as text)
	tell sourceBook
    repeat with oneSheet in (get worksheets of sourceBook) as list
  		tell oneSheet
  			copy {(value of range "a1:g50" in oneSheet)} to end of resultList
  		end tell
		end repeat
		close without saving
	end tell
end tell
return resultList

with an Excel file that has 2 sheets but i only get the values from the first sheet. resultList should be a list of lists of lists where: resultList[sheet][x][y]

I am calling this code snippet from within InDesign by using the doScript function since Javascript can’t read Excel files.

Any help is greatly appreciated. Regards,
Gerold

#2

Hi,

your script works in a normal AppleScript environment although there are some (double) reference problems
Try this


set theDoc to (path to desktop as text) & "spreadsheet.xls"
set resultList to {}

tell application "Microsoft Excel"
	-- open file theDoc
	open file theDoc
	repeat with oneSheet in (get worksheets of active workbook)
		copy {(value of range "a1:g50" in oneSheet)} to end of resultList
	end repeat
	close active workbook without saving
	
end tell
return resultList
#3

Hi Stefan,

thanks for your reply, works like a charm!

Regards,
Gerold