Hello,
I am attempting to import data from an Excel spreadsheet into a pre-made Word document using a macro in Excel.
The idea with these files is that they will be stored in a master folder and copied out by the Employees who will rename them according to the project they are working on (each project requires each of these files to be included with it).
Basically I would like the location of the data that the Word file I am working on to change dynamically based on the employee changing the location and name of the sheet.
I used the following code to attempt this:
It worked beautifully at first - change data in the spreadsheet, click the export button and a new word file is saved with the updated fields. However during testing it started giving me a variety of errors including:
"An operation cannot be completed because of database engine errors"
Which would bring up the Data Link properties tab in Word. I noticed in the source field it indicates the path to the original spreadsheet (that would normally be copied from) rather than the spreadsheet that is currently being used. This is despite me specifying that the data source is this path/this workbook etc.
Why wouldn't it use the spreadsheet currently being used as the source like indicated?
In addition, the 'Select Table' prompt suddenly started appearing after going through all these errors - but it is blank and triggers an error in code when you close/cancel/click OK. 'Run-time error '5922': Word was unable to open the data source.' when clicking OK or 'Run-time error 4198: command failed' when cancelling or closing.
The problem seems to be with the .OpenDataSource method of mailmerge.
This whole thing is really screwy - I've looked up all these errors but can't seem to find anything that will help my code work - maybe the whole thing needs to be redone? It seemed so simple but obviously something is messed up.
I'd appreciate any input your community could provide. Thank you for your time and effort.
Running Word/Excel 2010 on Windows 7 Enterprise.
I am attempting to import data from an Excel spreadsheet into a pre-made Word document using a macro in Excel.
The idea with these files is that they will be stored in a master folder and copied out by the Employees who will rename them according to the project they are working on (each project requires each of these files to be included with it).
Basically I would like the location of the data that the Word file I am working on to change dynamically based on the employee changing the location and name of the sheet.
I used the following code to attempt this:
Code:
Sub RunMailMerge() 'Will merge data on INPUTSsheet into customized document with fields for all data that needs to be entered
Dim wdOutputName, wdInputName, strWorkbookName As String 'file path names
wdOutputName = ThisWorkbook.Path & "\OutputDoc.doc") 'document to output
wdInputName = ThisWorkbook.Path & "WordInputDoc.doc" 'doc with fields used as base
' open the mail merge layout file
Dim wdDoc As Object 'declare object for word doc
Set wdDoc = GetObject(wdInputName, "Word.document") 'set object as Word document used as base
wdDoc.Application.Visible = True
strWorkbookName = ThisWorkbook.Path & "\" & ThisWorkbook.Name 'the string will use the location of the current Excel file used to define the database
With wdDoc.MailMerge 'run through mailmerge commands for input word document
.OpenDataSource _
Name:=strWorkbookName, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto = 0, _
Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
SQLStatement:="SELECT * FROM [IPNUTS$]" 'selects the data source used for the mailmerge - should be this spreadsheet under the INPUTS tab
.MainDocumentType = wdFormLetters
.Destination = wdSendToNewDocument 'merges results to new document
.SuppressBlankLines = True
.Execute Pause:=False 'report errors in a new document if any occur
End With
' show and save output file
wdDoc.Application.Visible = True
wdDoc.Application.ActiveDocument.SaveAs wdOutputName 'save as a new file - named after the output name given above
On Error Resume Next
' cleanup
wdDoc.Close SaveChanges:=False 'close out original base doc
Set wdDoc = Nothing 'clear object from memory
End Sub
It worked beautifully at first - change data in the spreadsheet, click the export button and a new word file is saved with the updated fields. However during testing it started giving me a variety of errors including:
"An operation cannot be completed because of database engine errors"
Which would bring up the Data Link properties tab in Word. I noticed in the source field it indicates the path to the original spreadsheet (that would normally be copied from) rather than the spreadsheet that is currently being used. This is despite me specifying that the data source is this path/this workbook etc.
Why wouldn't it use the spreadsheet currently being used as the source like indicated?
In addition, the 'Select Table' prompt suddenly started appearing after going through all these errors - but it is blank and triggers an error in code when you close/cancel/click OK. 'Run-time error '5922': Word was unable to open the data source.' when clicking OK or 'Run-time error 4198: command failed' when cancelling or closing.
The problem seems to be with the .OpenDataSource method of mailmerge.
This whole thing is really screwy - I've looked up all these errors but can't seem to find anything that will help my code work - maybe the whole thing needs to be redone? It seemed so simple but obviously something is messed up.
I'd appreciate any input your community could provide. Thank you for your time and effort.
Running Word/Excel 2010 on Windows 7 Enterprise.