CODE QUESTION

[d0wnt0wn]

Hi can someone please tell me if i can use a statement like "if file exists go to userC" instead of the on error statement as shown here?

Sub SaveActiveSheet()
On Error GoTo userC
ActiveSheet.Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\kenny\Desktop\estimates 05\" & [c5].Value & ".xls"
MsgBox "your worksheet has just been saved!"
ActiveWorkbook.Close False
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


also I have tried usig this piece here to name a file with limited success....

[c5].Value + "#2" & ".xls" so if c5 had "cat" in the cell it would read cat #2 but i am having problems with it also.... any better way to do this???

 

[Tazguy37]

Why not use the FileExists method?

 

[d0wnt0wn]

hehe excuse my ignorance taz.....I have only been trying to use VBA for about 2 weeks and although I am becomming familiar with recognizing some of the snipits of code I am still at a loss on how to impliment it.... I have been trying to get some help on this for some time now but noone seems to know or the answer is too complicated for any one to help me with it..... I just cant figure out a way to get what i want to happen so I am kind of settling for this for now untill i can figure the other out.... what I really want to happen....

user clicks on button to save a worksheet from a workbook based on an address in a cell.
if file does not exist in location... save file as cell value.
If there is a file with that name already.... ask user to overwrite or save file with a suggested name of cell value plus a (#2) desiganation or something similar.
code saves file as cell value or cell value + (#2) and closes. MSG box pops up and says file was saved.

thats what I am ultimately trying to acheive



as far as the code goes i know that my code does not do that but I am trying to fix one thing at a time since i have not been able to get help with this

 

[Tazguy37]

Or instead of .SaveAs, use .SaveCopyAs: Saves a copy of the workbook to a file but doesn't modify the open workbook in memory.

 

[d0wnt0wn]

I dunno.... is what I am asking not possible to do??