edit this code please???

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
i have a little snippet of code here that will save a file based on an address i type in c5 ..... is it possible to set it up so that if there is already an estimate of that address in there.... that an option pops up that will alow me to over write or save as address #2

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!"
Exit Sub

End Sub
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

chiello

Well-known Member
Joined
Jan 18, 2005
Messages
848
d0wnt0wn said:
.... that an option pops up that will alow me to over write or save as address #2

ActiveWorkbook.SaveAs Filename:="C:\Documents and

The SaveAs Method asks itself to overwrite if a file already exists. If you type No (to overwrite) the code generates an error, else the file will be overwritten and the code will flow to the end.

I suggest the following solution

Sub SaveActiveSheet()
Dim MyPrompt As String, MyTitle As String
Dim NewName As Variant

MyPrompt = "There is already a file with the name " & Range("C5") & ", File Not Saved!" _
& Chr(10) & Chr(10) & "Enter new Name"
MyTitle = "New File Name"
NewName = False
ActiveSheet.Copy
Label_SaveFile:
On Error GoTo userC
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:
While (NewName = False) Or (NewName = "")
NewName = Application.InputBox(prompt:=MyPrompt, Title:=MyTitle, Type:=2)
Wend
Range("C5") = NewName
GoTo Label_SaveFile
End Sub


Ciao :p :p
 

chiello

Well-known Member
Joined
Jan 18, 2005
Messages
848
chiello said:
Sub SaveActiveSheet()
...
MyTitle = "New File Name"
NewName = False
ActiveSheet.Copy
...
userC:
While (NewName = False) Or (NewName = "")
...
End Sub

:) :rolleyes:

I think it is better if you place the statement NewName = False soon after userC Label, as follows:

Sub SaveActiveSheet()
...
MyTitle = "New File Name"
'Not Here
ActiveSheet.Copy
...
userC:
NewName = False
While (NewName = False) Or (NewName = "")
...
End Sub
 

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
hi... I have all inds of erros when trying to use that code...... when i click on my button that i have made for the macro a box pops up and says there is already a file with that name do i want to replace..... no matter what i click the box will not go away...... and no file gets saved to my folder....also i would like the user to be given the choice wether or not to over write the original or replace it..... now if i click on the button and there is a file with that name in the folder... the box pops up and i enter a new name and i get a run time error that points here.

ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\Administrator\Desktop\estimates 05\" & [c5].Value & ".xls"
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029

ADVERTISEMENT

Code:
Sub testIt()
    Dim FileName As Variant
    FileName = "g:\temp\" & Range("c5").Value & ".xls"
    FileName = Application.GetSaveAsFilename(FileName)
    If TypeName(FileName) = "String" Then
        ActiveWorkbook.SaveAs FileName
        End If
    End Sub
d0wnt0wn said:
i have a little snippet of code here that will save a file based on an address i type in c5 ..... is it possible to set it up so that if there is already an estimate of that address in there.... that an option pops up that will alow me to over write or save as address #2
{snip}
 

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
not quite what i am looking for tush but thanks..... I want to save the active sheet only not the workbook and although the name in c5 did appear in the save as box the save as box's default save location was not right
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029

ADVERTISEMENT

Get the name right. Unless you want me to change the first four letters of your nick to d-u-m-b.;)

Posting edited.

d0wnt0wn said:
not quite what i am looking for tush but thanks..... I want to save the active sheet only not the workbook and although the name in c5 did appear in the save as box the save as box's default save location was not right
 

Forum statistics

Threads
1,148,269
Messages
5,745,782
Members
423,972
Latest member
franklins

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