exit save as macro if filename already exists

[SCOTTWHITTAKER2333]

Ok I have a Template workbook that has an auto save as macro that triggers on open. It creates the filename based on criteria entered into a few input boxes buy the user. The problem I am having is that there are many different templates that I have with a simmaler macro, and sometimes the "excel challanged" users are opening the same template by mistake and creating the file more than one time. What I want to do is have the macro check to see if the file name exists in the directory before it completes the save as and if it does then display a message that the file has already been created. If possible I would also like it to close the template and open the file that was already created.
This is what my code looks like right now:

Private Sub Workbook_Open()
'
' saveasauto Macro
' Macro recorded 5/28/2010 by SCOTT.WHITTAKER '
'
If (Worksheets("Bowls").Range("R14") = "") Then
SKUa = InputBox("Enter the product number")
Shift = InputBox("Enter your shift:")
MeaName = Shift & "-" & SKUa & "-" & "-" & "Net wts" & Format(Now(), "mm-dd-yy") & ".xls"
ActiveWorkbook.SaveAs Filename:=Path & "\" & MeaName, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False
 Worksheets("Bowls").Range("R14") = SKUa
 Worksheets("Bowls").Range("F1") = Shift
 End If
End Sub

 

[HalfAce]

Does something like this get you closer?
Note, I don't see where you have 'Path' defined as anything so I assumed you want these workbooks saved to (and therefore opened from) the same directory the template is in. (We can change that if it ain't right.)

Private Sub Workbook_Open()

' saveasauto Macro
' Macro recorded 5/28/2010 by SCOTT.WHITTAKER _
      (tweaked by HalfAce sometime later...)
      
Dim SKUa$, Shift$, MeaName$, fName$
GiveMeAName:
If Worksheets("Bowls").Range("R14") = "" Then
  SKUa = InputBox("Enter the product number")
  Shift = InputBox("Enter your shift:")
  If Len(SKUa) = 0 Or Len(Shift) = 0 Then
    If MsgBox("Can't save this file without an SKU and a Shift entered." & vbCr & vbCr & _
      "Want to try again?", vbYesNo) = vbYes Then
      GoTo GiveMeAName
    Else
       ThisWorkbook.Close False
    End If
  End If
  MeaName = Shift & "-" & SKUa & "-" & "-" & "Net wts" & Format(Now(), "mm-dd-yy") & ".xls"
  fName = ThisWorkbook.Path & "\" & MeaName
  If Dir(fName, vbDirectory) <> "" Then
    MsgBox "A file named '" & MeaName & " already exists." & vbCr & vbCr & _
    MeaName & " will now open."
    Workbooks.Open fName
    ThisWorkbook.Close False
    Exit Sub
  End If
  ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & MeaName, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False
  With Worksheets("Bowls")
    .Range("R14") = SKUa
    .Range("F1") = Shift
  End With
 End If
End Sub

Hope it helps.

 

[SCOTTWHITTAKER2333]

Absolutly!!! That is perfect!!!
Wow thanks I am very impressed that you gave me exactly what I was looking for on the first try. Usually when I come to these types of forums it takes 5 or 6 posts before its right.
THANK YOU VERY MUCH!!!

 

[HalfAce]

You're very welcome. (We strive for only perfection on this forum.)