Converting VBA to Applescript for Excel 2008

Hi can anyone help me - I have a worksheet from Excel X that has a VBA script that lets you click a button that prints and resets a timesheet every fortnight

I could tinker with VBA or applescript but converting this is way above me

This is the script in VBA


’ Button1_Click Macro


Sub Button1_Click()
If (Cells(14, 9).Value <> “”) Then
ActiveSheet.Unprotect (“letmein”)
ActiveWindow.SelectedSheets.PrintOut Copies:=1 'print out the sheet
Cells(2, 2).Value = 1 + Cells(14, 1).Value 'start the next period from the day after the end of the current period
Cells(2, 8).Value = Cells(16, 9).Value 'balance carried forward to next period
Range(Cells(5, 2), Cells(14, 3)).Value = “” 'clear current entries
Range(Cells(5, 5), Cells(14, 7)).Value = “” 'clear current entries
ActiveSheet.Protect password:=“letmein”, DrawingObjects:=True, Contents:=True, Scenarios:=True

Else
Beep
Beep
End If

End Sub

If anyone could help me it would be greatly appreciated

Cheers

Chrisso

Model: Powermac G%
AppleScript: 2.1.2
Browser: Firefox 3.0.3
Operating System: Mac OS X (10.5)

Thank you.
I’m also learning AppleScript from a VBA background and this was a good practice code for me.
The MicroSoft website, where I got these Excel2004 AppleScript commands, has a section on VBA to AppleScript.

If more experienced scripters have comments, I would appreciate them.
(Is my variable oneDay really needed?)

I couldn’t make the beep, so I put up a dialog box instead.

tell application "Microsoft Excel"
	set oneday to 24 * 60 * 60
	
	set previousDay to value of range "A14"
	if class of previousDay as text = "date" then
		print out active sheet
		
		unprotect active sheet password "password"
		set value of range "B2" to previousDay + oneday
		
		set previousBalance to value of range "i16"
		set value of range "H2" to previousBalance
		
		clear contents range "B5:C14, E5:F14"
		protect worksheet active sheet password "password" with drawing objects, worksheet contents and scenarios
	else
		display dialog "No date in A14"
	end if
end tell

Thanks for such a quick reply - I gave it a quick run before I go It prints and clears the data from some cells but leaves other and does not change to the new date

I will be away for a couple of days and give it a closer look but initially it is great

Cheers

Chris

no, 24 * 60 * 60 is the same as days

beep 2

beeps twice

StefanK,
Thanks for the tips.

Thanks very much for the conversion - with a couple of minor tweaks it seems to be working perfectly

One query The original VBA script was assigned to a button - any suggestions as to how I might reassign script to the button

Button1_Click

Cheers

Chris

No clue as to how to assign it to a button.
Does 2008 have buttons that can be put on a spread sheet? I don’t see why it would? Other controls from the Forms menu would have a function, but, since VBA is not supported, a command button has no function.

I think that one can put icons on the dock or top bar that will call an AppleScript. perhaps someone else on the board knows how to do that.