Unable to open a Word document with Below VBA Code

[siddo]

Sub SOCMacro()

a = Range("B1").Value
b = Range("B2").Value
C = Range("B3").Value

Application.DisplayAlerts = False
Application.ScreenUpdating = False

d = a & "\" & b
e = a & "\" & C

Call MergeRun("d", "e", "Select [DateA], [Address], [Service Organization Name], [Title of description of service organization’s system],[As of Date(D)],[Date to Date(E)], [Date(H)], [Subservice Orgenaization Name], [Indicate service(s) provided by the subservice organization], [Type or name of system], [Insert the title of subservice organization assertion] FROM [RawData$A2:L5]")

End Sub

Sub MergeRun(frmFile As String, datfile As String, SQL As String)


Dim wdApp As Word.Application
Dim myDoc As Word.Document


Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set wdApp = CreateObject("Word.Application")
End If


With wdApp

.Visible = True


Set myDoc = .Documents.Open(frmFile, False, False, False)
.ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
.ActiveDocument.MailMerge.OpenDataSource Name:=datfile, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=False, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="", SQLStatement:=SQL, SQLstatement1:="", _
SubType:=wdMergeSubTypeOther

With wdApp.ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True


With .DataSource
.FirstRecord = 1
.LastRecord = 2
End With
.Execute Pause:=False
End With

wdApp.Application.DisplayAlerts = wdAlertsAll
End With

errorExit:
On Error Resume Next
myDoc.Close False

 
End Sub

Request if you could help

 

[siddo]

If it is a variable then get rid of the quotation marks around it in the Call line of code.

Same thing with the e variable.

I'm not sure where, but once I run the code the excel gets stuck, see if you can help. Thanks

Sub SOCMacro()

Dim d As String
Dim e As String
Dim strSql As String


a = Range("B1").Value
b = Range("B2").Value
C = Range("B3").Value

Application.DisplayAlerts = False
Application.ScreenUpdating = False

d = a & "\" & b
e = a & "\" & C
strSql = "SELECT * FROM 'RawData$'"

Call MergeRun(d, e, strSql)


End Sub

Sub MergeRun(frmFile As String, datfile As String, SQL As String, Optional bClose As Boolean = False, Optional bPrint As Boolean = False, _
Optional iNoCopies As Long = 1)

If Dir(frmFile) = "" Then
MsgBox "Form file does not exist." & vbLf & frmFile, vbCritical, "Exit - Missing Form File"
End If

If Dir(datfile) = "" Then
MsgBox "Data file does not exist." & vbLf & datfile, vbCritical, "Exit - Missing Data File"
End If
 
If Dir(frmFile) = "" Or Dir(datfile) = "" Then Exit Sub

Dim wdApp As New Word.Application, myDoc As Word.Document
 
With wdApp
.DisplayAlerts = wdAlertsNone

Set myDoc = .Documents.Open(Filename:=frmFile, ReadOnly:=False, AddToRecentFiles:=False)

With myDoc
With .MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=datfile, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=False, Revert:=False, _
AddToRecentFiles:=False, PassWordDocument:="", _
PasswordTemplate:="", WritePassWordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, Connection:="", _
SQLStatement:=SQL, SQLStatement1:="", SubType:=wdMergeSubTypeOther

With wdApp.ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
End With

With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With

.Execute Pause:=False
End With

.Close False

End With

.DisplayAlerts = wdAlertsAll

If bPrint = True Then .ActiveDocument.PrintOut Copies:=iNoCopies
If bClose = True Then
.ActiveDocument.Close False
.Quit

End If

End With

Set myDoc = Nothing: Set wdApp = Nothing
 
End Sub

 

[johnnyL]

I see that you have been googling. Once again though you have used a non descriptive term. The term 'stuck' does not help us help you.

Please explain, what is working, when it stops working, any errors encountered, does it just keep running and never stops, etc.

 

[johnnyL]

Please remember, we are not there looking over your shoulder. We can't see what the program is doing or not doing. You have to be as descriptive as possible to relate what is happening or not happening.

 

[siddo]

I see that you have been googling. Once again though you have used a non descriptive term. The term 'stuck' does not help us help you.

Please explain, what is working, when it stops working, any errors encountered, does it just keep running and never stops, etc.

Even I'm not sure where I'm stuck. Whenever I try to run the code it opens the word document and it does nothing it just gets stuck.

 

[siddo]

Please remember, we are not there looking over your shoulder. We can't see what the program is doing or not doing. You have to be as descriptive as possible to relate what is happening or not happening.

I understand that, however I'm not sure where I have made a mistake in the code so that I can point out. Just that whenever I run it. It opens the proper word document and then it gets stuck

 

[johnnyL]

So you have made some progress, now you can get the word document to open?

 

[siddo]

So you have made some progress, now you can get the word document to open?

Yes, Now I'm able to open the word document

 

[siddo]

In word I'm getting this popup

 

[johnnyL]

Just my opinion, but I would consider this thread as solved.

I would recommend that you post what you did to finally get the word file to open, and then start a new thread with a new question so the answer to your original question here is not lost in the shuffle.

I am not a moderator here, so take my opinion with a grain of salt as the saying goes.

 

[Macropod]

Duplicate post: Mailmerge to Excel - Stuck once the vba code opens word document
Cross-post: Mailmerge to Excel - Stuck once the vba code opens word document