Save a word file from Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have a database that opens a Word mail merge template and drops a record
into it. (This all works fine)
What I'm trying to do next is this:
After Access has opened the Word Template file and put the returned record
in to it, I want it to automatically run a "Save As" save which will use the
2 mailmerge field values as the file name. Then I want it to close down Word
and return to Access.

Is this possible??
And please, if it is, can you reply with a real easy to follow example 'cos
my brains might just melt!!!

Thank you
 
It's possible, but you need to show us the code you're using to create
the document and "drop a record into it". Basically you just open a
recordset to get the field values, concatenate them, add the folder name
at the start and ".doc" at the end, and pass the result to the SaveAs
method of your Word document object.
 
Hi John.

I'm using the code below to open a Word mail merge template (from an access
form) that has been set up to use a table from this database.
(The table only holds 1 record wich is written in using a 'make table'
query.)

I'd like to be able to send word a 'save as' command, then as you have
sugested, read and concatonate the first 2 fields from the table into the
file name.


Thanks in advance.

**************************************************
Private Sub btn_contract_Click()
On Error GoTo Err_btn_contract_Click

Dim stDocName As String
Dim retval
Dim Prog, File As String
Dim oRst As ADODB.Recordset
Dim oConn As ADODB.Connection
Dim sSQL As String
Dim sConn As String
Dim this_db As String

'Automates the connection string
this_db = CurrentProject.FullName

'sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Dbs\Work
Apps\KM_DB\Mail_merge.mdb;Persist Security Info=False"
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & this_db &
";Persist Security Info=False"

Set oConn = New ADODB.Connection
With oConn
.ConnectionString = sConn
.Open
End With

sSQL = "SELECT option_value FROM [options] WHERE option_name =
'path_to_template'"

Set oRst = New ADODB.Recordset
With oRst
.ActiveConnection = oConn
.CursorLocation = adUseClient
DoCmd.Hourglass True
.Open sSQL, , adOpenForwardOnly, adLockReadOnly
If Not oRst.EOF Then
File = oRst.Fields("option_value").Value
End If
DoCmd.Hourglass False
End With

oRst.Close
oConn.Close
Set oRst = Nothing
Set oConn = Nothing

DoCmd.SetWarnings False
'Runs the make table query
stDocName = "CreateMergeToContractTable"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Prog = "C:\Program Files\Microsoft Office\Office10\winword.exe"

DoCmd.SetWarnings True
'Opens the word template
retval = Shell(Prog & " " & File, vbNormalNoFocus)


Exit_btn_contract_Click:
Exit Sub

Err_btn_contract_Click:
MsgBox Err.Description
Resume Exit_btn_contract_Click

End Sub

**************************************************
 
Try something like this, replacing this part of your existing code:
Prog = "C:\Program Files\Microsoft Office\Office10\winword.exe"

DoCmd.SetWarnings True
'Opens the word template
retval = Shell(Prog & " " & File, vbNormalNoFocus)
Click to expand...

You'll need to go to Tools|References and set a reference to the
Microsoft Word Object Library.

Dim oWordApp As Word.Application
Dim oDoc As Word.Document

'Create an instance of Word
Set oWord = CreateObject("Word.Application")
'Open the document
Set oDoc = oWord.Documents.Open(File)
'NB: if File is a .dot (Word template file), use this instead:
'Create new document from template
Set oDoc = oWord.Documents.Add(File)
'Save the document - replace XXX with a variable containing the
'path and filename
oDoc.SaveAs XXX
oDoc.Close
Set oDoc = Nothing
'Close Word
oWord.Quit
Set oWord = Nothing

'and on to the error handler

Hi John.

I'm using the code below to open a Word mail merge template (from an access
form) that has been set up to use a table from this database.
(The table only holds 1 record wich is written in using a 'make table'
query.)

I'd like to be able to send word a 'save as' command, then as you have
sugested, read and concatonate the first 2 fields from the table into the
file name.


Thanks in advance.

**************************************************
Private Sub btn_contract_Click()
On Error GoTo Err_btn_contract_Click

Dim stDocName As String
Dim retval
Dim Prog, File As String
Dim oRst As ADODB.Recordset
Dim oConn As ADODB.Connection
Dim sSQL As String
Dim sConn As String
Dim this_db As String

'Automates the connection string
this_db = CurrentProject.FullName

'sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Dbs\Work
Apps\KM_DB\Mail_merge.mdb;Persist Security Info=False"
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & this_db &
";Persist Security Info=False"

Set oConn = New ADODB.Connection
With oConn
.ConnectionString = sConn
.Open
End With

sSQL = "SELECT option_value FROM [options] WHERE option_name =
'path_to_template'"

Set oRst = New ADODB.Recordset
With oRst
.ActiveConnection = oConn
.CursorLocation = adUseClient
DoCmd.Hourglass True
.Open sSQL, , adOpenForwardOnly, adLockReadOnly
If Not oRst.EOF Then
File = oRst.Fields("option_value").Value
End If
DoCmd.Hourglass False
End With

oRst.Close
oConn.Close
Set oRst = Nothing
Set oConn = Nothing

DoCmd.SetWarnings False
'Runs the make table query
stDocName = "CreateMergeToContractTable"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Prog = "C:\Program Files\Microsoft Office\Office10\winword.exe"

DoCmd.SetWarnings True
'Opens the word template
retval = Shell(Prog & " " & File, vbNormalNoFocus)


Exit_btn_contract_Click:
Exit Sub

Err_btn_contract_Click:
MsgBox Err.Description
Resume Exit_btn_contract_Click

End Sub

**************************************************
Click to expand...
 
Thank you John. That's exactly what I was trying to achieve.

Cheers

John Nurick said:
Try something like this, replacing this part of your existing code:
Prog = "C:\Program Files\Microsoft Office\Office10\winword.exe"

DoCmd.SetWarnings True
'Opens the word template
retval = Shell(Prog & " " & File, vbNormalNoFocus)
Click to expand...

You'll need to go to Tools|References and set a reference to the
Microsoft Word Object Library.

Dim oWordApp As Word.Application
Dim oDoc As Word.Document

'Create an instance of Word
Set oWord = CreateObject("Word.Application")
'Open the document
Set oDoc = oWord.Documents.Open(File)
'NB: if File is a .dot (Word template file), use this instead:
'Create new document from template
Set oDoc = oWord.Documents.Add(File)
'Save the document - replace XXX with a variable containing the
'path and filename
oDoc.SaveAs XXX
oDoc.Close
Set oDoc = Nothing
'Close Word
oWord.Quit
Set oWord = Nothing

'and on to the error handler

Hi John.

I'm using the code below to open a Word mail merge template (from an access
form) that has been set up to use a table from this database.
(The table only holds 1 record wich is written in using a 'make table'
query.)

I'd like to be able to send word a 'save as' command, then as you have
sugested, read and concatonate the first 2 fields from the table into the
file name.


Thanks in advance.

**************************************************
Private Sub btn_contract_Click()
On Error GoTo Err_btn_contract_Click

Dim stDocName As String
Dim retval
Dim Prog, File As String
Dim oRst As ADODB.Recordset
Dim oConn As ADODB.Connection
Dim sSQL As String
Dim sConn As String
Dim this_db As String

'Automates the connection string
this_db = CurrentProject.FullName

'sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=N:\Dbs\Work
Apps\KM_DB\Mail_merge.mdb;Persist Security Info=False"
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & this_db &
";Persist Security Info=False"

Set oConn = New ADODB.Connection
With oConn
.ConnectionString = sConn
.Open
End With

sSQL = "SELECT option_value FROM [options] WHERE option_name =
'path_to_template'"

Set oRst = New ADODB.Recordset
With oRst
.ActiveConnection = oConn
.CursorLocation = adUseClient
DoCmd.Hourglass True
.Open sSQL, , adOpenForwardOnly, adLockReadOnly
If Not oRst.EOF Then
File = oRst.Fields("option_value").Value
End If
DoCmd.Hourglass False
End With

oRst.Close
oConn.Close
Set oRst = Nothing
Set oConn = Nothing

DoCmd.SetWarnings False
'Runs the make table query
stDocName = "CreateMergeToContractTable"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Prog = "C:\Program Files\Microsoft Office\Office10\winword.exe"

DoCmd.SetWarnings True
'Opens the word template
retval = Shell(Prog & " " & File, vbNormalNoFocus)


Exit_btn_contract_Click:
Exit Sub

Err_btn_contract_Click:
MsgBox Err.Description
Resume Exit_btn_contract_Click

End Sub

**************************************************
Click to expand...
Click to expand...
 
Back
Top