Loop back to dialog box

[rmwilson]

Is there a way to loop back for another filename on vbNo using this.

If Dir(SaveFileName) = "" Then 'do nothing
Else
resp = MsgBox(prompt:="This file already exists -- Overwrite Existing File?", Buttons:=vbYesNo)
If resp = vbNo Then
'Loop ?
exit function
end if
end if

 

[Andrew Poulsom]

Try something like this:

Dim MyFile As String
MyFile = Dir(SaveFileName) 
Do While MyFile <> ""
    resp = MsgBox(prompt:="This file already exists -- Overwrite Existing File?", Buttons:=vbYesNo)
    If resp = vbNo Then
        MyFile = Dir()
    Else
        Exit Do
    End If
Loop

 

[rmwilson]

Andrew,

Thanks for your reply. Doesn't seem to loop under these conditions

If Left(Application.OperatingSystem, 3) = "Win" Then
SaveFileName = Application.GetSaveAsFilename(CurFile, _
"Formatted text (Space Delimited) (*.SCT), *.SCT", , "Slicer 3")
Else
SaveFileName = Application.GetSaveAsFilename(CurFile, _
"TEXT", , "Slicer 3")
End If

' Check to see if Cancel was clicked.
Dim MyFile As String
MyFile = Dir(SaveFileName)
Do While MyFile <> ""
resp = MsgBox(prompt:="This file already exists -- Overwrite Existing File?", Buttons:=vbYesNo)
If resp = vbNo Then
MyFile = Dir()
Else
Exit Do
End If
Loop

 

[Andrew Poulsom]

Is this what you want?

    Dim MyFile As String
    Do
        If Left(Application.OperatingSystem, 3) = "Win" Then
            SaveFileName = Application.GetSaveAsFilename(CurFile, _
                "Formatted text (Space Delimited) (*.SCT), *.SCT", , "Slicer 3")
        Else
            SaveFileName = Application.GetSaveAsFilename(CurFile, _
                "TEXT", , "Slicer 3")
        End If
'       Check to see if Cancel was clicked.
        If SaveFileName = False Then Exit Sub
        MyFile = Dir(SaveFileName)
        If MyFile <> "" Then
            resp = MsgBox(prompt:="This file already exists -- Overwrite Existing File?", Buttons:=vbYesNo)
            If resp = vbYes Then
                Exit Do
            End If
        End If
    Loop

 

[rmwilson]

ADVERTISEMENT

Thanks Andrew, but it seems to loop even with a new filename entered. Can this be resolved?

Thanks!

rmw

 

[Andrew Poulsom]

Maybe:

    Dim MyFile As String
    Do
        If Left(Application.OperatingSystem, 3) = "Win" Then
            SaveFileName = Application.GetSaveAsFilename(CurFile, _
                "Formatted text (Space Delimited) (*.SCT), *.SCT", , "Slicer 3")
        Else
            SaveFileName = Application.GetSaveAsFilename(CurFile, _
                "TEXT", , "Slicer 3")
        End If
'       Check to see if Cancel was clicked.
        If SaveFileName = False Then Exit Sub
        MyFile = Dir(SaveFileName)
        If MyFile <> "" Then
            resp = MsgBox(prompt:="This file already exists -- Overwrite Existing File?", Buttons:=vbYesNo)
            If resp = vbYes Then
                Exit Do
            End If
        Else
            Exit Do
        End If
    Loop

 

[rmwilson]

That's it! thanks a bunch Andrew.

rmw