Hello!
Hopefully the macro will throw an error when the script runs. If not, and if this isn’t described in the MS Excel document, then I’d recommond you to use the Applescript SaveAs dialog for MsExcel, however inconvenient that may be. 
Using the try catch, will at least tell you what happened, userCancel, or Save, if it works as it should.
I havent tested with AppleScript, but with Excel VBA
Application.Dialogs(xlDialogSaveAs).Show
Will return True if the file is saved, and False if it is canceled.
It will not return the file name/path chosen.
To get the name/path, one would use Application.GetSaveAsFileName, and use the returned string with Workbooks.SaveAs
Right, the AppleScript equivalent is
tell application "Microsoft Excel"
activate
set theLocation to get save as filename
save workbook as active workbook filename theLocation
end tell
Hi Mike
For example in Windows I hide a userform and call the dialog.
After the user save or cancel the code do some stuff and unhide the userform
On A Mac it not stop so it show the saveas dialog and unhide userform because it finish the macro
I know I can use getsaveasfilename (btw also bad on a Mac) but maybe applescript is a better option
Hi Stefan
When I have unsaved workbook open in Excel with some code in it and run your script from the script editor
It default to xlsx.
So I change it to xlsm and enter a file name
When i click on Save it say that the macros will be removed (not correct because I change it to xlsm)
When I click on Save it is saved as a xlsx with a xlsm extension
Actually without specifying any file format save workbook as takes the file format from the name extension
Hi Stephan
Not useful then this dialog
No problem that it default to another format but when You change it in the dialog
it must follow that format when it save.
On my machine (Lion, Office 2011) it does change the type depending on the settings in the dialog.
I tried it with an existing and a blank document.
Open the Finder info window of the Excel file. It should display the proper file type in the Kind line
Yes the extension is OK
Have you try to open it ?
You’re right, it doesn’t open
Then you have to specify the file type explicitly, for example
tell application "Microsoft Excel"
activate
set theLocation to get save as filename
if theLocation ends with "xlsm" then
save workbook as active workbook filename theLocation file format macro enabled XML file format
else
save workbook as active workbook filename theLocation
end if
end tell
I don’t like that
If the user can change he format it should work.
I test more this weekend
Thanks for all your help
It will, but you have to add more else parts to consider all formats or use a list
PS:
this is an example with a list and 5 given file formats. It can be extended.
If the format is not found, the document will be saved as default file format.
As the variable formatList contains enumerated constants, it must be inside the application tell block
There is no error handling in case no name extension is specified.
property extensionList : {"xls", "xlsm", "xml", "xlsb", "xltm"}
tell application "Microsoft Excel"
set formatList to {Excel98to2004 file format, macro enabled XML file format, ¬
Excel XML file format, Excel binary file format, macro enabled template file format}
activate
set theLocation to get save as filename
end tell
set {TID, text item delimiters} to {text item delimiters, "."}
set nameExtension to last text item of theLocation
set text item delimiters to TID
set theIndex to indexOfItemInList(nameExtension, extensionList)
tell application "Microsoft Excel"
if theIndex > 0 then
set fileFormat to item theIndex of formatList
save workbook as active workbook filename theLocation file format fileFormat
else
save workbook as active workbook filename theLocation file format workbook normal file format
end if
end tell
on indexOfItemInList(theItem, theList)
repeat with i from 1 to count theList
if item i of theList is theItem then return i
end repeat
return 0
end indexOfItemInList
Thanks Stefan
Will check it out this weekend
Thanks for all your help
I use the old getsaveasfilename in VBA and add some checking so the correct fileformt will be used when you save
I add it to my page
http://www.rondebruin.nl/mac.htm#GetSaveAsFileName
Thanks all for your reply’s
This is entirely doable in Excel VBA (Excel 2011)
This VBA code (put in the userform’s code module) worked for me.
(Although I’d be more comfortable with using .SaveCopyAs, since I don’t want the project that contains the userform to go away)
You might have some problems if you are passing arguments to the GetSaveAsFile name, since the filtering structure is different for Windows vs. Mac
[code]’ Excel VBA
Private Sub butSave_Click()
Dim uiFilePath As String
Me.Hide
uiFilePath = Application.GetSaveAsFilename
If uiFilePath = "False" Then
Me.Caption = "canceled"
Else
ThisWorkbook.SaveAs uiFilePath
End If
Me.Show
End Sub[/code]
P.S. http://www.mrexcel.com/forum/excel-questions/ is a good free forum for Excel VBA questions
SaveAs without the fileformat number wil not work correct Mike.
You get files that you can’t open, extension and fileformat number must match.
by default it use the number of the default saveformat setting in Excel.
SaveCopAs can only save in the same format as the workbook
The VBA code that I posted was tested and it worked for me.
(I may have an “add appropriate extension” option selected somewhere)
Also, with Mac’s, the file extension is just a part of the name, files can be opened and worked with or without extensions.
If you look in the VB Editor’s Object Browser, keyword xlFileFormat will show a list of file format constants, like xlCSV and xlWorkbookNormal
see 13 in this thread Mike
With script you can have the same problem as in your VBA
If the format and the extension not match you can’t open the file that you save
My VBA code avoid it no, like you see that stephan also do in his script
See my function here
http://www.rondebruin.nl/mac.htm#GetSaveAsFileName
I think it is a good thing to consider the options for export in this matter -cross plattform compatibility, MsWindows relies on the file extension.
I am not saying this to you per se, mike. 