searching folder for existing file

**TedH** · 01-20-2009, 12:51 AM

Here's another one:
I need to check if a file already exists in a folder, and prompt the user to choose if they want to overwrite it.

I've found some posts that dal with getting filenames, but haven't found one that addresses this.

Thanks again!
Ted

If <<<folder contains file>>> Then
    Dim DuplicateMsg, ResponseDuplicate
    DuplicateMsg = "This file already exists." & Chr(13) & Chr(13) _
        & "Do you want to overwrite it?"
    ResponseDuplicate = MsgBox(DuplicateMsg, vbYesNo + vbInformation)
If ResponseDuplicate = vbYes Then
    ActiveWorkbook.Save
        
End If
Else
ActiveWorkbook.SaveAs filePath & Application.PathSeparator & namePN & " PrtRev_ " _
                         & nameRev & " SO_" & nameSO & " ILRev2.xls"
End If

**shg** · 01-20-2009, 01:47 AM

If Len(Dir(somePathAndFile)) > 0 Then
    ' do this

**mudraker** · 01-20-2009, 02:04 AM

try this code
The ActiveWorkbook.SaveAs command id from your code the rest is from me so you will probally need to tinker with the variable names, file paths

The msgbox is your wording with buttons set by me - you may want to change it to suit

Dim sPath As String
Dim sFName As String
Dim sResp As String

sPath = "c:\temp\"
sFName = "Meter Sheet - Email.xls"

If Dir(sPath & sFName, vbNormal) = "" Then
  ' File does not exist - add normal save commands here
Else
   If MsgBox("This file already exists." & Chr(13) & Chr(13) _
      & "Do you want to overwrite it?", _
      vbYesNo + vbDefaultButton2 + vbQuestion) = vbYes Then
         ActiveWorkbook.Save
   Else
      ActiveWorkbook.SaveAs filePath & Application.PathSeparator & namePN & " PrtRev_ " _
         & nameRev & " SO_" & nameSO & " ILRev2.xls"
   End If
End If

**TedH** · 01-26-2009, 05:23 PM

Mudraker:
(just getting back to this....)

This code works EXCEPT when the file already exists in the folder. I'd like be able to choose not to overwrite the file without it kicking me out of the macro.

This was my attempted solution, but it didn't work. I'm still getting the "regular" saveas dialog prompt. Is there anyway to skip this? I didn't see anything in the help file about using true/false....

Ted

    Dim sPath As String
    Dim sFName As String
    Dim sResp As String

    sPath = "F:\QC RECORDS\411 INSPECTION LAYOUT\" & nameCust & Application.PathSeparator
    sFName = namePN & " PrtRev_ " & nameRev & " SO_" & nameSO & " ILRev2.xls"
    Range("p2") = sPath
If Dir(sPath & sFName, vbNormal) = "" Then
    Range("p3") = "Dir(sPath & sFName, vbNormal) is empty"
    ' File does not exist - add normal save commands here
    BookRev2.SaveAs filePath & Application.PathSeparator & namePN & " PrtRev_ " _
         & nameRev & " SO_" & nameSO & " ILRev2.xls"
Else
   If MsgBox("This file already exists. REV1" & Chr(13) & Chr(13) _
      & "Do you want to overwrite it?" & Chr(13) & Chr(13) & "No will put it in the review folder", _
        vbYesNo + vbDefaultButton2 + vbQuestion) = vbYes Then
        Range("p3") = "Dir(sPath & sFName, vbNormal) is full"
        BookRev2.SaveAs filePath & Application.PathSeparator & namePN & " PrtRev_ " _
                         & nameRev & " SO_" & nameSO & " ILRev2.xls"
         
    Else
    BookRev2.SaveAs "F:\QC RECORDS\411 INSPECTION LAYOUT\review files\" & namePN & " PrtRev_ " _
                         & nameRev & " SO_" & nameSO & " REVIEW DUPLICATE ILRev2.xls"
    End If

searching folder for existing file

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

searching folder for existing file

Re: searching folder for existing file

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions