Opening and Open or Closed Worksheet

Scripting Forums AppleScript | Mac OS X
,
#1

I have the following script which works fine if the worksheet isn’t open but it fails when it is open. I need this script to work even if it is open. It will be set up to post to the worksheet on a timer through calendar and it may be posting when the worksheet is open or it may be closed.


tell application "Microsoft Excel"
	open "/Users/abc/Documents/AppleScripts/Allocation.xlsx"
	
	tell application "Microsoft Excel"
		
	-- allow for the first 4 rows before you start testing for a blank row
	
		repeat with x from 4 to (count rows of active sheet)
			if value of cell 1 of row x = "" then exit repeat
		end repeat
	end tell
	
	
	
	
	-- post Monthly allocation on next available row with the current date and Month

	set today to short date string of (current date)
	set monthName to month of (current date) as text
	
	set value of cell ("A" & x) to today
	set value of cell ("B" & x) to "Monthly Allocation"
	set value of cell ("C" & x) to monthName & " Allocation"
	set value of cell ("D" & x) to 30.0
	
end tell

Thanks

Randal

#2

Hello.

This works for me in MsExcel 2008

set wbName to "Allocation.xlsx"
set success to false
set itisOpen to false
tell application "Microsoft Excel"
	activate
	try
		set a to name of every workbook
		set success to true
	end try
	if success is true then
		if wbName is in a then
			set itisOpen to true
			activate object window wbName
		end if
	end if
	if not itisOpen then
		open "/Users/abc/Documents/AppleScripts/Allocation.xlsx"
	end if
	repeat with x from 4 to (count rows of active sheet)
		if value of cell 1 of row x = "" then exit repeat
	end repeat
	-- post Monthly allocation on next available row with the current date and Month
	
	set today to short date string of (current date)
	set monthName to month of (current date) as text
	
	set value of cell ("A" & x) to today
	set value of cell ("B" & x) to "Monthly Allocation"
	set value of cell ("C" & x) to monthName & " Allocation"
	set value of cell ("D" & x) to 30.0
end tell
#3

this works great, thanks a lot!