Looking up excel range in word

[SaintBobCat]

Firstly, sorry, not strictly 100% an excel query......

I am (trying) to write a macro in word that when opened requests a name and if that name appears on a list (currently in excel) a particular paragraph is included and if the name isn't on the list to exclude the paragraph.

I'm really stuck so i need some advice as how to approach this???

 

[Macropod]

Hi SaintBobCat,

I assume you're trying to do this from Word.

What part are you stuck with - creating an Excel session, getting the list from Excel, testing the input against the list, inserting/excluding the paragraph?

Cheers

 

[SaintBobCat]

Yes, creating in word.

I want it all in one word document. There is a list of 200 names to check against. Is there a way to have a excel sheet liked and hidden in a word document? so i guess i'm stuck with the 'creating an excel session' part.

Cheers

 

[Macropod]

Hi SaintBobCat,

If you're processing all 200 records, perhaps a mailmerge would be the best route. With a mailmerge document, you can insert a field to perform an IF test in order to decide whether to include/exclude the paragraph.

Cheers

 

[SaintBobCat]

Thanks Macropod, but that's not pratical for this situation. If i explain fully....

I want to have word document that i am able to send out to people. They will then then be asked for a persons name upon opening the document. If that name is on a lookup list then a specific paragraph is included in the text and if not then it won't be. So i'm after a 'stand alone' document that doesn't have a datasource or a mailmerge.

So my first thought was if you are able to combine a word document with an excel sheet hidden and attached so that you can do the lookup on the excel sheet??

Cheers.

 

[Macropod]

Hi SaintBobCat,

Unfortunately, Word vba isn't much good at automating Excel to do anything useful with an embedded worksheet. However, there are other ways of skinning this cat. Whichever way you go, you'll need to use vba.

One approach would be to hard-code the list into your vba module. Unless someone gets access to the vba module (which you can password protect), there's no way to view the list.

Another approach would be to insert the list into a SET field in the document. SET fields create bookmarks which you can test the contents of via vba, but the contents of the field & bookmark aren't normally visible in the document - and the bookmarks themselves can be hidden by prefixing them with an underscore character. Embedding the list into the vba module is much more secure, however.

Whichever approach you take, it's then a simple matter of testing the list for a name match.

Also, rather than having the hidden paragraph embedded in a field in the document, where it could be viewed via Alt-F9 (the same weakness applies to the contents of SET fields), it'd be safer to simply have a bookmark where you want the paragraph to appear and, if the correct response is given to the username prompt, insert the text into the bookmark.

Cheers

 

[SaintBobCat]

Great thanks Macropod!

It looks like embedding the list in vba is the saftest/most pratical solution. Other that writing 200 if statments in vba is there a easier way to do this?

 

[Macropod]

Hi SaintBobCat,

A single Instr statement might be enough. For example, you could add code like the following to the document's 'ThisDocument' module:

Option Explicit
Dim BmkNm As String, BmkRng As Range, strTxt As String

Private Sub Document_Open()
Dim List As String, Client As String
List = ", Avard, Baum, Blewitt, Brown, Carmody, Cleary, Couchman, Day, "
List = List & "Driver, Fernandes, Gadzic, Goodchild, Hakaraia, Hebar, Horsham, "
List = List & "James, Kearns, Kristiansen, Leskien, Maher, McFarlane, Miller, "
List = List & "Murphy, O'Brien, Palmer, Playford, Rehak, Roulston, Shaw, Sood, "
List = List & "Summerrell, Tkachenko, Van Der Toorn, Wark, Williams, Yang,"
strTxt = "The quick brown fox jumps over the lazy dog" & vbCrLf
BmkNm = "MyBookmark"
Client = Trim(InputBox("What is your Surname?"))
Client = ", " & Client & ","
If InStr(1, List, Client, 0) = 0 Then strTxt = ""
If ActiveDocument.Bookmarks.Exists(BmkNm) Then
  Set BmkRng = ActiveDocument.Bookmarks(BmkNm).Range
  BmkRng.Text = strTxt
  ActiveDocument.Bookmarks.Add BmkNm, BmkRng
End If
Set BmkRng = Nothing
End Sub

This macro will run whenever the document is opened, prompting for the username. Only if the username is valid is the text inserted into the document, in a pre-positioned bookmark named 'MyBookmark'.

So that the excluded text doesn't remain in the body of the document for someone else to access by disabling macros next time the document is opened, you can insert the following additional code to the document's 'ThisDocument' module:

Private Sub Document_Close()
strTxt = ""
If ActiveDocument.Bookmarks.Exists(BmkNm) Then
  Set BmkRng = ActiveDocument.Bookmarks(BmkNm).Range
  BmkRng.Text = strTxt
  ActiveDocument.Bookmarks.Add BmkNm, BmkRng
End If
Set BmkRng = Nothing
End Sub

Cheers

 

[SaintBobCat]

This is absolutely brilliant! Exactly what I was after - thanks so much for taking time to look in to this.

Macropod, you're a legend!