how to get multiple clipboard items from ms word and replace bookmarks

[rich99730]

Hi.
The following code will take only one item from the clipboard and paste it into each bookmark. I am trying to get 3 different items that are w/in the clipboard and replace the bookmarks with the clipboard items. For example I need the address from the clipboard to replace the address field. I need the auto_num from the clip board to replace the auto_num bookmark and finally I need the price from the clipboard to replace the amt_due bookmark. The FillBookmarks function replaces the bookmarks w/ in the document because for some reason and with all the research I have done once you run this macro, the bookmarks are removed.

So to reiterate my problem again if not clear.
1.) I have only 3 item stored on the clipboard and each item will replace each bookmark.
2.) The following code works fine, but only uses the last item copied or selected from the clipboard and replaces all three bookmark references.

Hope that is clear enough and any help would be great.

Thank you.
Rich


Sub cpyclpbrd()
Selection.GoTo What:=wdGoToBookmark, Name:="address"
With ActiveDocument.Bookmarks
.DefaultSorting = wdSortByName
.ShowHidden = False
End With
Selection.Paste
Selection.GoTo What:=wdGoToBookmark, Name:="auto_num"
With ActiveDocument.Bookmarks
.DefaultSorting = wdSortByName
.ShowHidden = False
End With
Selection.Paste
Selection.GoTo What:=wdGoToBookmark, Name:="amt_due"
With ActiveDocument.Bookmarks
.DefaultSorting = wdSortByName
.ShowHidden = False
End With
Selection.Paste
FillBookmark wdDoc, sAddress, "address"
FillBookmark wdDoc, lLoanNum, "auto_num", "0"
FillBookmark wdDoc, lPastDue, "amt_due", "0"
ActiveDocument.PrintOut
End Sub

Sub FillBookmark(ByRef wdDoc As Object, _
ByVal vValue As Variant, _
ByVal sBmName As String, _
Optional sFormat As String)
Dim wdRng As Object
Set wdRng = wdDoc.Bookmarks(sBmName).Range
If Len(sFormat) = 0 Then
wdRng.Text = vValue
Else
wdRng.Text = Format(vValue, sFormat)
End If
wdRng.Bookmarks.Add sBmName, wdRng
End Sub

 

[TonyJollans]

I'm afraid you can't do it - unless you are running Word 2000; later versions do not have any programmatic access to the Office clipboard. I suppose it might be possible with some complex SendKeys but I've never tried and suspect the possible scenarios too numerous to make it viable.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[fumei]

How do you even have multiple separate items in the clipboard?

faq219-2884

Gerry
My paintings and sculpture

 

[rich99730]

Tony,
Thanks. How about if each time I copy an item can that be stored in a variable and that variable used later? Do you think that would work?? Sounds logical enough since, as stated above the code does use the last copied item....

Fumei, I assume you are saying that multiple means seperate? Good catch. If not then each copy from another application stores each item as a seperate instance on the clipboard. Therefore, if I copied 3 different times then I would have 3 different items.

Thanks for the replies and are very appericiated.
Rich

 

[TonyJollans]

Yes, Rich, you can do that but if you do all your copying and pasting in the same macro do you actually need to save what you have copied? Can you not paste it immediately?

If you do want to save the copied items, it is relatively easy if they're just text but rather more involved if you want formatting as well. What is the source of the data? If another Word document then things are easier.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[rich99730]

Hi Tony,
Running word 2003 by the way and please let me clarify. So here is the data I have, and it is plain txt but not from another word doc. It is from another application that will only allow me to copy the information which is then retained on the clipboard, and here is the data.
1.) a name/address
2.) a payment
3.) a "number" that is associated with the name/address

I am writing letters and these are the only 3 pieces of information I change each and every time for a 100 or so letters. I guess why I mentioned storing them in a variable was because you stated there was no way to access the clipboard. If at the end of the macro i clear the clipboard and then when i copy the information for the new customer (ie. the name/address, payment, the number) then each time it will be stored in the same variable and applied to the specific bookmark.

The following is an example letter and each bkmrk need to be replaced each time with a different customers information. Hope that makes more sense.






John Smith
221 Blame Lane <--- This is a bookmark
City, CA 99999 address
RE: Loan Number 9999999999 <---- This is a bookmark
auto_num
Dear Customer:

It has come to our attention that your payment is past due in the amount of $ 249.99 ... <---- This is a bookmark
amt_due

 

[TonyJollans]

Sounds like it should be a mail merge.

How do you copy from the other application? In code, or manually?

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[rich99730]

Tony,
I am so sorry just when i think I have all questions answered you throw another logical explanation at me However, I wish it were that easy "mail merge" it is all manual. Corporate bologna. I am building my work around and it is all manual. In the other app. they have the CNTRL-C disabled and the clipboard disabled. although I can use the copy button and that is it, so that is what I do, copy the 3 pieces of data to the word clipboard and then use that to replace each bookmark. Kinda stupid I think but say-la-v

Thanks for all the insight and patience.
Rich

 

[macropod]

Hi Rich,

Can the crippled coporate system you're using at least output the data to a delimited text file? If so, Word could do a mailmerge using that as the data source.

Cheers

[MS MVP - Word]

 

[rich99730]

Hi Macropod,

Yes they could and I just started the job and have already asked and the hoops one must jump through are blazing. hopefully that will come soon enough. In the mean time, that is not an option. Any other ideas would be much appreciated.

Thanks
Rich

 

[TonyJollans]

I am not sure I fully understand what you can put on the clipboard. To begin with you said that your three pastes all pasted the last of the three items (the amount?). Does this mean that you are doing three separate copies from the app (which get trapped by the Office clipboard) and then want to do three pastes within Word? If that's the case, any automation you can do in Word is going to be very limited.

Is there really nothing else you can do - for example something along the lines macropod suggests? Your app must have have some facilities beyond a simple copy - a report you could feed into another process perhaps.


Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[rich99730]

Hi Tony,
I am going to answer each sentence.

what you can put on the clipboard?

I can copy(put) any info from the app., and the clipboard will capture it.

your three pastes all pasted the last of the three items (the amount?)

The code above takes whatever I last copied to the clipboard and pastes that, (whether it is the amount, address, or number)in each bookmark postion.

Does this mean that you are doing three separate copies from the app (which get trapped by the Office clipboard) and then want to do three pastes within Word?

Yes I am doing 3 separate copies from the app., which get trapped by the clipboard. And then yes I want what is on the clipboard, the address, amount, and number to basically be pasted into each bookmark position as noted above.

Is there really nothing else you can do?

Yes, but i could lose my job. I do not work for the IT/RiskManagement division and they will not allow certian information to be output to a file. (no clue why when I can copy the same information anyway, makes no sense). However, I am in the process of working through the corp., ladder right now to get that info, added to our output file.

In the mean time I was looking for a quicker way to output the 100 or so letters I need to write every day. Because currently I have to take each of the 3 items on the clipboard and paste them in the (letter template).

Thank you,
Rich

 

[TonyJollans]

I understand your frustration but I don't really think there's much you can automate from Word.

In theory you could trap the clipboard activity yourself but it's non-trivial programming - it might be worth looking for a different tool that could help - a quick Google turned up clip-magic and clipboardgenie; I didn't study them in depth but they're probably worth a look.

Sorry not to be more help.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[rich99730]

Hi Tony,
Thank you so much. for all your insight. If I ever figure something out I will update this post. and thanks again.
Sincerely,
Rich