Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Excel 2003 as DataSource problem
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 |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Excel 2003 as DataSource problem
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 wrote in message ... 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 |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Excel 2003 as DataSource problem
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. |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Excel 2003 as DataSource problem
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 wrote in message ... 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. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mail Merge and Excel Datasource | Mailmerge | |||
Mailmerge from Excel Datasource | Mailmerge | |||
Editing an Excel DataSource | Mailmerge | |||
Excel datasource | Mailmerge | |||
Problem with repeated access to the datasource (Excel Spreadsheet) | Mailmerge |