Printed Directory Instructions

This page contains instructions on how to create a file used to print the annual DoubleGate directory. These instructions use Microsoft Word and Excel for Windows. Create a folder called DoubleGateDirectory on the root of your C: drive to hold the files you will create and download.
Begin by exporting the resident names, phone numbers, etc. from the website to a Microsoft Excel file and use Excel to sort the data. To prevent web page load timeouts, the data is exported in two alphabetical groups, A through M and N through Z.

x

  1. Open the following web page (wait for the page to load) https://doublegate.net/directory-mail-merge-file-a-m/ (make sure that the year shown on this page is current, if not the member/non-member information will not be correct). If the incorrect year is shown, contact Ron to update the year.
  2. Copy all of the data that appears beginning with “.Last_Name” and ending with the last tilde character “~” (the period is needed to keep the column headers at the top of the Excel worksheet when it is sorted in a later step)
  3. Open a new blank Excel workbook
  4. Paste the copied data into cell A1
  5. Open the following web page (wait for the page to load) https://doublegate.net/directory-mail-merge-file-n-z/
  6. Copy all of the data that appears beginning with the first resident’s last name and ending with the last character. If the last resident shown on tis page has children, the last character will be a tilde “~”. If the last resident shown on the page has no children, the last character will be a carat "^".
  7. Paste the copied data into column A just after the first set of data that was pasted (there should be no blank rows)
  8. Select column A by clicking on the “A” column header
  9. Select the “Data” tab
  10. Click “Text to Columns”
  11. Select “Delimited” then click “Next”
  12. Check the “Other:” checkbox, remove checks from any other delimiter
  13. Enter the carat symbol “^” next to “Other:” and click “Next”
  14. Select “Column data format” to “General”
  15. Set “Destination” to $A$1 and click “Finish”
  16. The data should now be separated into columns
  17. Right click on a cell in column A and select “Sort” then select “Sort A-Z”
  18. The data should now be sorted alphabetically with the column headers still in first row with “.Last_Name” in cell A1.
  19. Delete the period in cell A1 (i.e. change “.Last_Name” to “Last_Name”)
  20. Save the workbook to a folder on your C: drive called C:\DoubleGateDirectory

Next, use the mail merge feature of Microsoft Excel to convert the Excel file created above to a printable directory. The software used on the website inserts extra commas and tilde characters after the names of the children. These will be removed manually after the merge is complete.

  1. Download the “DoubleGate Directory Mail Merge.docx” file by clicking HERE
  2. Open the Microsoft Word mail merge file
  3. If “PROTECTED VIEW” is displayed, click “Enable Editing”
  4. A window will appear indicating “Data from your database will be placed in the document. Do you want to continue?”. Click “No”
  5. Save the file to a folder on your C: drive called C:\DoubleGateDirectory
  6. Select the “Mailings” tab
  7. Click the down arrow next to “Start Mail Merge” to display the mail merge options
  8. Select “Step-by-Step Mail Merge Wizard…”
  9. Click “Previous” at the bottom right until “Step 1 of 6” is shown
  10. Set “What type of document are you working on?” to “Directory”
  11. Select “Next: Starting document” in the bottom right
  12. Select “Use the current document”
  13. Select “Next: Select recipients”
  14. Select “Use an existing list” and click “Browse…”
  15. Browse to C:\DoubleGateDirectory folder and open the Excel file you created earlier
  16. Verify “First row of data contains column headers” is checked then click “OK”
  17. On the “Mail Merge Recipients” window, click “Next”
  18. Select “Next: Arrange your directory”
  19. Select “Next: Preview your directory”
  20. Select “Next: Complete the merge”
  21. Select “To New Document…”
  22. Set “Merge Records” to “All” and click “OK”
  23. Wait for the merge to complete
  24. Scroll to the top of the document and click on the first resident’s last name
  25. Select the “Home” tab and click “Replace”
  26. Set “Find what:” to “, ~” (comma space tilde)
  27. Delete anything that may appear next to “Replace with:” and click “Replace All”
  28. The commas and tildes after the children’s name should not be gone
  29. With the Replace windows till open, set “Find what:” to “~” (tilde)
  30. Delete anything that may appear next to “Replace with:” and click “Replace All”
  31. Close the “Find and Replace” dialog box. All remaining tildes should be gone
  32. Scroll through the document to verify that formatting is correct. Some long lines of text may wrap in such a way that the remainder of the text disappears. Force the missing text to the next line by placing the cursor between the text that you can see and the missing text and press the key. The easiest way to spot a problem is to select the “View” tab then select “Multiple Pages”. You may want to go through the excel spreadsheet to identify several of the longest text strings in each of the fields and double check those records (people with long names, long email addresses, homeowners with different last names, and families with more than 4 kids).
  33. Insert page breaks where alphabetical tabs will be inserted and make sure each new tab starts on an odd number page. For example, if the first tab is for “A-B” and the second tab is for “C-D”, place your cursor after the last resident whose name begins with “B” and press < Ctrl >< Enter >. This will force the first resident whose name begins with “C” to appear on a new page. If “C” starts on an even number page, press again to make it an odd number page. If you made a mistake, press < Ctrl >< Z > to undo and try again. The tabs are as follows:
    • AB
    • CD
    • EF
    • GHI
    • JKL
    • MNO
    • PQR
    • ST
    • UVW
    • XYZ

Generate the list of residents by street name and insert it at the bottom of the Word document.

  1. Open the following web page https://doublegate.net/resident-directory-by-street/
  2. Copy all of the data that appears beginning with “DoubleGate Residents – by Street” and ending with the last resident’s name
  3. Open the Word document that was created above
  4. Scroll to the very bottom of the document
  5. Press < Ctrl >< Enter > to insert a new blank page at the end. If necessary, do this again to start the list on an odd number page.
  6. Paste the data on the new blank page
  7. Highlight all of the pasted data
  8. Change the font to Arial 9pt to match the alphabetical directory

Important!

Make sure that the first page of each alphabetical section and the first page of the street directory begin on an ODD numbered page to avoid printing where the 3 holes are punched. Add a blank page if needed.
Insert the cover page, table of contents, HOA board members page, Swim & Tennis Club board members page and DoubleGate map pages. You can copy this from the previous year’s directory and update the board member names.

Add page numbering.

Most likely the company that will print the directories will want a PDF file so save your final file as a PDF using File->Save As.

Printing:

  • Before printing, double check spacing on all pages and each section begins with odd number page.
  • If a single record is on a page, can you squeeze it in on the previous page? The cost of printing is based on the number of pages.
  • Printing requirements:
  • 5.5” x 8.5”
  • 3 hole punched
  • Blue slip sheets inserted between tabs (may need to provide printer the page numbers where the slip sheets are to be inserted)
  • Black and White printing
  • Shrink wrapped
  • Request a proof to make sure everything looks good and lines up correctly