How to copy Word documents to a location without overwriting existing file?

[sueb]

In my Access database, I have a button on a form that copies a set of existing Word documents to a location (associated with the record on that form).

However, I don't want an existing file to be overwritten (as it currently happens), but rather I want to make a copy (filename + "(2)" or "(3)", etc., as appropriate).

Here's the code as it stands:

Function Public_Copy_Files(strChartNum As String, strAccount As String)
On Error GoTo Err_Public_Copy_Files
 
    Dim strFileName As String
 
    ' Make the directories, if necessary
    Public_Make_Directories strChartNum, strAccount
 
    ' Copy forms into Account folder
    strFileName = "18-1.DOC"
    FileCopy conFormSource & strFileName, _
             conPath & strChartNum & "\" & strAccount & "\" & strFileName
    strFileName = "FAX COVER.DOT"
    FileCopy conFormSource & strFileName, _
             conPath & strChartNum & "\" & strAccount & "\" & strFileName
    strFileName = "Medi-Cal UR Review.doc"
    FileCopy conFormSource & strFileName, _
             conPath & strChartNum & "\" & strAccount & "\" & strFileName
    strFileName = "UR FORM.DOC"
    FileCopy conFormSource & strFileName, _
             conPath & strChartNum & "\" & strAccount & "\" & strFileName
 
Exit_Public_Copy_Files:
   Exit Function
 
Err_Public_Copy_Files:
    MsgBox Err.Description
    Resume Exit_Public_Copy_Files
 
End Function
 

[sueb]

(in addition, i'd love it if i could avoid hard-coding the filenames, but make this code smart enough to simply find each each document it in the "source location", and make a copy in the new location!)

[Rabbit]

You'll want to look into the Scripting.FileSystemObject. It has functions and attributes for .FileExists(), .FolderExists(), .Subfolders, and .Files.

[NeoPa]

In the following illustrative procedure I've assumed that conFormSource and conPath are set up to terminate with a backslash (\) char.

Public Function Public_Copy_Files(strChartNum As String, strAccount As String)
On Error GoTo Err_Public_Copy_Files
    Dim strFromName As String, strToName As String, strFileList As String
    Dim intX As Integer
 
    ' Make the directories, if necessary
    Call Public_Make_Directories(strChartNum, strAccount)
 
    ' Loop through and note all .DOC files found in conFormSource
    strFromName = Dir(conFormSource & "*.DOC")
    Do While strFromName > ""
        strFileList = strFileList & "," & strFromName
        strFromName = Dir
    Loop
 
    ' For each .DOC file found copy to destination
    Do While strFileList > ""
        intX = InStrRev(strFileList, ",")
        strFromName = Mid(strFileList, intX + 1)
        strFileList = Left(strFileList, intX - 1)
        ' If file already exists then add "(2)" to the end
        strToName = conPath & strChartNum & "\" & strAccount & "\" & strFromName
        If Dir(strToName) > "" Then
            intX = InStrRev(strToName, ".")
            strToName = Left(strToName, intX - 1) & "(2)" & _
                        Mid(strToName, intX)
        End If
        Call FileCopy(conFormSource & strFromName, strToName)
    Loop
 
Exit_Public_Copy_Files:
   Exit Function
 
Err_Public_Copy_Files:
    MsgBox Err.Description
    Resume Exit_Public_Copy_Files
 
End Function

Basically, it first lists the matching files found in conFormSource (into strFileList). Next it loops through the list and checks, for each item, whether the same-named file exists in the destination folder. If it does, it simply adds the text "(2)" just before the ".DOC" bit at the end. It then takes the resultant filename and copies the original file to that. Each iteration of the loop processes a different file until there is none left.

[sueb]

Oh, this is beautiful and (naturally) works exactly right!

However, now, of course, I want to go a little further. Thinking about some of my more unsophisticated users, I'd like to modify this so that, for every click of the "copy files" button, a separate set of files are created; i.e., the third time they click that button without checking to see what happened in the Explorer window, they get "filename (3).doc".

I'd like to change lines 23 through 27 to something like:

        Do While Dir(strToName) > ""
            intX = InStrRev(strToName, ".")
            strToName = Left(strToName, intX - 1) _
                        & "(" & #LOOPINDEX# & ")" _
                        & Mid(strToName, intX)
        Loop
 

Of course, I can manually manage a loop index, but isn't there one already available? (you see how lazy i am...)

[NeoPa]

There is a For N = X To Y Step Z ... Next N construct, but I don't really see that fitting what you describe. Does it even make sense to choose a different number depending on how many times the request has been made? Should the count be session related, or should it continue one day where it left off the previous one?

Frankly, I'm not sure exactly what you want. The solution must match the requirement, and I don't understand what that is.