Excel 2003 as DataSource problem

[expressplanweb2]

I walked through the MS training of the mail merge process on how to
use the toolbar, and it seemed (and originally was) pretty straight-
forward. My actual objective was to create a way to merge ONE row of
Excel data (not mailing fields but custom fields) with ONE Word
document, via a macro. I needed to do it manually before I could
record the macro. The idea was I could pick a row, copy it into my
DataSource Sheet2$ and then execute my macro in Word.

I went through the toolbar, left to right. Set the document type as
NORMAL, connected to the DataSource, choosing the Sheet2$ of the
spreadsheet as the actual DataSource, added my fields, clicked Merge
to New Document, and it worked as advertised. So far so good. So I
recorded this macro in Word:


Sub Grabbitt()
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord =
ActiveDocument.MailMerge.DataSource.ActiveRecord
.LastRecord =
ActiveDocument.MailMerge.DataSource.ActiveRecord
End With
.Execute Pause:=False
End With
Selection.WholeStory
Selection.Copy
End Sub

Pretty simple, right? The macro worked the first time, but when I
copied a new row into Sheet2$ and saved the Excel sheet, re-executing
the macro did not pick up the new row. I assumed that was because no
re-querying of the DataSource took place. So I looked around with the
Object Browser to find a method of the DataSource object that would
refresh its query; to no avail. I want to avoid closing and opening
the Merge Document, because that entails too much time and turns my
time-saving macro into a time waster.

What should I do? That's the first question. Please, if time permits,
read on...

Problem 2 occurred after I tried to reopen all the documents after a
restart on my system. This time, upon trying to reopen the Merge
Document, it reported it could not locate its DataSource. Nothing had
moved. I tried detaching the DataSource (one of the options in the
error dialog for the "could not locate" error). Now, upon attempting
to reattach, I navigated to the spreadsheet, chose Sheet2$, but now it
wants me to SELECT TABLE, off of a blank ListBox. (nothing to Select).
So now, I cannot get my DataSource reattached. It never presented that
dialog the first time around. What causes this and how do I get past
it and reselect my DataSource, and why couldn't it find it in the
first place?

Any help on these two related issues would be greatly appreciated.
It's all Office 2003 Professional SP3

 

[Doug Robbins - Word MVP]

If all that you are wanting to do is create a document that contains the
data of one particular row in your spreadsheet, I would use a different
approach - a userform containing a combobox that was populated with the data
from the spreadsheet so that you could select an individual item and then
click on a command button on the form so that the datat from that item would
be inserted into the document.

To get started, see the article "How to create a Userform" at:

http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm

Then to load a combobox on the userform with the data from a range that is
named "Contacts" in a workbook Contacts.xls that is saved in the same folder
as your template, you would use the following initialize routine for the
userform:

Private Sub UserForm_Initialize()



' Requires that a reference be set to the Microsoft DAO 3.6 Object
Library

' Open the file containing the Contacts, assumed to be in the same
folder as the template

Set db = OpenDatabase(ThisDocument.Path & Application.PathSeparator &
"Contacts.xls", False, False, "Excel 8.0")

' Retrieve the recordset

Set rs = db.OpenRecordset("SELECT * FROM `Contacts`")

' Determine the number of retrieved records

With rs

.MoveLast

i = .RecordCount

.MoveFirst

End With

' Set the number of Columns = number of Fields in recordset

cmbContacts.ColumnCount = rs.Fields.Count

' Load the Contacts combobox with the retrieved records

cmbContacts.Column = rs.GetRows(i - 1)

' Cleanup

rs.Close

db.Close

Set rs = Nothing

Set db = Nothing



The reason that I am not loading the last record in the record set is that
in the Excel spreadsheet, that row should contain a message that if
additional records are to be added, they must be inserted a row that you
insert before what was the last row of the named range, so that the new
record would automatically be included in the named range and thus be loaded
into the combobox.



To get the data from the individual cells of the selected item, you use the
..BoundColumn property of the combobox. Assuming that you had a list of
contacts in the spreadsheet with data in the following columns:



FirstName

LastName

Street

City

State

Zip



and you use inserted the following { DOCVARIABLE } fields in the template in
the places where you wanted the data to appear



{ DOCVARIABLE varFirstName }

{ DOCVARIABLE varLastName }

{ DOCVARIABLE varStreet }

{ DOCVARIABLE varCity }

{ DOCVARIABLE varState }

{ DOCVARIABLE varZip }



You would use the following in the Command Button Click Event



With ActiveDocument

cmbContacts.BoundColumn = 1

.Variables("varFirstName").Value = cmbContacts.Value

cmbContacts.BoundColumn = 2

.Variables("varLastName").Value = cmbContacts.Value

cmbContacts.BoundColumn = 3

.Variables("varStreet").Value = cmbContacts.Value

cmbContacts.BoundColumn = 4

.Variables("varCity").Value = cmbContacts.Value

cmbContacts.BoundColumn = 5

.Variables("varState").Value = cmbContacts.Value

cmbContacts.BoundColumn = 6

.Variables("varZip").Value = cmbContacts.Value

.Fields.Update

End With


--
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

 

[expressplanweb2]

Doug:

Thanks for your response and the time you put into it. I've done my
share of VB and VB.NET programming, although it's been about six years
since I did so professionally. I never really did VBA programming in
Office, however, so it was good to be reminded that I had access to
forms, buttons, etc.

My users are looking at web sites for companies that may be interested
in our services. When they find one, they send a personalized email
(or web form fill-in) to the company in question. They also record the
name, phone number, and email if available, along with some other data
on a line in an Excel spreadsheet as a record of what they did. This
sheet also serves as a follow-up record for salespeople to use. They
then go looking for another appropriate company and repeat the
process.

What does the user do now? They hand fill in the line on the Excel
sheet from what they see on the web, and then copy and paste the
individual data points onto the MS Word letter, and do a Select All
and Copy on that letter. Then, based on whether there is an
appropriate contact form on the particular web site they are working
on, they either paste that customized letter into either the form or
into the body field of a web-based email application such as gMail.
The big time waster is copying and pasting all the data points from
the spreadsheet line into the MS Word document one by one.

After looking at what you produced, I decided to change my design to
something much simpler than my first cumbersome attempt. In short, I
now want the user to plug the values into the UserForm first, and then
have the Userform send the data directly to the Merge Document as well
as a new line on the spreadsheet.
Based on that, where should the UserForm "live", the Excel sheet or
the Merge Document? How does the VBA UserForm access both the Excel
sheet and also the Merge Document? The idea is that this needs to be
much quicker than the laborious process they currently use.

Again, Doug, thanks again for your help and I hope this thread helps
some future reader solve a similar problem.

 

[Doug Robbins - Word MVP]

See Part 5 of the series of articles on fellow MVP Dian Chapman's website
at:

http://www.mousetrax.com/techpage.html#autoforms

--
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