*New to AppleScript* Converting Excel 2011 Vba to Excel 2008 AppleScript

esneaker

New Member
Joined
Feb 1, 2015
Messages
11
Greetings all!

I created an Excel program/workbook that uses vba macros throughout for others to benefit from. The only problem is, some people do not have Excel 2011, instead they only have 2008 which does not utilize macros.
1. I was told I can still automate Excel using applescript. If this is true, can this script be shared with other users?

2. How would I change this vba code to applescript? (it calls a UserInterface, copies a hidden worksheet, and changes its name). It seems simple in theory, I just don't know where to start.

Code:
Sub Button1_Click()
UserForm1.Show
End Sub

Private Sub ComboBox1_Change()
Dim index As Integer
index = ComboBox1.ListIndex
ComboBox2.Clear
Select Case index
Case Is = 0
With ComboBox2
.AddItem "1QTR"
.AddItem "2QTR"
.AddItem "3QTR"
.AddItem "4QTR"
End With
Case Is = 1
With ComboBox2
.AddItem "January"
.AddItem "Febuary"
.AddItem "March"
.AddItem "April"
.AddItem "May"
.AddItem "June"
.AddItem "July"
.AddItem "August"
.AddItem "September"
.AddItem "October"
.AddItem "Novenber"
.AddItem "December"
End With
End Select
End Sub

Private Sub CommandButton1_Click()
Dim myWorksheet As Worksheet
    Dim myWorksheetName As String
    myWorksheetName = Format(ComboBox2)
    For Each myWorksheet In Worksheets
        If myWorksheet.Name = myWorksheetName Then
            MsgBox "Sheet already exists...Make necessary " & _
                "corrections and try again."
            Exit Sub
        End If
Next myWorksheet
If ComboBox1 = "MON PLAN" Then
Sheets("Template1").Visible = True
Sheets("Template1").Copy After:=Worksheets(Worksheets.Count)
' RENAME
ActiveSheet.Name = myWorksheetName
Sheets("Sheet63").Visible = False
Else

        Sheets("Template2").Visible = True
Sheets("Template2").Copy After:=Worksheets(Worksheets.Count)
' RENAME
ActiveSheet.Name = myWorksheetName
Sheets("Template2").Visible = False
        
End If
End Sub
Private Sub UserForm_Initialize()
With ComboBox1
.AddItem "QTR PLAN"
.AddItem "MON PLAN"
End With
End Sub

Sub MONTH_PLANNER()


End Sub

Any help would be greatly appreciated!! Thank you for your time!!
 
Last edited by a moderator:

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
You can do the cells stuff with apple script, but you can't invoke a user form. Which is OK in your case, since Excel 2008 didn't support user forms.

But it looks like you are using the user form to just let the user choose from two combo boxes, which can be replaced with applescript's Choose From List.

http://macscripter.net is the applescript help forum that I use.
 
Last edited:
Upvote 0
I think that this will do what you want.
You can set default value for the style and name selection dialogs.

Code:
-- AppleScript

tell application "Microsoft Excel"
	set myWorkbook to active workbook

	set SheetStyle to button returned of (display dialog "Pick style of new sheet" buttons {"Month style", "Quarter style", "Cancel"})
	
	if SheetStyle = "Month Style" then
		set NewSheetNameList to {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November"}
		set templateSheet to worksheet "Template1" of myWorkbook
	else
		set NewSheetNameList to {"1QTR", "2QTR", "3QTR", "4QTR"}
		set templateSheet to worksheet "Template2" of myWorkbook
	end if
	
	set NewSheetName to choose from list NewSheetNameList
	
	if NewSheetName ≠ false then
		set NewSheetName to item 1 of NewSheetName
		if my SheetNameExists(NewSheetName, myWorkbook) then
			display dialog NewSheetName & " exists. Correct and try again."
		else
			
			set lastSheet to worksheet (count of worksheets of active workbook) of active workbook
			copy worksheet (worksheet "Template1" of active workbook) after lastSheet
			set name of active sheet to NewSheetName
		end if
	end if
end tell


on SheetNameExists(testName, inWorkbook)
	tell application "Microsoft Excel"
		try
			return (name of (worksheet testName of inWorkbook) = testName)
		on error
			return false
		end try
	end tell
end SheetNameExists
 
Upvote 0
mikerickson,

1. Thanks for the other forum!! It provides great insight and direction with AppleScripting.
2. Your code does bring up what I want! However, I get the following error when trying to copy the sheet and rename:
"Can’t make «class 1172» of application "Microsoft Excel" into the expected type."
The ScripEditor highlights: "SheetNameExists(NewSheetName, myWorkbook)" as it's checking if the worksheet already exists.

Any thoughts? Your help is greatly appreciated!
 
Upvote 0
It worked for me, but I'm never comfortable passing objects to Apple Script handlers (sub-routines). Try this. If the problem persists, the experts at the other site would have some insight.
Code:
-- Apple Script

tell application "Microsoft Excel"
	set myWorkbook to active workbook
	set SheetStyle to button returned of (display dialog "Pick style of new sheet" buttons {"Month style", "Quarter style", "Cancel"})
	
	if SheetStyle = "Month Style" then
		set NewSheetNameList to {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November"}
		set templateSheet to worksheet "Template1" of myWorkbook
	else
		set NewSheetNameList to {"1QTR", "2QTR", "3QTR", "4QTR"}
		set templateSheet to worksheet "Template2" of myWorkbook
	end if
	
	set NewSheetName to choose from list NewSheetNameList
	
	if NewSheetName ≠ false then
		set NewSheetName to item 1 of NewSheetName
		try
			if (name of (worksheet NewSheetName of myWorkbook) = NewSheetName) then
				display dialog NewSheetName & " exists. Correct and try again."
				return
			end if
			
		on error
			set lastSheet to worksheet (count of worksheets of myWorkbook) of myWorkbook
			copy worksheet templateSheet after lastSheet
			set name of active sheet to NewSheetName
			
		end try
	end if
end tell
 
Last edited:
Upvote 0
mikerickson,

You're awesome! Thanks for the help!! It works like a champ! Exactly what I wanted it to do. One last questions:
Does AppleScript allow you to hide/unhide sheets for editing?
I'm able to do this in VBA (see my original code). I've tried my hand at it...and I'm not winning. If the template is unhidden, it copies the template fine and renames it like I want it to. However, if the sheet is hidden,it will only rename the last sheet in the workbook, and will create an additional hidden copy of the template(shows template1(1), template1(2), etc.) My goal is for the user to not see the hidden file at all.

I really appreciate your assistance! You've been most helpful! Thank you!!



set visible of worksheet "Sheet63" to true

set templateSheet to worksheet "Sheet63" of myWorkbook

set visible of worksheet "Sheet63" to false
 
Upvote 0
I sold my own problem! Had to invoke the visibility code after the renaming:

Thanks again for the Awesome guidance!!
...
on error

if SheetStyle = "Month Style" then
set visible of worksheet "Template1" to true
set lastSheet to worksheet (count of worksheets of myWorkbook) of myWorkbook
copy worksheet templateSheet after lastSheet
set name of active sheet to NewSheetName
set visible of worksheet "Template1" to false
else
set visible of worksheet "Template2" to true
set lastSheet to worksheet (count of worksheets of myWorkbook) of myWorkbook
copy worksheet templateSheet after lastSheet
set name of active sheet to NewSheetName
set visible of worksheet "Template2" to false
end if


 
Upvote 0
Why are you making the template sheet visible, just to make it invisible later? The copy works just fine (for me) if Template is not visible.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top