In your original post, you said that your VBA code opened a Word template.
However, in the code you posted, you open two Word documents (ie not Word
templates). As you know, one is the main mailmerge document and the other
is the datasource document.
In general terms, I think it would be better if your VBA code created a
new
document from a Word template (*.dot) for the main document and, unless
there is a special reason for keeping it, I would abandon the datasource
document altogether. There are a number of reasons for this.
MAIN DOCUMENT:
If you create a main mailmerge document from a template, you can use the
CREATEDATE field in the template to insert the date into the document.
The
resulting date will be the date the document was created from the
template.
If you then save the document and open it in future, the date does not
change. Normally, this is the best way to date a letter.
I would edit the main document and break the link with the datasource
document. To do this, open the main document (or template), click the
"Main
document Setup" icon on the Mail Merge Toolbar (in Word 2002), and select
"Normal Word Document".
DATASOURCE DOCUMENT:
If you must keep the datasource document, it would probably be safer to
use
a Word template: for, if you create a datasource document from a template,
then you can save the document before executing the merge and this will
not
change the template. For example, when you put your data string
(varSøkere)
into the datasource document, you do not save the document. I assume you
don't save because you do not want to change your original document as
that
would mess up the next merge. However, I'm not sure it's a good idea to
execute the merge before saving the datasource document. It could be the
main document will still see the datasource document as it is on disc, ie
without records. Maybe this is causing run-time error 4605. However, you
say it's worked OK like this for a number of years. Well, OK. But
intuitively, it doesn't feel right to me.
I know we are all supposed to use ADO and not DAO. However, I know DAO.
And DAO makes it easy (and maybe a bit faster) to do the following.
(Perhaps you could do something similar in ADO if you cannot use DAO.)
I would use your SQL string to create a temporary query and then use the
DoCmd.OutputTo method to save the data returned by the query as a RTF file
and execute the merge with the RTF file. Therefore, I would do this
(after
creating a reference to the DAO object library):
Sub MyMerge()
Const MY_QUERY_NAME As String = "MyTempQuery"
Const MY_DATASOURCE As String = "Data.rtf"
Dim objDB As DAO.Database
Dim objQDF As DAO.QueryDef
Dim objWRD As Word.Application
Dim objWDMAL As Word.Document
Dim strSQL As String
Dim strPath As String
Dim strMainPathName As String
Dim strDataPathName As String
Dim strFilnavn As String
... your code to set up strSQL
' Set up temporary query:
Set objDB = CurrentDb()
Set objQDF = objDB.CreateQueryDef(MY_QUERY_NAME)
objQDF.SQL = strSQL
objDB.QueryDefs.Refresh
' Output data from query:
strPath = Access.Application.CurrentProject.Path
If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
strDataPathName = strPath & MY_DATASOURCE
DoCmd.OutputTo acOutputQuery, MY_QUERY_NAME, acFormatRTF, _
strDataPathName
' Delete query:
objDB.QueryDefs.Delete MY_QUERY_NAME
' Open main template in Word:
Set objWord = New Word.Application
strMainPathName = strPath & strFilnavn & ".dot"
Set objWDMAL = objWord.Documents.Add(strMainPathName)
' It's faster to use the "With...End With" construct
' when calling out-of-process servers, like Word;
With objWDMAL.MailMerge
' Attach main document to RTF file:
.OpenDataSource DataDocPathName
' Exit if Datasource not attached to main document:
If .State <> wdMainAndDataSource Then
GoTo Exit_DataSourceNotAttached
End If
' See if there are any records:
If .DataSource.RecordCount = 0 Then
GoTo Exit_NoRecords
End If
' Do stuff with Datasource:
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
' Set mailmerge options:
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
' Check mailmerge state before executing merge:
If .State = wdMainAndDataSource Then
.Execute
Else
GoTo Exit_CannotExecuteMerge
End If
End With
objWDMAL.Close wdDoNotSaveChanges
objWord.Visible = True
Bye:
Set objQDF = Nothing
Set objDB = Nothing
Set objWDMAL = Nothing
Set objWRD = Nothing
Exit Sub
Exit_DataSourceNotAttached:
MsgBox "Data source not attached."
GoTo Bye
Exit_NoRecords:
MsgBox "No records. Merge cancelled."
GoTo Bye
Exit_CannotExecuteMerge:
MsgBox "Cannot execute mail merge"
GoTo Bye
End Sub
CONVERTING DOCUMENTS TO TEMPLATES:
As you know, a Word template has the (*.dot) filename extension and to
open
a template in VBA you use the Add method of the Documents
collection, eg:
Set objWdTab = objWord.Documents.Add _
(Application.CurrentProject.Path & "\Oppttab.dot")
Set objWdMal = objWord.Documents.Add( _
Application.CurrentProject.Path & _
("\" & strFilnavn & ".dot"))
You can change the names of your exiting documents from (*.doc) to (*.dot)
using Windows Explorer. Alternatively, you can open your documents in
Word
and then click File, SaveAs, and, in the "Save As Type" box, select
Document
Template (*.dot) to convert them to templates.
In future to edit and save the templates, you need to open the templates
as
templates, not documents. To do this, click File, New, and, in the
Templates
dialog (or the New dialog, depending on which version of Word you have),
click the TEMPLATE button in the "Create New" section. Then select the old
template and click OK to open it. After editing, save the template,
overwriting the old version of the template.
DIALOG PROMPTING TO INSERT DATA:
I have tested your observation about the dialog that shows when you open
the
document, ie the dialog that prompts you to agree to inserting data into
the
document. I have tested a document and a template linked to, and not
linked
to the datasource (ie four tests). When there is a link to data and I
open
the document or template manually, the prompt appears. But when I execute
the mail merge from VBA using the linked template or document, the merge
is
executed without error 4605.
Therefore, I'm not sure this is the reason you are getting error 4605.
Geoff
IndianaJonas said:
Thanks Geoff, but I am afraid this is not the answer!
Here's my code (sorry a lot of the names and stuff are in Norwegian!):
Private Sub OpptaksBrev()
On Error GoTo ErrHer
Dim objWord As Word.Application
Dim objWdTab As Word.Document
Dim objWdMal As Word.Document
Dim rstADO As ADODB.Recordset
Dim strSql As String
Dim varSøkere As Variant
Dim strKlasse As String
Dim strKlasseTrimmet As String
Dim strFilnavn As String
Dim boolVent As Boolean
If Me!chkGjeldende = True Then
If IsNull(Forms!frmSøkere!txtOpptaksdato) Or
IsNull(Forms!frmSøkere!txtForfall) Then
MsgBox "Du må fylle ut både opptaksdato og forfall på søkeren"
&
vbCr & _
"hvis du skal velge ""Gjeldende søker""!", vbCritical,
"Gjeldende søker"
Exit Sub
End If
End If
If Me!fraDokument = 3 Then
boolVent = True
End If
If IsNull(Forms!frmSøkere!txtAlt1) Then
MsgBox "Du må velge en klasse som søkeren søker på før du " & _
"skriver ut opptaksbrev!", vbCritical, "Velg klasse"
End If
If Me!chkGjeldende = True Then
strKlasse = Forms!frmSøkere!txtAlt1
Else
strKlasse = Me!cboKlasse
End If
strKlasseTrimmet = Trimmet(strKlasse)
strFilnavn = "opp" & strKlasseTrimmet
If boolVent Then
strFilnavn = strFilnavn & "v"
End If
Me.Visible = False
If Me!chkGjeldende = True Then
strSql = "SELECT * FROM [qryOpptaksbrev] " & _
"WHERE [Søkernr] = " & Forms!frmSøkere!txtSøkernummer
ElseIf boolVent = False Then
strSql = "SELECT * FROM [qryOpptaksbrev] " & _
"WHERE [Klasse 1] = '" & Me!cboKlasse & "'"
Else
strSql = "SELECT * FROM [qryOpptaksbrev] " & _
"WHERE [Klasse 1] = '" & Me!cboKlasse & "' And Venteliste = True"
End If
Set rstADO = New Recordset
Set rstADO.ActiveConnection = CurrentProject.Connection
rstADO.Open strSql, , adOpenKeyset, adLockOptimistic, adCmdText
varSøkere = rstADO.GetString
Set objWord = New Word.Application
Set objWdTab = objWord.Documents.Open _
(Application.CurrentProject.Path & "\Oppttab.doc")
With objWdTab.Bookmarks("Søkertabell").Range
.InsertAfter varSøkere
End With
Set objWdMal = objWord.Documents.Open( _
Application.CurrentProject.Path & _
("\" & strFilnavn & ".doc"))
objWdMal.MailMerge.Execute True
objWdTab.Close wdDoNotSaveChanges
objWdMal.Close wdDoNotSaveChanges
objWord.Visible = True
ExitHer:
Set rstADO = Nothing
Set objWord = Nothing
DoCmd.Close acForm, "frmOpptBrevInnst", acSaveNo
Exit Sub
As you see, I have a Word document as the template, containing the merge
fields, and prelinked to the merge source which is a tabulator seperated
list
in Word.
But, it still won't work, and I think I may have discovered why!
When I open the template, I get a message telling me:
"If you open this document, the following SQL-command will run:
SELECT * FROM M:\Flettedok.doc
Data from the database will be inserted in the document. Would you like
to
continue? -YES- -NO-"
Loosely translated from Norwegian!
If I can get rid of this box somehow, preferrably using VB, I believe I
will
have solved my problem! But I don't know how to get rid of it :-(
Thanks again!
Jonas
Geoff said:
On second thoughts, it might also be a good idea to check for the
existance
of the data file (and main mailmerge file?). See example below.
I'm not sure how the Dir() function reacts if it tries to access a drive
or
folder which either doesn't exist or to which the user does not have
access.
It may generate a run-time error instead of a zero-length string. I'm
not
sure. So in addition to the code below, it'd be best if you have some
error-handling code (eg On Error GoTo ...).
EXAMPLE:
If Len(Dir(strPathNameDataDoc)) = 0 Then
GoTo ErrorMessage1
End If
With objDOC.MailMerge
.OpenDataSource strPathNameDataDoc
If .State = wdMainAndDataSource Then
.Execute
Else
GoTo ErrorMessage2
End If
End With
Bye:
Exit Sub
ErrorMessage1:
MsgBox "The data document was not found. Mailmerge " _
& "cancelled.", vbExclamation + vbOKOnly, _
"Program Terminated"
GoTo Bye
ErrorMessage2:
MsgBox "The main mailmerge document was not successfully " _
& "attached to the data document. Mailmerge cancelled.", _
vbExclamation + vbOKOnly, "Program Terminated"
GoTo Bye
Hi,
Error 4605 results from running the Mailmerge Execute method when a
data
source is not attached to the main document. It's usual to check the
State
property of the Mailmerge object before running Execute, as follows:
With objDOC.MailMerge
.OpenDataSource strPathNameDataDoc
If .State = wdMainAndDataSource Then
.Execute
Else
GoTo ErrorMessage1
End If
End With
Bye:
Exit Sub
ErrorMessage1:
MsgBox "The main mailmerge document was not successfully " _
& "attached to the data document. Mailmerge cancelled.", _
vbExclamation + vbOKOnly, "Program Terminated"
GoTo Bye
The fact that you're getting this problem after the school upgraded
(when
it had worked perfectly well before) may imply that the school moved
the
data document or your application to a different drive or folder. It
seems
the main document is not attaching to the data document in their new
environment. Alternatively, maybe the user does not have permission
to
the data folder. It's something odd like that. In any case, it won't
hurt if your code checks the .State property and displays an error
message
rather than allow a run-time error.
Regards
Geoff
message
Once upon a time, I made an Access application for a school I was
working
for, in Access 2000.
On tuesday, I got a call from them, telling me that they have
upgraded
from
Office 2000 to Office 2003, and my application didn't work properly
anymore!
My application uses automation functions to create a list in a word
document, and then open a template document thet will use this list
as
a
record source for a mail-merge. The reason I created the list in
Word,
is
becaus the db is secured, so a Word mail mege wouldn't be able to
access
a
query in the secured access db. Anyway, the problem (I think) is with
the
main mail merge docun\ment, not the db! The whole thing has worked
perfectly,
for 5 (!!!) years, until they upgraded!
Now the same function tells them:
"4605 - This method or property is not available because the document
is
not
a mail merge main document."
I have tried just about everything I can think of, made a new
document,
inserted new merge fields, and so on...
"Is there anybvody out there..." (!) who knows of an easy solution to
my
problem? To me it looks as if this is an upgrade-compatibility
problem,
if
anyone knows otherwise, please tell me!
Any help will be highly appreciated, of course!
Jonas
