Need help modifying code

  • Thread starter Thread starter Renee Moffett
  • Start date Start date
R

Renee Moffett

I have code behind a form that works fine to produce a mail merge letter in
word. However, we are transitioning machines in my office. Previously, the
code that opened the mail merge source could be directed to the same path
for all machines. Now, some machines will have the front end on the D drive
(the old method) and newer machines will have it on C (their only drive).
Can someone offer a suggestion to modify the following code so that it will
work on either type of machine. Thanks.

Private Sub Print_Click()
Dim strTemplateName As String
Dim objWordApp As Word.Application
Dim wddoc As Word.Document

strTemplateName = DLookup("[FileName]", "tblFormLetterAddresses",
"[TemplateID]=" & Me!frameLetterChoices)

Set objWordApp = CreateObject("Word.Application")

With objWordApp
.Visible = True
.WindowState = wdWindowStateMaximize
End With

'Create New Document from Template
Set wddoc = objWordApp.Documents.Add(Template:=strTemplateName,
NewTemplate:=False)

'Select ODBC Data Source
wddoc.MailMerge.OpenDataSource Name:= _
"D:\My Databases\Atlas Database 1.0.mdb", ConfirmConversions:=False,
_
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto,
_
Connection:= _
"DSN=MS Access 97 Database;DBQ=D:\My Databases\Atlas Database
1.0.mdb;DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
, SQLStatement:="SELECT * FROM `tblFormLetter`", SQLStatement1:=""
'Mail Merge
With wddoc.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
wddoc.CLOSE SaveChanges:=wdDoNotSaveChanges
End With

Set wddoc = Nothing
Set objWordApp = Nothing

End Sub
 
Try getting the drive the db is on by

dim thePath as string
thePath = left(CurrentDb.Name,1)

This should give you either 'c' or 'd'. Then whenever you
refer to the path, set a variable to: thePath & the rest
of the name you were using and pass that variable to the
form/function/whatever.

Hope this helps!

-----Original Message-----
I have code behind a form that works fine to produce a mail merge letter in
word. However, we are transitioning machines in my office. Previously, the
code that opened the mail merge source could be directed to the same path
for all machines. Now, some machines will have the front end on the D drive
(the old method) and newer machines will have it on C (their only drive).
Can someone offer a suggestion to modify the following code so that it will
work on either type of machine. Thanks.

Private Sub Print_Click()
Dim strTemplateName As String
Dim objWordApp As Word.Application
Dim wddoc As Word.Document

strTemplateName = DLookup
Click to expand...
("[FileName]", "tblFormLetterAddresses",
"[TemplateID]=" & Me!frameLetterChoices)

Set objWordApp = CreateObject("Word.Application")

With objWordApp
.Visible = True
.WindowState = wdWindowStateMaximize
End With

'Create New Document from Template
Set wddoc = objWordApp.Documents.Add (Template:=strTemplateName,
NewTemplate:=False)

'Select ODBC Data Source
wddoc.MailMerge.OpenDataSource Name:= _
"D:\My Databases\Atlas Database 1.0.mdb", ConfirmConversions:=False,
_
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto,
_
Connection:= _
"DSN=MS Access 97 Database;DBQ=D:\My Databases\Atlas Database
1.0.mdb;DriverId=281;FIL=MS
Click to expand...
Access;MaxBufferSize=2048;PageTimeout=5;" _
 
Take out the hard coded references to the computer Hard disk
( "D:\My Databases\")

and replace them with a variable

Set the variable from a known local source on each machine
This could stored in the registry, on a local tbale or even a user
entry prompt (Users will get very sick of this real fast if you ask
them every time)

My choice to do this would be to use the registry as that is the
appropriate place to store macine specific info
MSACcess gives you tools to do this relatively safely. Look up
GetSetting in Help
 
Back
Top