1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Vlookup in Word, continued

Discussion in 'Business Applications' started by Yorkshire Guy, Apr 7, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. Yorkshire Guy

    Yorkshire Guy Thread Starter

    Joined:
    Dec 9, 2003
    Messages:
    563
    Hi Dreamboat and XLGuru.

    A while ago I posted a request for help with getting addresses into Word that were held in an Excel s/s. (s/s is a list of names and addresses, 1 address per row, coulumns = address lines.
    (WinXP and OfficeXP).

    You both gave me some approaches but I've been chasing some other problems so have only gotten back around to this just now.

    This may now be of use to others as I think I have a working solution!

    Mail Merge in Office XP isn't immediately as user friendly as in t'old days but Dreamboat's stuff removes some of the mysticism.

    Anyway, this appears to work.

    1. Set up a Word Template that is a Mail Merge document using the Excel s/s as source.
    Enter the name and address lines as merge fields (what a pain in XP as the Address Block doesn't suit UK style addresses!!).

    2. When you want to write a new letter and get the required address from the s/s:

    A. File / New and select the above template as the document basis.

    B. With the Mail Merge toolbar visible, click on the binoculars to open the 'Find Entry' window.

    C. Type in the name or membership no or key that you use to select an address row from your s/s. and CANCEL.

    d. ??? this doesn't look to change the open document at all, nor does it look as if anything has happened, however:

    e. Now press the View Merged Data buitton on the Mail Mege Tioolbar (<<ABC>>),

    and dah dah...

    f. The required name and address magically appears in a new document and off you go to edit / save / print it etc.

    The only 'problem' I have is that if you open WORD and are doing the above and then also want to open the s/s then Excel tells you that the s/s is locked for editing by someone else , but it works fine if the s/s was already open when you then secondly start the merge.

    I'm now looking at a nice macro to do the above steps from one button press, but it seems to halt on the Find Entry window.
     
  2. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    Hi again HEWANM. :)

    >> Vlookup in Word, continued

    The best way to continue is to post an update in the original thread, else it all gets a bit messy. ;)

    I may have suggested before relocating your data to a table in a Word file. You may have said that you needed to keep it in Excel. If you could at all move it to Word you'd solve your "locked for editing" issue.

    As I may have said, you can set up your s/s so that one cell has a (data validation) dropdown from which you select a name or ID#, & the act of selecting copies that name (or ID#)'s details (with the help of a few formulas) to the clipboard as a vertical array.

    From there, you just ALT+TAB to Word and Paste Special as Unformatted Text.

    That said, don't take me up on this until DB has looked at your Word issues. If they can be addressed, she'll tell you how.

    (The book kinda stands out on the shelf at the comp store, Anne).

    Rgds,
    Andy
     
  3. Yorkshire Guy

    Yorkshire Guy Thread Starter

    Joined:
    Dec 9, 2003
    Messages:
    563
    Thanks XLGuru, I did play with selecting and pasting the name and address from Excel direct into Word, including formatting the address lines to remove blank ones etc, then I saw the Find Entry in the new Office XP Mail Merge (I was on Office2K before) and thought this simpler.
    The locking issue isn't really much of a problem since I generally have the spreadsheet open anyway.
    I don't want to make Word the primary source since the NAme and Address s/s is part of a workbook with lots of other sheets.

    All that would be needed to make this a really neat solution would be the macro to automate opening the Find Entry, not proceeding until a find item had been specified, then merging that result into a new doc - I'm still playing with that.

    thanks again Andy.

    BR
    Michael
     
  4. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> All that would be needed ... would be ...
    >> to automate opening the Find Entry

    No idea if it's the same thing/method as Word 2K ; try

    Dialogs(wdDialogMailMergeFindRecord).Show

    HTH,
    Andy
     
  5. Yorkshire Guy

    Yorkshire Guy Thread Starter

    Joined:
    Dec 9, 2003
    Messages:
    563
    Andy,

    you're a wizz, this works a treat:

    Dialogs(wdDialogMailMergeFindRecord).Show
    ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle

    I was using:
    WordBasic.MailMergeFindEntry
    ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle

    This is similar.

    Do you know how I could condition the toggle to view the searcgh results?
    what is happening is If I search for say 'Smith' , the above displays the N&A for the first Smith (I can't see this until I view the merge as from second line of the macro.
    If that's the wrong Smith and I repeat the macro, this time it toggles back to the main doc and I don't see the result.
    So what I want is the macro line 2 to only run when I'm on the main doc, and not run when I'm already on the merged doc.

    I've got to go now , if you have any suggestions I will follow up tomorrow.

    thanks

    Michael
     
  6. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> macro line 2 to only run when I'm on the main doc

    Yes, I know where you're coming from. I struck out on Google. Plus it's hard for me to test, suitable data-wise. Plus I've always found the ABC button to be a tad klunky (might be just mine).

    What I came up with (a) was about as brute force as it gets (b) produced weird results at times. Notwithstanding, see how (if, more likely) it works for you.

    Rgds,
    Andy

    ActiveDocument.MailMerge.DataSource.ActiveRecord = wdFirstRecord
    If ActiveDocument.MailMerge.ViewMailMergeFieldCodes = True Then
    ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
    Dialogs(wdDialogMailMergeFindRecord).Show
    Else
    Dialogs(wdDialogMailMergeFindRecord).Show
    End If
     
  7. Yorkshire Guy

    Yorkshire Guy Thread Starter

    Joined:
    Dec 9, 2003
    Messages:
    563
    Andy,

    tried your macro, however the if toggle / don't toggle doesn't work for me.

    thinking sideways, the reason to toggle is to see the results of the Find Entry (because Find Next) doesn't show the results).

    if instead I use the 'Mail Merge Receipients' button from the Toolbar, this not only shows me the Source list, and when you use 'Find' from there, positions you in the list at the result. So, if this isn't the one you want you use 'Find' again.

    Then when you OK that to return to the main doc and press the View Merged Data button, all is OK.

    I can't make all that into a new Macro, don't know what the macro line is for 'Mail Merge Receipients' ?

    Anyway, maybe this is as good as it gets!

    Thanks for all your help.
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/217963

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice