Excel mail merge to Word Doc

[Gavin23]

Hi all
Been struggling a bit with a piece of code to try get my Word Doc to mail merge with data from my spreadsheet. Says it's having an issue with the last line i.e. "SQLStatement:="SELECT*FROM[Sheet2$]"

Assistance would be greatly appreciated - still new to working with this.

Sub Open_Word_Document()
'On Error Resume Next

'Opens a Word Document from Excel

Dim objWord As Object

Set objWord = CreateObject("Word.Application")
objWord.Visible = True

'Change the directory path and file name to the location
'of your document

objWord.Documents.Open "C:\Users\gavin\Desktop\funding\funding.docx"

With objWord

.ActiveDocument.MailMerge.OpenDataSource Name:="C:\users\gavin\desktop\funding\funding_Summary.xlsm" _
, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="", _
SQLStatement:="SELECT*FROM[Sheet2$]"

With .ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
End With
'
End Sub

 

[Joe4]

SQLStatement:="SELECT*FROM[Sheet2$]"

I don't know if this is the issue, but I think you should at least have spaces in there, i.e.

SQLStatement:="SELECT * FROM [Sheet2$]"

 

[Gavin23]

I don't know if this is the issue, but I think you should at least have spaces in there, i.e.

SQLStatement:="SELECT * FROM [Sheet2$]"

Thanks, already tried that but still getting the same msg.

 

[Joe4]

What is the actual name of the Excel Sheet and Table you are trying to pull the data from?

 

[Gavin23]

What is the actual name of the Excel Sheet and Table you are trying to pull the data from?

Workbook is called "Funding_Summary" and the sheet is called "Sheet2". If I open the Word doc itself, manually, then it asks to update details from "SELECT * FROM [Sheet2$]" and all is fine.....

 

[Joe4]

Workbook is called "Funding_Summary" and the sheet is called "Sheet2". If I open the Word doc itself, manually, then it asks to update details from "SELECT * FROM [Sheet2$]" and all is fine.....

So then is it working now?

 

[Gavin23]

So then is it working now?

Only if I open the Word doc up separately. If I try run the code from within the spreadsheet it gives me the error...Selecting "yes" below pulls the data into the Word doc

 

[AOCSet]

Gavin23, did you ever get this VBA to work, without manually opening Word?