Is there a way for me to perform a mail merge and use a data source that is will be opened in a new instance of Excel? Currently I modified some code to work for me, but the VBA opens the workbook in one instance, then when the code for mail merge occurs, it opens a second instance of the same workbook name. I have a progress bar that tells how far along the code is running and the screen keeps flickering even with everything turned off.
VBA Code:
myPath = ThisWorkbook.Path & "\signdata\"
arrayCount = CountFilesInFolder(myPath)
Debug.Print arrayCount
'Target File Extension (must include wildcard "*")
myExtension = "*.xlsx*"
'Target Path with Ending Extention
myFile = Dir(myPath & myExtension)
counter = 0
'Loop through each Excel file in folder
Do While myFile <> ""
'Set variable equal to opened workbook
Set wb = app.Workbooks.Open(Filename:=myPath & myFile)
strWorkbookName = wb.Path & "\" & myFile
app.Visible = True
'Set newSheet = wb.Worksheets(1)
rowCount = wb.Sheets("Sheet1").Cells(Rows.count, 1).End(xlUp).Row
'Ensure Workbook has opened before moving on to next line of code
DoEvents
'Get next file name
myFile = Dir
'Start of mailmerge
wd.Visible = True
Set wdocSource = wd.Documents.Open(SavePath & "\Template\Closeout_Signs_TEMPLATE.doc")
wdocSource.MailMerge.MainDocumentType = wdFormLetters
wdocSource.MailMerge.OpenDataSource _
Name:=strWorkbookName, _
AddToRecentFiles:=False, _
Revert:=False, _
Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
SQLStatement:="SELECT * FROM `Sheet1$`"
Debug.Print Name
Debug.Print Connection
With wdocSource.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = 3
.LastRecord = rowCount
End With
.Execute Pause:=False
End With
On Error Resume Next
wd.ActiveDocument.FitToPages
Dim PathToSave As String
