Copy & Paste Between MS Word and VBA

[knowemptines]

Version of Program: Microsoft Office Professional 2010

What you want it to do: Automatically copy text from document 1 and, then, paste it into specific fields of VBA.


In MS Word, for example, a term (i.e., apple) is on the first line while its definition is on the second line.

Line 1: Apple

Line 2: The round fruit of a tree of the rose family, which typically has thin red or green skin and crisp flesh.


I want the macro to take the word apple and place it between the quotes of .Text = "" in VBA.

As for line 2, cut and paste the definition into the quotes of .Replacement.Text = "".

Thus the entire statement should read: .Replacement.Text = "apple -- The round fruit of a tree of the rose family, which typically has thin red or green skin and crisp flesh." (The term apple as well as the hyphens need to be there too.)

I need to do this for 4,100 words, and I don't want to spend my time hitting CTRL+c and CTRL+v. Terms will only be a few characters, but their definitions will vary in length. Here's an example in VBA:

.Text = "Apple"
.Replacement.Text = "apple -- The round fruit of a tree of the rose family, which typically has thin red or green skin and crisp flesh."

.Text = "Cow"
.Replacement.Text = " Cow -- Cause (someone) to submit to one's wishes by intimidation; or a fully grown female animal of a domesticated breed of ox, used as a source of milk or beef."

How do I go about accomplishing this task? I thought about a Find & Replace solution; that is, find instances of .Text = "" and .Replacement.Text = "" and, then, have the macro paste each term and definition into a vacant slot.

But how do I accomplish this in MS Word when the terms and definitions can only be separated by a numbered list? In groups of two? 1st term, 2nd definition; 3rd term, 4th definition.

I'd appreciate your help.

[gmaxey]

It hasn 't been tested on 4,100 pairs, and probably wouldn't work, but look at: http://gregmaxey.mvps.org/word_tip_pages/vba_find_and_replace.html

You might have to break your list of pairs into more manageable chunks, say 50 or so.

[Frosty]

I think this is a really really bad idea. Why would you want to hard code 4100 different definitions in to a vba code module?

Why don't you set up some sort of a database (whether it is a word table, excel spreadsheet, text file, csv file, etc etc) with your term and your definition?

From there, all you're really looking to do is loop through your "data" and perform a lot of find/replace functions on an actual document, yes?

If you can describe your process better, you could get some better help-- but I think what you're doing is exactly the wrong way to approach what I suspect is your actual problem (your instinct to have a coding solution rather than a manual copy/paste is a good instinct -- keep following it).

Alternatively ... you could just copy your VBA code modules into a word document, and do the same thing... but either way, at the end of the day... you need to loop through 4100 terms and their definitions for some purpose. I would highly advocate avoiding any coding structure which isn't utilizing a data table of some sort, but rather performing the same action 4100 times with minor textual differences).

The only thing "difficult" about your task is the number of times you want to do it. Fortunately, computers are really good at doing repetitive tasks 1000s of times.

[fumei]

On the OTHER hand, if there is not a data issue at all, there could be an easy solution.

Say it is a paragraph issue.

Apple
The round fruit of a tree of the rose family, which typically has thin red or green skin and crisp flesh.
Cow
Cause (someone) to submit to one's wishes by intimidation; or a fully grown female animal of a domesticated breed of ox, used as a source of milk or beef.

Now IF these are separate paragraphs, with NO "extra" paragaphs, then it is relatively easy - just using paragraph objects - to change it to:

Apple--The round fruit of a tree of the rose family, which typically has thin red or green skin and crisp flesh.
Cow--Cause (someone) to submit to one's wishes by intimidation; or a fully grown female animal of a domesticated breed of ox, used as a source of milk or beef.

In other words, no use of "apple" or "cow" as data points.

Assuming "Apple" is paragraph(1) - or ODD, and "The round fruit of...." is paragraph(2) - or EVEN, then

For Each paragraph, if it is ODD in the index;
take the range and strip the paragraph mark;
add "--";
add the text of the NEXT paragraph (EVEN);
continue.

Now the ODD paragraph has "--" and the text of the next even paragraph.

working backwards, delete all the EVEN paragraphs.

Apple--The round fruit of a tree of the rose family, which typically has thin red or green skin and crisp flesh.
Cow--Cause (someone) to submit to one's wishes by intimidation; or a fully grown female animal of a domesticated breed of ox, used as a source of milk or beef.

I will admit that is highly unlikely that the OP's document is as neat and tidy as this would require. LOL.

[fumei]

Hmmm, re-read the original post...

But how do I accomplish this in MS Word when the terms and definitions can only be separated by a numbered list? In groups of two? 1st term, 2nd definition; 3rd term, 4th definition.

Whoa. We need much more in terms of details!

[knowemptines]

Gmaxey, Frosty, and Fumei,

Thank you.

Fumei, you and I came up with a similar solution for organizing the data. Kudos.

Gmaxey, after downloading the templates, I received a pop-up warning about the safety of the file being opened. And, then, when I clicked disable macro, a word document presented the disclaimer below:

"The tips and macros included in that file are provided 'as was' in good faith and free of charge. Use them at your own risk. I accept no responsibility for their use or consequences of that use."

Can this add-in be easily removed after installation? No character limitations? Would it be possible to use a "call" function to go onto the next 50 items? I'm optimistic about your solution.

Frosty, please correct me if I'm wrong, but your suggestion is similar to Gmaxey's VBA Find & Replace recommendation; that is, it allows "a user defined list of 'find' and 'replace' pairs," which is somewhat akin to a data table.

[knowemptines]

Oh, and another thing,

I have a question about the wildcard function in MS Word.

Here's an example of what I'm doing: apple * orange.

But I want to find these instances within sentences, not between paragraphs or from beginning to end.

I need to find this pattern: [Apples taste as good as oranges].

I don't need this pattern: [Apples are interesting. Oranges] are boring.

[fumei]

"Fumei, you and I came up with a similar solution for organizing the data. Kudos. "

Hmmmm, if this has to do with the paragraphs, then I disagree...there is no organization of data.

knowemptines, your in-sentences ONLY requirement is quite different from your original post (and nothing at all to do with the subject line). VERY different. Please try to keep one situation per thread.

[Frosty]

Your original question *seemed* to related to automating a process in order to build VBA code so that you can run a single routine to find/replace a bunch of terms in a document with those terms and their definitions.

My argument is that you are trying to automate the wrong process (i.e., you are trying to automate the process of writing code) rather than make the process of replacing 4100 terms in a document. This is what I'm addressing.

At this point, I think it would be best if you simple provided a single document which contains 5 of the terms and definitions you would want, as well as a before and after of the text in question (what text which contained your terms would look like before a macro is run on it, and what it would look like after the macro is run.

I disagree with trying to work out a process which automatically builds VBA code. If you do that, your VBA code is going to be very inefficient.