OK-any Excel Scripters out there?

Scripting Forums AppleScript | Mac OS X
#1

Hello,

I have a schedule that I create in excel and I have used a VBScript that does the following:

When I type in a date, if that date lands on a Saturday, it changes that date automatically to the Friday before.
Same thing for Sunday’s-IF the date lands on a Sunday, it automatically changes that date to the following Monday.

I really want to do my schedules on the MAC, and was wondering if Applescript could do something like that. I don’t think it would be on formatting the cell, but maybe a script that finds every date in a document that falls on a Saturday and changes that date to the friday before, and finds any date that lands on a Sunday and changes it to Monday after?

I wouldn’t even know where to begin with this one, as I don’t use excel all that often except for some pretty simple stuff.

Thanks!
babs

#2

You might want to use Excel for that, a helper column with =A1+CHOOSE(WEEKDAY(A1,1),1,0,0,0,0,0,-1) should do what you want.

If you want an applescript, this should work

tell application "Microsoft Excel"
	set mySheet to sheet "Sheet1" of workbook "Workbook1.xls"
	set columnOfDates to column 1 of mySheet
	
	set LastRow to first row index of (get end (cell -1 of columnOfDates) direction toward the top)
	set dataRange to get resize (cell 1 of columnOfDates) row size LastRow
	
	set formula r1c1 of (get offset dataRange column offset 1) to "=RC1 + CHOOSE(WEEKDAY(RC1,1),1,0,0,0,0,0,-1)"
	
	set value of dataRange to get value of (get offset dataRange column offset 1)
	clear contents (get offset dataRange column offset 1)
end tell
#3

HI Mike,

This looks promising!
I will keep you posted on how it works out!!
thanks so much!
babs

#4

HI Mike,

OK-I played with this a bit.

The AppleScript is working, but it deletes everything in my sheet other than the first column? I have been trying to figure out how to get it to look at every column in the document, make the changes , and not delete anything, but I have not been successful.

As far as doing it right inside of excel, since I do not use excel all that much, I had some trouble figuring out what a “helper column” was. I looked it up and saw some examples, but I m still having difficulty putting it in this situation.

Anymore help is greatly appreciated… and thanks for all of this so far!!!
babs

#5

Try this

tell application "Microsoft Excel"
	activate
	display dialog "Change dates away from weekends on the active sheet." & linefeed & "This can not be un-done."
	
	set cCells to special cells (used range of active sheet) type cell type constants
	set cAreas to areas of cCells
	
	repeat with aIndex from 1 to count of cAreas
		set oneArea to (item aIndex of cAreas)
		repeat with i from 1 to count of cells of oneArea
			set oneCell to (cell i of oneArea)
			set oneVal to get value of oneCell
			if class of oneVal = date then
				set thisDay to weekday of (get value of oneCell)
				if thisDay = Sunday then
					set value of oneCell to ((value of oneCell) + (60 * 60 * 24))
				else if thisDay = Saturday then
					set value of oneCell to (value of oneCell) - (60 * 60 * 24)
				end if
			end if
		end repeat
	end repeat
	
	display dialog "done"
end tell
#6

hi Mike!!!

You are a genius!!!
PERFECT!!!
Thank you so much for all your help…
I have been playing around and doing better with this stuff, but since I really don’t know excel all that well, I got into the dictionary and was completely lost!!!
This is great, I cannot thank you enough :lol:

babs