"Check if Sheet Exists"/Sum of cell range

Scripting Forums AppleScript | Mac OS X
#1

Greetings all!

I’ve looked for help all over, and have found no solid answers. I have two questions. 1.) Is it possible to check whether a sheet exists(or active), then perform the following code…if it is True?

  1. How do you to take a range of cells and add their values, in apple script? I’ve tried many different ways and I continuously get “error “Microsoft Excel got an error: The object you are trying to access does not exist” number -1728 from range {”=sum(B98:B102)“}”

Here’s a sample of my code:
if active sheet name is “January”
select sheet “January”
copy range (range “B3:B7”) destination range “B3:B7” of sheet “Summary YTD”
copy range (range “B11:B17”) destination range “B11:B17” of sheet “Summary YTD”
copy range (range “B23:B64”) destination range “B23:B64” of sheet “Summary YTD”
copy range (range {“=sum(B98:B102)”}) destination range “B98” of sheet “Summary YTD”
select sheet “Summary YTD”
else
if active sheet name is “February”
…
end tell

Thanks for the help!!

#2

Hi,

to check if a sheet exists use the exists command


tell application "Microsoft Excel"
	if exists sheet "January" then
		-- do something
	end if

your sample code could be accomplished like


tell application "Microsoft Excel"
	set activeSheetName to name of active sheet
	if activeSheetName is "January" then
		select sheet "January"
		copy range (range "B3:B7") destination range "B3:B7" of sheet "Summary YTD"
		copy range (range "B11:B17") destination range "B11:B17" of sheet "Summary YTD"
		copy range (range "B23:B64") destination range "B23:B64" of sheet "Summary YTD"
		copy range (range {"=sum(B98:B102)"}) destination range "B98" of sheet "Summary YTD"
		select sheet "Summary YTD"
	else if activeSheetName is "February" then
		-- .
	end if
end tell

to add the values of a range of cells you could use a repeat loop


tell application "Microsoft Excel"
	set allValues to value of range "A1:A5"
	set totalSum to 0.0
	repeat with aValue in allValues
		set totalSum to totalSum + (item 1 of aValue)
	end repeat
end tell
#3

Greetings StefanK!

You are awesome!! Thank you for the quick reply!! The addition code works perfectly. I set two cells to 30 and 30 (arbitrary amounts). I have only one problem when trying to paste the result to the “Summary YTD” range. the following error:
error “Can’t get value of 60.0.” number -1728 from «class DPVu» of 60.0

when I use this code:

set totalSum to 0
	repeat with aValue in allValues
		set totalSum to totalSum + (item 1 of aValue)
	end repeat
	copy value of totalSum to of range "B98" of sheet "Summary YTD"

I’ve also tried:
set value of totalSum to of range “B98” of sheet “Summary YTD”

but get
Can’t set «class DPVu» of 60.0 to «class ccel» “B98” of «class X128» “Summary YTD” of application “Microsoft Excel”.

Thanks again for the help! I’m new to AppleScripting.

#4

almost


set value of range "B98" of sheet "Summary YTD" to totalSum
#5

I should have tried that next! Thank you so much for your help! That worked perfectly!!

#6

As a follow up, if you are thinking of charting data in excel via AS, Stefan and I set this out in a 2010 post. As he is so helpful with many posts, it might be easier to look up my posts (only 15) and you will see one on Excel charts. That’s the one I am referring to.

#7

TimMurphy,

Thanks for the input! I will definitely check the page. It will pay dividends for my worksheet project! Thanks gentlemen.