code is missing something

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
I have been tinkering around with this save as code and I have it almost the way i want it.....

it saves where and what i want...
it gives me a message when there is a duplicate file in the folder.....
but one thing it does that I do not like is when it tries to close after a duplicate file is found it brings up the do you want to save workbook2 dialog..... I would like to do one of two things with it.... a) can i stop that dialog box from appearing and the workbook just closes without saving... or can i have the option to save it as something else

Sub SaveActiveSheet()
On Error GoTo userC
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\Administrator\Desktop\estimates 05\" & [c5].Value & ".xls"
MsgBox "your worksheet has just been saved!"
ActiveWorkbook.Close
Exit Sub

userC:
MsgBox "There is a file with that name already, File Not Saved!"
ActiveWorkbook.Close
Exit Sub

End Sub
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

tmischler

Well-known Member
Joined
Jun 17, 2004
Messages
669
application.displayalerts = false will stop the dialogue coming up. Don't forget to turn them back on afterwards
 

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
i played with that a bit but what happened was the duplicate file got overwritten without any kind of notice... this is not what i want to happen.... i want either the option for a new save as something different or for the workbook to just close without saving and have a little popup saying that it couldnt be saved because of duplicate.. this will make the user change the name of the target cell slightly
 

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
still looking for some help with this if anyone cares to give me some input id appreciate it
 

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209

ADVERTISEMENT

Code:
Sub SaveActiveSheet()
On Error GoTo userC
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\Administrator\Desktop\estimates 05\" & [c5].Value & ".xls"
MsgBox "your worksheet has just been saved!"
ActiveWorkbook.Close
Exit Sub

userC:
MsgBox "There is a file with that name already, File Not Saved!"
ActiveWorkbook.Close False

End Sub
 

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
so all that was added was the false at the end of the close statement?... what does that do???
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237

ADVERTISEMENT

Code:
ActiveWorkbook.Close False
will just close the active workbook without prompting you to save changes.
 

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
so if i want to rename a file based one cell i can do something like this??


Sub SaveActiveSheet()
On Error GoTo userC
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\kenny\Desktop\estimates 05\" & [c5].Value & ".xls"
MsgBox "your worksheet has just been saved!"
ActiveWorkbook.Close
Exit Sub

userC:
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\kenny\Desktop\estimates 05\" & [c5].Value + " #2" & ".xls"
MsgBox "your worksheet has just been saved!"
ActiveWorkbook.Close False
Exit Sub

End Sub



I found that this seems to work ok.... but what if i make another estimate can i do this????


Sub SaveActiveSheet()
On Error GoTo userC
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\kenny\Desktop\estimates 05\" & [c5].Value & ".xls"
MsgBox "your worksheet has just been saved!"
ActiveWorkbook.Close
Exit Sub

userC:
On Error GoTo userD
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\kenny\Desktop\estimates 05\" & [c5].Value + " #2" & ".xls"
MsgBox "your worksheet has just been saved!"
ActiveWorkbook.Close False
Exit Sub

userD:
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\kenny\Desktop\estimates 05\" & [c5].Value + " #3" & ".xls"
MsgBox "your worksheet has just been saved!"
ActiveWorkbook.Close False
Exit Sub

End Sub


is that proper use of VBA??? or is there a much tidier way to go about this?
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
What I would do is keep a count of how many times the workbook has been saved (probably in an out of the way place in the workbook, hidden if necessary). Then, in a BeforeSave() event, strip the number off the current filename (might want to keep it the same number of digits all the time, 2 or 3), add one and resave it.

I'm sure there's more ways to go about this than you or i can think of. :eek:
 

Forum statistics

Threads
1,147,690
Messages
5,742,647
Members
423,746
Latest member
Joaogomes

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
Top