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

[esneaker]

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.

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!!

 

[mikerickson]

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.

 

[mikerickson]

I think that this will do what you want.
You can set default value for the style and name selection dialogs.

-- 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

 

[esneaker]

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!

 

[mikerickson]

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.

-- 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

 

[esneaker]

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

 

[esneaker]

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

 

[mikerickson]

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.