Hello. I have an excel macro I've written, and now I'm just trying to clean it up a little. The first thing the macro does is ask the user to save the file with:
If cancel is hit from the dialog box the office assistant pops up and asks if the user wants to overwrite the existing file. If yes is hit everything continues with no problem, but if no or cancel is hit, the macro goes into the debug phase and stops. What code do I need to handle this?
Please Login or Register to view this content.
Last edited by Leith Ross; 09-15-2012 at 04:32 PM.
Hello tomham,
Welcome to the Forum!
The GetSaveAsFilename method will return "False" as the file name if Cancel if pressed or the dialog is closed.
Please Login or Register to view this content.
Last edited by Leith Ross; 09-15-2012 at 04:40 PM.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
I appreciate the assistance.
When false, I need the workbook to close without saving changes and without a prompt.
I tried using the following, which worked ok, except on cancel, it saves a filed called false.csv.
Can you assist?Please Login or Register to view this content.
Last edited by Leith Ross; 09-15-2012 at 06:20 PM.
Hello tomham,
No problem. Here is the change...
Please Login or Register to view this content.
That didn't work.
When cancel is hit the worksheet tab name changes to "false" and the workbook is saved, then the macro displays the MsgBox "you pressed cancel"
. I think the false statement has to be combined FileSaveName, but I'm not sure of the syntax.
Move the 'save as' to the bottom?
BoonPlease Login or Register to view this content.
Hello tomham,
The problem is I forgot you changed the variable type for "FileSaveName". You originally cast it as String and then changed it to Variant. The call will return a boolean False if the variable is a Variant or "False" if is a String. Please be consistent with your Types, it will save you a lot of trouble in troubleshooting your code.
Please Login or Register to view this content.
Thanks Boon, that worked great. The only thing I added was "Application.DisplayAlerts = False" to force an overwrite in case the file alreay exists.
[SOLVED]Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks