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
Bookmarks