merge to word

[adaniele]

hi guys, this is the situation

I have a form where the user enter data.
I would like to let the user press a preview button and open a word doc containing the info entered in the form.

I created the doc and insert all the necessary fields coming from 3 different tables (wine,supplier and pop)

Code in the preview button:

    Dim objword As Object
    Set objword = CreateObject("word.application")
    Dim strSaveAs As String
    Dim strPath As String
    Dim strContract As String
    strPath = "S:\qf_qantas\wine\"
    strSaveAs = Me.ifsnumber & Date & ".doc"
          

    If IsNull(DLookup("ifsnumber", "pop", "ifsnumber=" & Me.ifsnumber & "")) Then
        MsgBox "You must save the POP first."
    Else
        Application.FollowHyperlink "W:\Documents and Settings\mdaniel\Application Data\Microsoft\Templates\QANTAS AIRWAYS.dot"
        'objword.ActiveDocument.SaveAs FileName:=strPath & strSaveAs
    End If

problems:
1- if i create the word as a DOC instead of a DOT an error comes saying that the file con not be open. Why?
2- if i create the word as a DOT i can not open it 'cos a msg appears asking if i want to run an sql to populate the document. how can avoid it?
3- i would like to open the word with the info coming from the form. How can i do it?

thx very much, max.

 

[adaniele]

more info....
i change the code by:

Private Sub poppreview_Click()

    strFileName = "W:\Documents and Settings\mdaniel\Application Data\Microsoft\Templates\QANTAS AIRWAYS.dot"
    Dim objword As Object
    Set objword = GetObject(strFileName, "Word.Document") ' Make Word visible.
    Dim strSaveAs As String
    Dim strPath As String
    strPath = "S:\qf_qantas\wine\"
    strSaveAs = Me.ifsnumber & Date & ".doc"
          

    If IsNull(DLookup("ifsnumber", "pop", "ifsnumber=" & Me.ifsnumber & "")) Then
        MsgBox "You must save the POP first."
    Else
        objword.Application.Visible = True 'filter the recors to the primary key on your form
        objword.MailMerge.DataSource.QueryString = "SELECT pop.*, supplier.*, wine.* FROM pop,supplier,wine " & _
        "WHERE pop.winecode=wine.winecode and pop.suppnumber=supplier.suppnumber and pop.ifsnumber=" & Me.ifsnumber & ""
        objword.ActiveDocument.SaveAs FileName:=strPath & strSaveAs
    End If
    
End Sub

Now it is working...BUT i can not save it . the last line brings me an error:

"run time error 5852
requested object is not available"

thx again, max.

 

[Krij]

hi guys, this is the situation

I have a form where the user enter data.
I would like to let the user press a preview button and open a word doc containing the info entered in the form.

I created the doc and insert all the necessary fields coming from 3 different tables (wine,supplier and pop)

Code in the preview button:

    Dim objword As Object
    Set objword = CreateObject("word.application")
    Dim strSaveAs As String
    Dim strPath As String
    Dim strContract As String
    strPath = "S:\qf_qantas\wine\"
    strSaveAs = Me.ifsnumber & Date & ".doc"
          

    If IsNull(DLookup("ifsnumber", "pop", "ifsnumber=" & Me.ifsnumber & "")) Then
        MsgBox "You must save the POP first."
    Else
        Application.FollowHyperlink "W:\Documents and Settings\mdaniel\Application Data\Microsoft\Templates\QANTAS AIRWAYS.dot"
        'objword.ActiveDocument.SaveAs FileName:=strPath & strSaveAs
    End If

problems:
1- if i create the word as a DOC instead of a DOT an error comes saying that the file con not be open. Why?
2- if i create the word as a DOT i can not open it 'cos a msg appears asking if i want to run an sql to populate the document. how can avoid it?
3- i would like to open the word with the info coming from the form. How can i do it?

thx very much, max.

To automate with Word you'll need to create a new .dot document. In the document insert a table to hold the different values from fields in the table of the database. Then make bookmarks in each tablecell and give them apropiate names. Then you'll need to code in vba (in the database). Here's an extract from an access 2000 database:

Public Function MakeAWordDoc(ByVal strFile As String)
On Error Resume Next

Dim dbMTW As DAO.Database
Dim rsMTW As DAO.Recordset
Dim strKUNUM, strKUNAVN, strKUADRESS As String
Dim Wordobj As Word.Application
strKUNUM = DLookup("[Custnr]", "tblCustomer", "[CustID] = " & Forms!frmCustomer!CustID)
strKUNAVN = UCase(DLookup("[Name]", "tblCustomer", "[CustID] = " & Forms!frmCustomer!CustID))
strKUADRESSE = UCase(DLookup("[Adress]", "tblCustomer", "[CustID] = " & Forms!frmCustomer!CustID))

Set dbMTW = CurrentDb()
Set rsMTW = dbMTW.OpenRecordset("tblCustomer", dbOpenTable)
rsMTW.Index = "PrimaryKey"
rsMTW.Seek "=", Forms!frmCustomer!CustID
'If rsMTW.NoMatch Then
'MsgBox "Did not find any!", vbOKOnly
'Exit Function
'End If
DoCmd.Hourglass True
Set Wordobj = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set Wordobj = CreateObject("Word.Application")
End If
Wordobj.Visible = True
Wordobj.Documents.Add _
Template:=strFile, _
NewTemplate:=False
Wordobj.Selection.Goto what:=wdGoToBookmark, name:="KNR"
Wordobj.Selection.TypeText "K.nr.: " & strKUNUM
'UCase(rsMTW![Custnr])
Wordobj.Selection.Goto what:=wdGoToBookmark, name:="NAME"
Wordobj.Selection.TypeText strKUNAVN
'UCase(rsMTW![Name])
Wordobj.Selection.Goto what:=wdGoToBookmark, name:="ADRESS"
Wordobj.Selection.TypeText strKUADRESSE
'UCase(rsMTW![Adress])

DoEvents
Wordobj.Activate

Set Wordobj = Nothing
DoCmd.Hourglass False

Exit Function
TemplateError:
Set Wordobj = Nothing
Exit Function

End Function

Then in the calling form of this function: behind a button make the following call:

Dim strM As String

'Assuming you have info about your own company in a table (be sure that 'LetterDOT' is pointing to the path where you stored the .dot):
strM = DLookUp("[LetterDOT]","tblCompanyInfo","[CompanyID] = 1)

MakeAWordDoc strM

 

[adaniele]

krij, thx very much 4 your hlp. it did not work 4 me.
i am not using recordset to get any data and i am not using any bookmark in the template. I tryed to fit your code in to mine, but it did not work.
Also, ii looks like my db do not recognise a few lines from your code.
ex
Dim Wordobj As Word.Application. (undefined method) .

Here is the last code i am using.
could you tell me how to avoid the msg where the word asks me if i want to populate the doc from my source, please?

do you know how can i do a save as , once the word is opened?
thx again 4 your time and hlp, max.

 

[adaniele]

krij, thx very much 4 your hlp. it did not work 4 me.
i am not using recordset to get any data and i am not using any bookmark in the template. I tryed to fit your code in to mine, but it did not work.
Also, ii looks like my db do not recognise a few lines from your code.
ex
Dim Wordobj As Word.Application. (undefined method) .

thx again 4 your time and hlp, max.

1234567890

 

[adaniele]

ok, lets start again.
i have a form with a button called pop previewed. i would like to populate a word doc and save it as a new doc with the name of the main field in the form.

what i have done so far is the following.

created the form (works)
created a templated. This template has fields from 3 different tables (wine, supplier and pop). In order to create the template i created a connection to the db and put each variable in the right place in the doc.

Problems:
when the word is opened, asks if i want to populate the doc from 1 of the 3 tables ONLY. why?
Is there any way to avoid this question from work?
Once the template is populated, i dont know how to save it . How?

here is my code:

Private sub poppreview_click()

Dim objword As Object 
Strfilename=”<filename and location>”


If IsNull(DLookup("ifsnumber", "pop", "ifsnumber=" & Me.ifsnumber & "")) Then 
MsgBox "You must save the POP first." 
Else
	Objword.application.visible=true
	Set objword=getobject(strfilename,”word.document”)
Endif

End sub

i also created a second template but using fields from a query.
the problem with this one is that show only the first record in the query.
The query contains all the necessary fields coming from the 3 tables, and in the main field has a filter: Forms!addpop!ifsnumber.
it seems like the filter is not working. why?


could you please hlp me?
thx, max.

 

[adaniele]

solved

here is the code that i get from another treath...

        'Create a Word instance
        Set appWord = CreateObject("Word.Application")
        appWord.Visible = True
        
        'Open the selected merge document
        strWordDoc = "W:\Documents and Settings\mdaniel\Application Data\Microsoft\Templates\QANTAS AIRWAYS2.dot"
        appWord.Documents.Open strWordDoc
        
        'Set the merge data source to the SQL statement, and do the merge
        strDBName = "S:\qf_space\wine\wine.mdb"
        strSQL = "select tempmergeaddpop.* from tempmergeaddpop;"
       
       With appWord
          .ActiveDocument.MailMerge.OpenDataSource Name:=strDBName, _
             LinkToSource:=True, SQLStatement:=strSQL
          .ActiveDocument.MailMerge.Destination = wdSendToNewDocument
          .ActiveDocument.MailMerge.Execute
          .Documents(strWordDoc).Close savechanges:=wdDoNotSaveChanges
          .ActiveDocument.Close savechanges:="s:\qf_space\wine\pops\test.doc"
       End With

thx for your hlp.