Greetings all,

I am very close to finishing my project, but I need to address an issue with my macros for when it has previously been run and the resulting files exist.

I have a excel sheet which is populated by the user, then the command button linked to the macros is pressed to process. The macros run one after the other to: Create and name a file folder, save the worksheet in the new folder with the same name as the newly created folder; creates a MS Word merge document and names and saves to the afore mentioned folder.

My issue is that the users want to be able to overwrite the newly created files within the folder if they make a correction and resubmit the data via the control button. In essence, they want the whole process to run again and overwrite anything that is of the same file name. How can this be done? If so can it be done without getting error messages?

Here are the 2 macros I am working with:

Sub filesave()
     ' By Norie modified - This one Worked
    Dim MyFile As String
    Dim sDir As String
     
    MyFile = Sheets("Form").Range("C52").Text
     
    sDir = _
    "\\vumcnk-IOM1\D\Reports\CURRENT MONTH\" & MyFile
     
    MkDir sDir
     
          
    ChDir sDir
    ActiveWorkbook.SaveAs FileName:=sDir & "\" & MyFile
    
    End Sub
Sub Merge()
 
    Dim wd As Object
    Dim wdocSource As Object
    Dim strWorkbookName As String
    Dim MyFile As String
    Dim sDir As String
     
    MyFile = Sheets("Form").Range("C52").Text
     
    sDir = _
    "\\vumcnk-IOM1\D\Reports\CURRENT MONTH\" & MyFile
 
 
    On Error Resume Next
    Set wd = GetObject(, "Word.Application")
    If wd Is Nothing Then
        Set wd = CreateObject("Word.Application")
    End If
    On Error GoTo 0
 
    Set wdocSource = wd.Documents.Open("\\vumcnk-IOM1\D\Reports\CURRENT MONTH\Mars Patient Transcription Header Document.dotm")
 
    strWorkbookName = ThisWorkbook.Path & "\" & ThisWorkbook.Name
 
    wdocSource.MailMerge.MainDocumentType = wdFormLetters
 
    wdocSource.MailMerge.OpenDataSource _
            Name:=strWorkbookName, _
            AddToRecentFiles:=False, _
            Revert:=False, _
            Format:=wdOpenFormatAuto, _
            Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
            SQLStatement:="SELECT * FROM `Pull Sheet$`"
 
    With wdocSource.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
    
 
    wd.Visible = True
    wd.ActiveDocument.SaveAs FileName:=sDir & "\" & MyFile & ".doc"
    wdocSource.Close
    wd.ActiveDocument.Close
    wd.Quit
   
    
    Set wdocSource = Nothing
    Set wd = Nothing
 
End Sub
Thank you in advance for any help you may be able to provide. Without the help I have already received from folks on this site, I would have never been able to get as far with this project as I have.


Patrick