Does File Already Exist

[Joanne Buss]

Good Morning All
I am using WinXP Pro SP3 with MSOffice 2003 on Dell Optiplex GX270

I am using the following code to check and see if a file I am renaming
already exists in the folder. An MVP helped me with this code 3-4 years ago
and while it works just great there is one little thing I would like to
tweak, but I just don't know how to. I am hoping someone here will help me.

Example - when I rename a file jones-r.txt and the code looks in the
directory for it, if it finds, say, jones-re.txt, it thinks it is the same
file and will ask me if I want to overwrite it. For some reason, it seems to
look only at the first letter after the dash, never seeing the second
letter. I would like it to see the second letter and only ask if I want to
overwrite if I have really given the file the exact same name.

Here is the Code:
NameReplace = InputBox(Message, Title, Default, 5500, 5500) 'Sets msg box
and its placement on the screen
ChangeFileOpenDirectory "h:\done"

Dim strMsg As String ' Message to return if file exists
strMsg = "Do you want to replace the existing " & NameReplace & "?"

' Check if the file exists.
If Dir(NameReplace & "*") = "" Then

' If file does not exist, save without prompting.
ActiveDocument.SaveAs FileName:=NameReplace, FileFormat:=wdFormatText
ActiveDocument.Close
Else

'If file does exist, prompt with warning message.
' Check value of button clicked in message box.
Select Case MsgBox(strMsg, vbYesNoCancel)
Case vbYes
' If Yes was chosen, save and overwrite existing file.
ActiveDocument.SaveAs FileName:=NameReplace,
FileFormat:=wdFormatText
ActiveDocument.Close

Case vbNo
Dim strMsg2 As String ' Message to return if file exists
strMsg2 = "What name do you want to use?"
NameReplace = InputBox(Message, Title, Default, 5500, 5500) 'Sets
msg box and its placement on the screen
ActiveDocument.SaveAs FileName:=NameReplace,
FileFormat:=wdFormatText
ActiveDocument.Close

Case vbCancel
' If Cancel is chosen, close document
ActiveDocument.Close
End Select
End If

Any enlightenment would be greatly appreciated. I have gotten the question
of this lodged in my head and am really hungry for the answer, like working
a puzzle and looking for the very last answer I guess.

Thank you so much
Joanne

 

[Graham Mayor]

The following should work and includes some additional error handling: I
would also have thought that closing the document if the user selects cancel
is a bit final and could lead to data loss. I have therefore disabled that
line.

Sub SaveAsTXT()
Dim NameReplace As String
Dim strMsg As String
Dim strMsg2 As String
Dim Message As String
Dim Title As String
Dim oDoc As Document

Message = "Enter File Name"
Title = "Save as text"
strMsg2 = "What alternative name do you want to use?"

Set oDoc = ActiveDocument
With oDoc
NameReplace = InputBox(Message, Title)
'user cancelled
If NameReplace = "" Then
MsgBox "User Cancelled", vbInformation, Title
Exit Sub
End If
'Add txt extension
NameReplace = NameReplace & ".txt"
ChangeFileOpenDirectory "h:\done"
' Message to return if file exists
strMsg = "File Exists! Do you want to replace the existing " _
& Left(NameReplace, Len(NameReplace) - 4) & "?"
' Check if the file exists.
If Dir(NameReplace) = "" Then
' File does not exist, save without prompting.
.SaveAs FileName:=NameReplace, _
FileFormat:=wdFormatText
.Close
Else
'File does exist, prompt with warning message.
' Check value of button clicked in message box.
Select Case MsgBox(strMsg, vbYesNoCancel)
Case vbYes
' If Yes was chosen, save and overwrite existing file.
.SaveAs FileName:=NameReplace, _
FileFormat:=wdFormatText
.Close
Case vbNo
' Message to return if file exists
Do Until Dir(NameReplace) = ""
NameReplace = InputBox(strMsg2, _
Title) & ".txt"
Loop
.SaveAs FileName:=NameReplace, _
FileFormat:=wdFormatText
.Close
Case vbCancel
' If Cancel is chosen, close document
'.Close
End Select
End If
End With
End Sub

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Karl E. Peterson]

Joanne Buss presented the following explanation :

I am using the following code to check and see if a file I am renaming
already exists in the folder. An MVP helped me with this code 3-4 years ago
Argh!

' Check if the file exists.
If Dir(NameReplace & "*") = "" Then

NOOOO!!! Don't use Dir() for this task. Ever. Never, ever,
*ever*...! (Said, mostly, to all the MVPs listening! <g>)

These work, and don't mess up the find handle for pending Dir
operations:

Public Function FileExists(ByVal FileName As String) As Boolean
Dim nAttr As Long
' Grab this files attributes, and make sure it isn't a folder.
' This test includes cases where file doesn't exist at all.
On Error GoTo NoFile
nAttr = GetAttr(FileName)
If (nAttr And vbDirectory) <> vbDirectory Then
FileExists = True
End If
NoFile:
End Function

Public Function FolderExists(ByVal PathName As String) As Boolean
Dim nAttr As Long
' Grab the attributes, test valid values for folder bit.
On Error GoTo NoFolder
nAttr = GetAttr(PathName)
If (nAttr And vbDirectory) = vbDirectory Then
FolderExists = True
End If
NoFolder:
End Function

 

[Fumei2 via OfficeKB.com]

NOOOO!!! Don't use Dir() for this task. Ever. Never, ever,
*ever*...! (Said, mostly, to all the MVPs listening! <g>)


Indeed.

 

[Jeff]

Try using the Scripting.FileSystemObject shown it gives you a wide range of
methods otherwise not available in VBA. One important note, You'll need to
pass the entire file path to the function. such as "C:\WINDOWS\Zapotec.bmp"

Public Function IsFile(ByVal sFile As String) As Boolean
Dim oFSO As Object

Set oFSO = CreateObject("Scripting.FileSystemObject")

If oFSO.FileExists(sFile) Then
IsFile = True
Else
IsFile = False
End If
End Function

 

[Karl E. Peterson]

Jeff expressed precisely :

Try using the Scripting.FileSystemObject shown it gives you a wide range of
methods otherwise not available in VBA.

Bu11shit.