SaveAs dialog get answer

Scripting Forums AppleScript | Mac OS X
, ,
#1

Hi all

You can call the SaveAs dialog with

tell application “Microsoft Excel”
show (get dialog dialog save as)
end tell

What can I do to know if the user enter a path and press Save or press the cancel button.

Thanks for your help

#2

Hello!

Something like this, you’d have to log the “n” to see that the error code for cancel is indeed −128.

I recommend that you read/skim through the AppleScript Language Guide.

set userCancelled to false
tell application "Microsoft Excel"
	try
		show (get dialog dialog save as)
	on error e number n
		if n = -128 then set userCancelled to true
	end try
end tell

If memory serves me right, there should be a Pdf document, published by Microsoft concerning using AppleScript with MsExcel.

#3

Thanks

Try to explain more

I want it to use this applescript in VBA script because
Application.Dialogs(xlDialogSaveAs).Show

On a Mac not wait before it run the other code in the macro, in Windows it wil wait

So I want that the applescript return to me if the use cancel or Save so the VBA code
after I call this apple script can do what I want depending of what the user does

#4

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. :expressionless:

Using the try catch, will at least tell you what happened, userCancel, or Save, if it works as it should.

#5

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

#6

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

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

#8

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

#9

Actually without specifying any file format save workbook as takes the file format from the name extension

#10

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.

#11

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

#12

Yes the extension is OK

Have you try to open it ?

#13

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

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

#15

It will, but you have to add more else parts to consider all formats or use a list

#16

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

Thanks Stefan

Will check it out this weekend

Thanks for all your help

#18

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

#19

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

#20

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