Word template loses datasource when vba is edited

L

Liz

I have a Word 2003 template (usedin Word 2007) that has worked fine
for a long while. I need to update it with a small change. When I do,
the part of the userform that has a link to data in a mail merge
datasource no longer works.

I haven't been successful in being able to update the vba and make the
datasource link "stick"!!

Any suggestions, help would be appreciated.

Thanks.

Liz
 
D

Doug Robbins - Word MVP

Show us the code and the change that you are making.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
 
L

Liz

Show us the code and the change that you are making.







- Show quoted text -
Click to expand...

Here is the vba currently in the template:

============================================================

Sub MustFillIn()

If Employee.Value = "" Then
MsgBox "The Employee field can not be blank!" _
& " You must make a selection", vbOKOnly
OKButton1.Enabled = False
Else
OKButton1.Enabled = True
End If

End Sub


Private Sub Employee_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Call MustFillIn
Employee.SetFocus

End Sub



Private Sub OKButton1_Click()



Documents("eelist.doc").Close SaveChanges:=wdDoNotSaveChanges
Unload UserForm1

On Error GoTo ErrorHandler
Set dsMain = ActiveDocument.MailMerge.DataSource

With ActiveDocument.MailMerge.DataSource
.ActiveRecord = wdFirstRecord
Do

If dsMain.DataFields("Name").Value = Employee.Value Then
dsMain.Included = True
Else
dsMain.Included = False
End If
If .ActiveRecord <> .RecordCount Then
.ActiveRecord = wdNextRecord
End If
Loop Until .ActiveRecord = .RecordCount
ErrorHandler:

End With

Selection.GoTo What:=wdGoToBookmark, Name:="OurFile"
Selection.Find.ClearFormatting
Selection.TypeText Text:=OurFile.Value
Selection.GoTo What:=wdGoToBookmark, Name:="YourFile"
Selection.Find.ClearFormatting
Selection.TypeText Text:=YourFile.Value

Selection.GoTo What:=wdGoToBookmark, Name:="Sent"
Selection.Find.ClearFormatting
Selection.TypeText Text:=Sent.Value

Selection.GoTo What:=wdGoToBookmark, Name:="ClientName"
Selection.Find.ClearFormatting
Selection.TypeText Text:=ClientName.Value
Selection.GoTo What:=wdGoToBookmark, Name:="Salutation"
Selection.Find.ClearFormatting
Selection.TypeText Text:=Salutation.Value

' Add Enclosure if it is checked off
If CheckBox1 = True Then
Selection.GoTo What:=wdGoToBookmark, Name:="Enclosure"
Selection.Find.ClearFormatting
Selection.TypeText Text:="Enclosure"
Selection.TypeParagraph
End If

' Add Secretary Initials if it is filled out
If Initials.Value = "" Then
Selection.GoTo What:=wdGoToBookmark, Name:="Initials"
Selection.Find.ClearFormatting
Else
Selection.GoTo What:=wdGoToBookmark, Name:="Initials"
Selection.HomeKey Unit:=wdLine, Extend:=wdExtend
Selection.Find.ClearFormatting
With Selection.Find
.Text = "/"
End With
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.EndKey Unit:=wdLine, Extend:=wdExtend
Selection.Delete Unit:=wdCharacter, Count:=1
Selection.TypeText Text:=Initials.Value
Selection.TypeParagraph
End If

' Add the Attention line if it exists by creating a table
If Attention.Value = "" Then
Selection.GoTo What:=wdGoToBookmark, Name:="Attention"
Selection.Find.ClearFormatting
Else
Selection.GoTo What:=wdGoToBookmark, Name:="Attention"
Selection.Find.ClearFormatting
Selection.TypeParagraph

ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=1,
NumColumns:= _
2, DefaultTableBehavior:=wdWord9TableBehavior,
AutoFitBehavior:= _
wdAutoFitFixed
With Selection.Tables(1)
If .Style <> "Table Normal" Then
.Style = "Table Normal"
End If
.ApplyStyleHeadingRows = True
.ApplyStyleLastRow = False
.ApplyStyleFirstColumn = True
.ApplyStyleLastColumn = False
End With
Selection.TypeText Text:="Attention:"
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:=Attention.Value
Selection.Tables(1).Columns(1).PreferredWidthType =
wdPreferredWidthPoints
Selection.Tables(1).Columns(1).PreferredWidth = InchesToPoints(1)
Selection.Move Unit:=wdColumn, Count:=1
Selection.SelectColumn
Selection.Columns.PreferredWidthType = wdPreferredWidthPoints
Selection.Columns.PreferredWidth = InchesToPoints(5.5)

Selection.Tables(1).Select
Selection.Font.Bold = wdToggle
Selection.MoveLeft Unit:=wdCharacter, Count:=1
End If

' Add the Re line if it exists by creating a table
If ReBox.Value = "" Then
Selection.GoTo What:=wdGoToBookmark, Name:="Re"
Selection.Find.ClearFormatting
Else
Selection.GoTo What:=wdGoToBookmark, Name:="Re"
Selection.Find.ClearFormatting
Selection.TypeParagraph
ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=1,
NumColumns:= _
2, DefaultTableBehavior:=wdWord9TableBehavior,
AutoFitBehavior:= _
wdAutoFitFixed
With Selection.Tables(1)
If .Style <> "Table Normal" Then
.Style = "Table Normal"
End If
.ApplyStyleHeadingRows = True
.ApplyStyleLastRow = False
.ApplyStyleFirstColumn = True
.ApplyStyleLastColumn = False
End With
Selection.TypeText Text:="Re:"
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:=ReBox.Value
Selection.Tables(1).Columns(1).PreferredWidthType =
wdPreferredWidthPoints
Selection.Tables(1).Columns(1).PreferredWidth = InchesToPoints
(0.5)
Selection.Move Unit:=wdColumn, Count:=1
Selection.SelectColumn
Selection.Columns.PreferredWidthType = wdPreferredWidthPoints
Selection.Columns.PreferredWidth = InchesToPoints(5.5)

Selection.MoveLeft Unit:=wdCharacter, Count:=1
End If

' Add the CC line if it exists by creating a table
If cc.Value = "" Then
Selection.GoTo What:=wdGoToBookmark, Name:="cc"
Selection.Find.ClearFormatting
Else
Selection.GoTo What:=wdGoToBookmark, Name:="cc"
Selection.Find.ClearFormatting
ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=1,
NumColumns:= _
2, DefaultTableBehavior:=wdWord9TableBehavior,
AutoFitBehavior:= _
wdAutoFitFixed
With Selection.Tables(1)
If .Style <> "Table Normal" Then
.Style = "Table Normal"
End If
.ApplyStyleHeadingRows = True
.ApplyStyleLastRow = False
.ApplyStyleFirstColumn = True
.ApplyStyleLastColumn = False
End With
Selection.TypeText Text:="cc:"
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:=cc.Value
Selection.Tables(1).Columns(1).PreferredWidthType =
wdPreferredWidthPoints
Selection.Tables(1).Columns(1).PreferredWidth = InchesToPoints
(0.5)
Selection.Move Unit:=wdColumn, Count:=1
Selection.SelectColumn
Selection.Columns.PreferredWidthType = wdPreferredWidthPoints
Selection.Columns.PreferredWidth = InchesToPoints(5.5)

Selection.Tables(1).Select
Selection.Font.Bold = wdToggle
Selection.MoveLeft Unit:=wdCharacter, Count:=1
End If

ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
Unlinklet

' Add the pathname but leave the codes in for Refresh
Selection.GoTo What:=wdGoToBookmark, Name:="Date"
Selection.InsertDateTime DateTimeFormat:="MMMM d, yyyy",
InsertAsField:= _
True, DateLanguage:=wdEnglishCanadian,
CalendarType:=wdCalendarWestern, _
InsertAsFullWidth:=False
Selection.GoTo What:=wdGoToBookmark, Name:="Path"
Selection.Font.Size = 7
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty,
Text:= _
"FILENAME ", PreserveFormatting:=True

Selection.GoTo What:=wdGoToBookmark, Name:="End"
AutoOpen

End Sub
Private Sub CancelButton2_Click()
Documents("eelist.doc").Close SaveChanges:=wdDoNotSaveChanges
Unload UserForm1

End Sub



Private Sub UserForm_Initialize()

Dim wd As Document

Dim sLastName As String

Dim strTableLastname As String, introw As Integer, tblEmployee As
Table
Application.Documents.Open ("C:\startup\eelist.doc")

Set wd = Application.Documents("eelist.doc")
Set tblEmployee = wd.Tables(1)

Max = wd.Tables(1).Rows.Count

For RowCount = 2 To Max

strTableLastname = tblEmployee.Columns(2).Cells
(RowCount).Range.Text
sLastName = Left(strTableLastname, Len(strTableLastname) - 2)
Employee.AddItem sLastName
Next RowCount

OKButton1.Enabled = False

Label23.ForeColor = RGB(255, 0, 0)
Label23.Font = Bold
Label24.ForeColor = RGB(255, 0, 0)
Label24.Font = Bold

End Sub
Private Sub cmdAddressBook_Click()
Dim strAdd As String
Dim iPtr As Integer
Dim iEnd As Integer
Const strADDRESS_DELIMETER = ", "
Dim strErrMsg As String
Dim strCcNames As String
Dim strToNames As String
Dim strTmpText As String
Dim strRtChar As String

On Error GoTo AddrBookError
strErrMsg = strERR_ADDR_BOOK

' Get To fields
strAdd = Application.GetAddress("", "<PR_COMPANY_NAME>" & vbCr &
"<PR_STREET_ADDRESS>" & vbCr & "<PR_LOCALITY>" & ", " &
"<PR_STATE_OR_PROVINCE>" & " " & "<PR_POSTAL_CODE>" & vbCr &
"<PR_COUNTRY>", 0, 1, 1)
iEnd = Len(strAdd)

' Location of the To names
iPtr = InStr(strAdd, vbCr)
Select Case iPtr
Case 0
' Nothing selected
Exit Sub

Case Else
' To selected
strToNames = (strAdd)

End Select

If Len(strToNames) Then
strTmpText = ClientName.Text
strRtChar = Right$(strTmpText, 1)
If (strRtChar = vbCr) Or (strRtChar = vbLf) Or (strRtChar =
strADDRESS_DELIMETER) Or (strRtChar = "") Then
strTmpText = strTmpText & strToNames
Else
strTmpText = strTmpText & strADDRESS_DELIMETER &
strToNames
End If
ClientName.Text = strTmpText
End If

ReturnAddrBk:
' SelectTextBox ClientName
Exit Sub

AddrBookError:
If Err.Number Then strErrMsg = strErrMsg & vbCr & Err.Description
' DisplayErrorMsg strErrMsg
GoTo ReturnAddrBk
End Sub
=================================================

The new document is created based on the template, and the eelist.doc
loads in the background. When the user elects to click the
CmdAddressBook button, it opens their Outlook 2007 Contacts list. They
choose their contact, then click OK in the Contacts dialogue. Instead
of being presented with their document, and the rest of the form to
fill, they are presented with the eelist.doc. Users get confused and
think they have lost their document and start closing windows. Too
confusing. I want to modify this code:

Dim strTableLastname As String, introw As Integer, tblEmployee As
Table
Application.Documents.Open ("C:\startup\eelist.doc")

TO

Dim strTableLastname As String, introw As Integer, tblEmployee As
Table
Application.Documents.Open ("C:\startup\eelist.doc"), Visible:=
False


Please note that I do not need to make ANY change in the vba for the
template, other than to click Debug => Compile project, to lose its
connection to the datasource...ie, the data for the ee I chose does
not get pulled from the eelist.doc.

Thanks for looking!

Liz
 
D

Doug Robbins - Word MVP

I would declare a document object that I would set to the document created
from the template (assume the document object is named TargetDoc) and then
after the line of code

Set wd = Application.Documents("eelist.doc")

insert

TargetDoc.Activate

to make TargetDoc the activedocument.

BTW your code could be vastly improved by using the Range object rather than
the Selection object.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
Show us the code and the change that you are making.







- Show quoted text -
Click to expand...

Here is the vba currently in the template:

============================================================

Sub MustFillIn()

If Employee.Value = "" Then
MsgBox "The Employee field can not be blank!" _
& " You must make a selection", vbOKOnly
OKButton1.Enabled = False
Else
OKButton1.Enabled = True
End If

End Sub


Private Sub Employee_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Call MustFillIn
Employee.SetFocus

End Sub



Private Sub OKButton1_Click()



Documents("eelist.doc").Close SaveChanges:=wdDoNotSaveChanges
Unload UserForm1

On Error GoTo ErrorHandler
Set dsMain = ActiveDocument.MailMerge.DataSource

With ActiveDocument.MailMerge.DataSource
.ActiveRecord = wdFirstRecord
Do

If dsMain.DataFields("Name").Value = Employee.Value Then
dsMain.Included = True
Else
dsMain.Included = False
End If
If .ActiveRecord <> .RecordCount Then
.ActiveRecord = wdNextRecord
End If
Loop Until .ActiveRecord = .RecordCount
ErrorHandler:

End With

Selection.GoTo What:=wdGoToBookmark, Name:="OurFile"
Selection.Find.ClearFormatting
Selection.TypeText Text:=OurFile.Value
Selection.GoTo What:=wdGoToBookmark, Name:="YourFile"
Selection.Find.ClearFormatting
Selection.TypeText Text:=YourFile.Value

Selection.GoTo What:=wdGoToBookmark, Name:="Sent"
Selection.Find.ClearFormatting
Selection.TypeText Text:=Sent.Value

Selection.GoTo What:=wdGoToBookmark, Name:="ClientName"
Selection.Find.ClearFormatting
Selection.TypeText Text:=ClientName.Value
Selection.GoTo What:=wdGoToBookmark, Name:="Salutation"
Selection.Find.ClearFormatting
Selection.TypeText Text:=Salutation.Value

' Add Enclosure if it is checked off
If CheckBox1 = True Then
Selection.GoTo What:=wdGoToBookmark, Name:="Enclosure"
Selection.Find.ClearFormatting
Selection.TypeText Text:="Enclosure"
Selection.TypeParagraph
End If

' Add Secretary Initials if it is filled out
If Initials.Value = "" Then
Selection.GoTo What:=wdGoToBookmark, Name:="Initials"
Selection.Find.ClearFormatting
Else
Selection.GoTo What:=wdGoToBookmark, Name:="Initials"
Selection.HomeKey Unit:=wdLine, Extend:=wdExtend
Selection.Find.ClearFormatting
With Selection.Find
.Text = "/"
End With
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.EndKey Unit:=wdLine, Extend:=wdExtend
Selection.Delete Unit:=wdCharacter, Count:=1
Selection.TypeText Text:=Initials.Value
Selection.TypeParagraph
End If

' Add the Attention line if it exists by creating a table
If Attention.Value = "" Then
Selection.GoTo What:=wdGoToBookmark, Name:="Attention"
Selection.Find.ClearFormatting
Else
Selection.GoTo What:=wdGoToBookmark, Name:="Attention"
Selection.Find.ClearFormatting
Selection.TypeParagraph

ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=1,
NumColumns:= _
2, DefaultTableBehavior:=wdWord9TableBehavior,
AutoFitBehavior:= _
wdAutoFitFixed
With Selection.Tables(1)
If .Style <> "Table Normal" Then
.Style = "Table Normal"
End If
.ApplyStyleHeadingRows = True
.ApplyStyleLastRow = False
.ApplyStyleFirstColumn = True
.ApplyStyleLastColumn = False
End With
Selection.TypeText Text:="Attention:"
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:=Attention.Value
Selection.Tables(1).Columns(1).PreferredWidthType =
wdPreferredWidthPoints
Selection.Tables(1).Columns(1).PreferredWidth = InchesToPoints(1)
Selection.Move Unit:=wdColumn, Count:=1
Selection.SelectColumn
Selection.Columns.PreferredWidthType = wdPreferredWidthPoints
Selection.Columns.PreferredWidth = InchesToPoints(5.5)

Selection.Tables(1).Select
Selection.Font.Bold = wdToggle
Selection.MoveLeft Unit:=wdCharacter, Count:=1
End If

' Add the Re line if it exists by creating a table
If ReBox.Value = "" Then
Selection.GoTo What:=wdGoToBookmark, Name:="Re"
Selection.Find.ClearFormatting
Else
Selection.GoTo What:=wdGoToBookmark, Name:="Re"
Selection.Find.ClearFormatting
Selection.TypeParagraph
ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=1,
NumColumns:= _
2, DefaultTableBehavior:=wdWord9TableBehavior,
AutoFitBehavior:= _
wdAutoFitFixed
With Selection.Tables(1)
If .Style <> "Table Normal" Then
.Style = "Table Normal"
End If
.ApplyStyleHeadingRows = True
.ApplyStyleLastRow = False
.ApplyStyleFirstColumn = True
.ApplyStyleLastColumn = False
End With
Selection.TypeText Text:="Re:"
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:=ReBox.Value
Selection.Tables(1).Columns(1).PreferredWidthType =
wdPreferredWidthPoints
Selection.Tables(1).Columns(1).PreferredWidth = InchesToPoints
(0.5)
Selection.Move Unit:=wdColumn, Count:=1
Selection.SelectColumn
Selection.Columns.PreferredWidthType = wdPreferredWidthPoints
Selection.Columns.PreferredWidth = InchesToPoints(5.5)

Selection.MoveLeft Unit:=wdCharacter, Count:=1
End If

' Add the CC line if it exists by creating a table
If cc.Value = "" Then
Selection.GoTo What:=wdGoToBookmark, Name:="cc"
Selection.Find.ClearFormatting
Else
Selection.GoTo What:=wdGoToBookmark, Name:="cc"
Selection.Find.ClearFormatting
ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=1,
NumColumns:= _
2, DefaultTableBehavior:=wdWord9TableBehavior,
AutoFitBehavior:= _
wdAutoFitFixed
With Selection.Tables(1)
If .Style <> "Table Normal" Then
.Style = "Table Normal"
End If
.ApplyStyleHeadingRows = True
.ApplyStyleLastRow = False
.ApplyStyleFirstColumn = True
.ApplyStyleLastColumn = False
End With
Selection.TypeText Text:="cc:"
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:=cc.Value
Selection.Tables(1).Columns(1).PreferredWidthType =
wdPreferredWidthPoints
Selection.Tables(1).Columns(1).PreferredWidth = InchesToPoints
(0.5)
Selection.Move Unit:=wdColumn, Count:=1
Selection.SelectColumn
Selection.Columns.PreferredWidthType = wdPreferredWidthPoints
Selection.Columns.PreferredWidth = InchesToPoints(5.5)

Selection.Tables(1).Select
Selection.Font.Bold = wdToggle
Selection.MoveLeft Unit:=wdCharacter, Count:=1
End If

ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
Unlinklet

' Add the pathname but leave the codes in for Refresh
Selection.GoTo What:=wdGoToBookmark, Name:="Date"
Selection.InsertDateTime DateTimeFormat:="MMMM d, yyyy",
InsertAsField:= _
True, DateLanguage:=wdEnglishCanadian,
CalendarType:=wdCalendarWestern, _
InsertAsFullWidth:=False
Selection.GoTo What:=wdGoToBookmark, Name:="Path"
Selection.Font.Size = 7
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty,
Text:= _
"FILENAME ", PreserveFormatting:=True

Selection.GoTo What:=wdGoToBookmark, Name:="End"
AutoOpen

End Sub
Private Sub CancelButton2_Click()
Documents("eelist.doc").Close SaveChanges:=wdDoNotSaveChanges
Unload UserForm1

End Sub



Private Sub UserForm_Initialize()

Dim wd As Document

Dim sLastName As String

Dim strTableLastname As String, introw As Integer, tblEmployee As
Table
Application.Documents.Open ("C:\startup\eelist.doc")

Set wd = Application.Documents("eelist.doc")
Set tblEmployee = wd.Tables(1)

Max = wd.Tables(1).Rows.Count

For RowCount = 2 To Max

strTableLastname = tblEmployee.Columns(2).Cells
(RowCount).Range.Text
sLastName = Left(strTableLastname, Len(strTableLastname) - 2)
Employee.AddItem sLastName
Next RowCount

OKButton1.Enabled = False

Label23.ForeColor = RGB(255, 0, 0)
Label23.Font = Bold
Label24.ForeColor = RGB(255, 0, 0)
Label24.Font = Bold

End Sub
Private Sub cmdAddressBook_Click()
Dim strAdd As String
Dim iPtr As Integer
Dim iEnd As Integer
Const strADDRESS_DELIMETER = ", "
Dim strErrMsg As String
Dim strCcNames As String
Dim strToNames As String
Dim strTmpText As String
Dim strRtChar As String

On Error GoTo AddrBookError
strErrMsg = strERR_ADDR_BOOK

' Get To fields
strAdd = Application.GetAddress("", "<PR_COMPANY_NAME>" & vbCr &
"<PR_STREET_ADDRESS>" & vbCr & "<PR_LOCALITY>" & ", " &
"<PR_STATE_OR_PROVINCE>" & " " & "<PR_POSTAL_CODE>" & vbCr &
"<PR_COUNTRY>", 0, 1, 1)
iEnd = Len(strAdd)

' Location of the To names
iPtr = InStr(strAdd, vbCr)
Select Case iPtr
Case 0
' Nothing selected
Exit Sub

Case Else
' To selected
strToNames = (strAdd)

End Select

If Len(strToNames) Then
strTmpText = ClientName.Text
strRtChar = Right$(strTmpText, 1)
If (strRtChar = vbCr) Or (strRtChar = vbLf) Or (strRtChar =
strADDRESS_DELIMETER) Or (strRtChar = "") Then
strTmpText = strTmpText & strToNames
Else
strTmpText = strTmpText & strADDRESS_DELIMETER &
strToNames
End If
ClientName.Text = strTmpText
End If

ReturnAddrBk:
' SelectTextBox ClientName
Exit Sub

AddrBookError:
If Err.Number Then strErrMsg = strErrMsg & vbCr & Err.Description
' DisplayErrorMsg strErrMsg
GoTo ReturnAddrBk
End Sub
=================================================

The new document is created based on the template, and the eelist.doc
loads in the background. When the user elects to click the
CmdAddressBook button, it opens their Outlook 2007 Contacts list. They
choose their contact, then click OK in the Contacts dialogue. Instead
of being presented with their document, and the rest of the form to
fill, they are presented with the eelist.doc. Users get confused and
think they have lost their document and start closing windows. Too
confusing. I want to modify this code:

Dim strTableLastname As String, introw As Integer, tblEmployee As
Table
Application.Documents.Open ("C:\startup\eelist.doc")

TO

Dim strTableLastname As String, introw As Integer, tblEmployee As
Table
Application.Documents.Open ("C:\startup\eelist.doc"), Visible:=
False


Please note that I do not need to make ANY change in the vba for the
template, other than to click Debug => Compile project, to lose its
connection to the datasource...ie, the data for the ee I chose does
not get pulled from the eelist.doc.

Thanks for looking!

Liz
 
L

Liz

I would declare a document object that I would set to the document created
from the template (assume the document object is named TargetDoc) and then
after the line of code

Set wd = Application.Documents("eelist.doc")

insert

TargetDoc.Activate

to make TargetDoc the activedocument.
Click to expand...

THanks Doug, I will give this a try!!

BTW your code could be vastly improved by using the Range object rather than
the Selection object.
Click to expand...

ABSOLUTELY!!! I did not create this mess...they hired a "professional"
several years ago to create templates, and then some in-house trainers
have fiddled with them, and then "expert" users themselves have
fiddled. We have hundreds of templates and macros that break...on a
daily basis. Enough to keep my busy for years :)

I do what I can, I have a better plan (imho), as they have bits and
pieces of data stored all over, I want to consolidate, centralize and
have more intuitativeness built into the templates. But it will take
time. Of this one document, there must be 24 that have the same logic
(letters on letterhead, letters with logos embedded, letters that
print in colour, letters that print on plain, and then the faxes, and
the letters by email, etc. for several locations (so different address
info, ad nauseum), so if I can fix this one, then I will have to apply
the same to the rest of them.

Thanks again, I will post my results.

Liz
 
L

Liz

THanks Doug, I will give this a try!!




ABSOLUTELY!!! I did not create this mess...they hired a "professional"
several years ago to create templates, and then some in-house trainers
have fiddled with them, and then "expert" users themselves have
fiddled.  We have hundreds of templates and macros that break...on a
daily basis. Enough to keep my busy for years :)

I do what I can, I have a better plan (imho), as they have bits and
pieces of data stored all over, I want to consolidate, centralize and
have more intuitativeness built into the templates. But it will take
time. Of this one document, there must be 24 that have the same logic
(letters on letterhead, letters with logos embedded, letters that
print in colour, letters that print on plain, and then the faxes, and
the letters by email, etc. for several locations (so different address
info, ad nauseum), so if I can fix this one, then I will have to apply
the same to the rest of them.

Thanks again, I will post my results.

Liz
Click to expand...

Hi again, Doug...I have made the suggested change. This caused a
problem elsewhere - which is not of concern - right now, but it still
did not fix my problem of once I edit the vba the template loses its
link to the data source.

Any other rabbits to pull out of the hat?

Liz
 
D

Doug Robbins - Word MVP

I realise now that it is the loss of the datasource when you modify the
template that is really the issue.

When you use the term datasource, are you using it as it applies to
mailmerge or is the document ("C:\startup\eelist.doc") the datasource to
which you are referring?

When you modify the vba code, are you saving the template as a .dotm (Word
2007 macro enabled template)? If the template is (or was) actually a mail
merge main document, changing the file type to .dotm might be what is
deleting the mail merge data source. If that is the case, try re-attaching
the data source before saving the template with the modified code.

Before I came to the first mentioned realization, I cobbled together enough
of what you have to confirm that using the following as the Initialize event
for the userform results in the focus returning the the document from which
the userform was called, and the userform itself, when a name is selected in
the Select Name dialog (that is called by the Private Sub
cmdAddressBook_Click() routine) and the OK button in that dialog is clicked,
loading the data into the ClientName control on the userform. In effect,
while the eelist.doc is opened, it remains in the background and unless the
user was to click on it in the taskbar, they would not see it.

Private Sub UserForm_Initialize()
Dim Target As Document
Dim wd As Document
Dim sLastName As String
Dim strTableLastname As String, introw As Integer, tblEmployee As Table
Set Target = ActiveDocument
Set wd = Documents.Open("C:\startup\eelist.doc")
Set tblEmployee = wd.Tables(1)
Target.Activate

Max = wd.Tables(1).Rows.Count

For RowCount = 2 To Max
strTableLastname = tblEmployee.Columns(2).Cells(RowCount).Range.Text
sLastName = Left(strTableLastname, Len(strTableLastname) - 2)
Employee.AddItem sLastName
Next RowCount

OKButton1.Enabled = False

Label23.ForeColor = RGB(255, 0, 0)
Label23.Font = Bold
Label24.ForeColor = RGB(255, 0, 0)
Label24.Font = Bold

End Sub



--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
THanks Doug, I will give this a try!!




ABSOLUTELY!!! I did not create this mess...they hired a "professional"
several years ago to create templates, and then some in-house trainers
have fiddled with them, and then "expert" users themselves have
fiddled. We have hundreds of templates and macros that break...on a
daily basis. Enough to keep my busy for years :)

I do what I can, I have a better plan (imho), as they have bits and
pieces of data stored all over, I want to consolidate, centralize and
have more intuitativeness built into the templates. But it will take
time. Of this one document, there must be 24 that have the same logic
(letters on letterhead, letters with logos embedded, letters that
print in colour, letters that print on plain, and then the faxes, and
the letters by email, etc. for several locations (so different address
info, ad nauseum), so if I can fix this one, then I will have to apply
the same to the rest of them.

Thanks again, I will post my results.

Liz
Click to expand...

Hi again, Doug...I have made the suggested change. This caused a
problem elsewhere - which is not of concern - right now, but it still
did not fix my problem of once I edit the vba the template loses its
link to the data source.

Any other rabbits to pull out of the hat?

Liz
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA word change keys 0
Dynamic Subject Line Email Merge 0
Embed the same Excel multiple times 0
Word VBA - Delete text from bulleted paragraphs 1
Linking data in MS Word into Excel 5
VBA mailmerge event 0
Word 2016 opening sooo slow 0
Mail Merge in Word template 0

Top