Dear All,
I Have created the following pices of code that help me to fill the following filds (Date, TO and Text) in a word document automaticaly taking the relevant information from my database.
This is the code:
Compare Database
Option Explicit
Private objWord As Object
Private blOpenedWord As Boolean
-------------------------------------------------------------------------------------------
Public Function OpenWord()
On Error GoTo Err_OpenWord
Set objWord = GetObject(, "Word.Application")
objWord.Application.Visible = True
Exit_OpenWord:
Exit Function
Err_OpenWord:
If Err = 429 Then 'word is not running
Set objWord = CreateObject("Word.Application")
blOpenedWord = True
Resume Next
Else
MsgBox "Error no. " & Err.Number
Resume Exit_OpenWord
End If
End Function
---------------------------------------------------------------------------------------------------------------------------
Public Function OpenNewWordDoc()
' Uses OpenWord function to open Word
' Opens new blank document
On Error GoTo Err_OpenNewWordDoc
Dim docWord As Word.Document
OpenWord
Set docWord = objWord.Documents.Add
' insert text into the Word document
objWord.Selection.TypeText "This document was created automatically by Access"
objWord.Selection.TypeParagraph
objWord.Selection.TypeText "It will now be closed and saved"
objWord.Selection.TypeParagraphobjWord.Activate
docWord.Save
docWord.Close
Set docWord = Nothing
If blOpenedWord = True Then
objWord.Quit
End If
Set objWord = Nothing
Exit_OpenNewWordDoc:
Exit Function
Err_OpenNewWordDoc:
MsgBox "Error no. " & Err.Number
Resume Exit_OpenNewWordDoc
End Function
-------------------------------------------------------------------------------------------------------
Public Function OpenWordDoc()
On Error GoTo Err_OpenWordDoc
Dim fname As String
Dim docWord As Word.Document
OpenWord
' file name needs to include path
' if file to open is in same folder as database can use Path attribute of current project
fname = CurrentProject.Path & "\empMemo.doc"
Set docWord = objWord.Documents.Open(fname)
Exit_OpenWordDoc:
Exit Function
Err_OpenWordDoc:
MsgBox "Error no. " & Err.Number
Resume Exit_OpenWordDoc
End Function
---------------------------------------------------------------------------------------------------------------
Public Function CreateWordMemo()
' Open memo in Word and insert text
On Error GoTo CreateWordMemo_Error
Dim dbs As Database
Dim rstEmployees As Recordset
Dim appWord As Word.Application
Dim docWord As Word.Document
Dim fname As String
Dim strDate As String
strDate = Date 'assign date to string variable so we can print it
OpenWord ' use function to open Word
'Open recordset based on employee name query
Set dbs = CurrentDb()
Set rstEmployees = dbs.OpenRecordset("qryEmpFullName")
' open the memo document
' assume memo document in same folder as database
fname = CurrentProject.Path & "\empMemo.doc"
Set docWord = objWord.Documents.Open(fname)
objWord.Visible = True
With objWord
.Selection.Goto wdGoToBookmark, Name:="DateToday" 'move to date bookmark
.Selection.TypeText " " & strDate 'insert date
.Selection.Goto wdGoToBookmark, Name:="MemoToLine" 'move to memo line bookmark
End With
'Loop through recordset returned by query, inserting name of each employee
Do Until rstEmployees.EOF
objWord.Selection.TypeText rstEmployees!Employee & ", "
rstEmployees.MoveNext
Loop
'reset variables to nothing and free up memory for other processes
Set rstEmployees = Nothing
Set dbs = Nothing
Set docWord = Nothing
Set objWord = Nothing
Exit_CreateWordMemo:
Exit Function
CreateWordMemo_Error: 'error trapping routine
MsgBox Err.Description
Resume Exit_CreateWordMemo
End Function
I have tested the code it works absolutely fine.
What are my problems?
1) I can not make go the word cursor automaticaly to the text after I have inserted the Date: and TO: but it carry on inserting me more users in the TO: Location.
2) Also I have to try to save and close this work.
3) Also I am NOT ABLE TO attached this pice of code to my form, I have also thoguht to insert a combo box where I can select the person I want to send the memo and to attach a command button and open this word memo Document from the form.
I have a coomand button on my form and the combo box but I can not make work the final pices to open This Word Memo in Automation.
I am looking forwqard to hearing from you.
Carletto
I Have created the following pices of code that help me to fill the following filds (Date, TO and Text) in a word document automaticaly taking the relevant information from my database.
This is the code:
Compare Database
Option Explicit
Private objWord As Object
Private blOpenedWord As Boolean
-------------------------------------------------------------------------------------------
Public Function OpenWord()
On Error GoTo Err_OpenWord
Set objWord = GetObject(, "Word.Application")
objWord.Application.Visible = True
Exit_OpenWord:
Exit Function
Err_OpenWord:
If Err = 429 Then 'word is not running
Set objWord = CreateObject("Word.Application")
blOpenedWord = True
Resume Next
Else
MsgBox "Error no. " & Err.Number
Resume Exit_OpenWord
End If
End Function
---------------------------------------------------------------------------------------------------------------------------
Public Function OpenNewWordDoc()
' Uses OpenWord function to open Word
' Opens new blank document
On Error GoTo Err_OpenNewWordDoc
Dim docWord As Word.Document
OpenWord
Set docWord = objWord.Documents.Add
' insert text into the Word document
objWord.Selection.TypeText "This document was created automatically by Access"
objWord.Selection.TypeParagraph
objWord.Selection.TypeText "It will now be closed and saved"
objWord.Selection.TypeParagraphobjWord.Activate
docWord.Save
docWord.Close
Set docWord = Nothing
If blOpenedWord = True Then
objWord.Quit
End If
Set objWord = Nothing
Exit_OpenNewWordDoc:
Exit Function
Err_OpenNewWordDoc:
MsgBox "Error no. " & Err.Number
Resume Exit_OpenNewWordDoc
End Function
-------------------------------------------------------------------------------------------------------
Public Function OpenWordDoc()
On Error GoTo Err_OpenWordDoc
Dim fname As String
Dim docWord As Word.Document
OpenWord
' file name needs to include path
' if file to open is in same folder as database can use Path attribute of current project
fname = CurrentProject.Path & "\empMemo.doc"
Set docWord = objWord.Documents.Open(fname)
Exit_OpenWordDoc:
Exit Function
Err_OpenWordDoc:
MsgBox "Error no. " & Err.Number
Resume Exit_OpenWordDoc
End Function
---------------------------------------------------------------------------------------------------------------
Public Function CreateWordMemo()
' Open memo in Word and insert text
On Error GoTo CreateWordMemo_Error
Dim dbs As Database
Dim rstEmployees As Recordset
Dim appWord As Word.Application
Dim docWord As Word.Document
Dim fname As String
Dim strDate As String
strDate = Date 'assign date to string variable so we can print it
OpenWord ' use function to open Word
'Open recordset based on employee name query
Set dbs = CurrentDb()
Set rstEmployees = dbs.OpenRecordset("qryEmpFullName")
' open the memo document
' assume memo document in same folder as database
fname = CurrentProject.Path & "\empMemo.doc"
Set docWord = objWord.Documents.Open(fname)
objWord.Visible = True
With objWord
.Selection.Goto wdGoToBookmark, Name:="DateToday" 'move to date bookmark
.Selection.TypeText " " & strDate 'insert date
.Selection.Goto wdGoToBookmark, Name:="MemoToLine" 'move to memo line bookmark
End With
'Loop through recordset returned by query, inserting name of each employee
Do Until rstEmployees.EOF
objWord.Selection.TypeText rstEmployees!Employee & ", "
rstEmployees.MoveNext
Loop
'reset variables to nothing and free up memory for other processes
Set rstEmployees = Nothing
Set dbs = Nothing
Set docWord = Nothing
Set objWord = Nothing
Exit_CreateWordMemo:
Exit Function
CreateWordMemo_Error: 'error trapping routine
MsgBox Err.Description
Resume Exit_CreateWordMemo
End Function
I have tested the code it works absolutely fine.
What are my problems?
1) I can not make go the word cursor automaticaly to the text after I have inserted the Date: and TO: but it carry on inserting me more users in the TO: Location.
2) Also I have to try to save and close this work.
3) Also I am NOT ABLE TO attached this pice of code to my form, I have also thoguht to insert a combo box where I can select the person I want to send the memo and to attach a command button and open this word memo Document from the form.
I have a coomand button on my form and the combo box but I can not make work the final pices to open This Word Memo in Automation.
I am looking forwqard to hearing from you.
Carletto
