*New to AppleScript* Converting VBA to AppleScript.

Greetings!

I am new to AppleScript, and i was recommended to this forum. So, I thank you in advance your help!

I created a program/workbook in Excel 2011 using VBA macros. The users of the program consequently use Excel 2008 which does not utilize macros. I have a few buttons on the spreadsheet that perform certain functions. Can someone help guide me to where to start with these? Again, I appreciate any help I can get!!
1.) How do I attach the following code to a button using AppleScript, I’d prefer if it’s the same button as the Macro-button.
2.) If I send this file to another user(with Excel 2008), what would they have to do in oder for the program to work?

My code::

'Check to see if worksheet exits
Public Function SheetExists(ByVal SheetName As String) As Boolean

Dim i As Integer
With ActiveWorkbook
For i = 1 To Sheets.Count
If Sheets(i).Name = SheetName Then
SheetExists = True
Exit Function
End If
Next
SheetExists = False
End With
End Sub
'Send to Summary page
Sub SummaryCells()

Dim ws As Worksheet
Sheets(“Summary YTD”).Select
Sheets(“Data”).Visible = False

For Each ws In Worksheets
If ws.Name Like “Jan” Then
Sheets(“Summary YTD”).Range(“B3:B7”).Value = Sheets(“Jan”).Range(“B3:B7”).Value
Sheets(“Summary YTD”).Range(“B98”).Value = Application.WorksheetFunction.Sum(Sheets(“Jan”).Range(“B98:B102”))
End If
If ws.Name Like “Feb” Then
Sheets(“Summary YTD”).Range(“B3:B7”).Value = Sheets(“Feb”).Range(“B3:B7”).Value
Sheets(“Summary YTD”).Range(“B98”).Value = Application.WorksheetFunction.Sum(Sheets(“Feb”).Range(“B98:B102”))
End If

'This line of code continues through December.

End Sub

Here are links to neat tutorials :

http://bernard.rey.free.fr/ExcelVBA-AppleScript.doc

http://www.mactech.com/vba-transition-guide/index-004.html

https://msdn.microsoft.com/en-us/library/office/aa192490(v=office.11).aspx

Yvan KOENIG (VALLAURIS, France) lundi 16 février 2015 21:51:08

Thanks Yvan!!

Those sites provided great tutorials and insights that are helping me in other areas! However, there are two issues I’m not finding answers on:

  1. Making a hidden template sheet visible for editing, then hide again. Specifically, In Mac 2011, I’m able to unhide a template, copy it, change the name, and hide it again.

  2. Making a cell(range) a pseudo button since buttons/Macros do not work in Excel 2008. I don’t want the AppleScript to perform these functions automatically. Any help would be greatly appreciated!!

Thank you in advance!

I am travelling at the moment without a laptop, but if memory serves me right, you can create a button in Ms excel, assign it to a “MacScript()” command which you need to link to your script, and it will run. I can’t remember the exact syntax off the top of my head, but this is the only way, though i remember it worked for me.
Look up the command and you should find examples.

TimMurphy,

Thanks again for looking into my post! I’ve found a way to attach the button to the AppleScript. And it works perfect for my computer…However, I am sharing this excel document with other users. The AppleScript references my hard drive name. Is there a way to change the name to whomever uses it? So that they can utilize the scripts on their Macs?? They share the Dropbox folder. Script is below.

Sub Button1_Click()
Dim scriptToRun As String

scriptToRun = “set myScript to ““Macintosh HD:Users:MyUserName:Dropbox:Mac Excel Scripts:NewWorksheet-2008.scpt”” as alias”
scriptToRun = scriptToRun & vbCr & “run script myScript”
MacScript (scriptToRun)

End Sub

I want to replace myUsername with theirUsername. If there’s a better way to do this, please advise. Thanks again for the help!! This forum has been awesome!

For windows this is accomplised by using the Environ:
"\Users" & Environ(“Username”) & "\Dropbox\Mac Excel Scripts\NewWorksheet-2008.scpt"

-E

I am sorry, but I can’t really help with your last question…