Copying/Pasting Word data into Excel with Macros

[pvsmith5]

I have a 300-page word document with some data that I have to use a macro in Word to copy and then am trying to paste into Excel. I got this to work with one copy/paste, but how do I send the focus back to the Word document, and run another macro in Word, then copy/paste back to Excel?

Here's what the text looks like, and I need to copy/paste the name, then space over and get the next number, then one of the dates, and then get the classes that have the codes 7500 and 8102, and then get the name associated with that code number.

DOE, JOHN A 9999999999 4/23/2008 8/23/2006 5/28/1992 F
412 E 18TH ST.

ANYWHERE MO 64683- 10



A 1 7500 LA 1 1ST 1 1 EV RM 21 LANG ASIEBENBORN, S.
A 2 7645 HEALTH 1ST 3 1 EV RM 201 TMS HOFFMAN, J
A 3 7485 PHY.SCIENCE 1ST 3 1 EV RM 25 SCIENCSTARK, A.
A 4 7885 AG SCI 1 1ST 3 1 EV VO-AG ROGERS, S.
A 5 7931 CITIZ & GOVT 1ST 9 1 EV RM 12 SOC STBEAVERS, C.
A 6 8102 GEOM 1ST 5 1 EV RM 15 MATH CLOUGH, R.
A 7 7691 THS SINGERS 1ST 1 1 EV VOCAL MUSIC ROBBINS E.
A 8 7752 HOMEROOM 1ST 1 1 EV RM 10 SOC STTIPTON, M.
A 1 7512 LA 1 2ND 2 1 EV RM 21 LANG ASIEBENBORN, S.
A 2 7650 LIFE SPORTS 2ND 2 1 EV GYM BEST, L.
A 3 7487 PHY.SCIENCE 2ND 4 1 EV RM 25 SCIENCSTARK, A.
A 4 7886 AG SCI 1 2ND 4 1 EV VO-AG ROGERS, S.
A 5 8024 CITIZ & GOVT 2ND 10 1 EV RM 12 SOC STBEAVERS, C.
A 6 8103 GEOM 2ND 6 1 EV RM 15 MATH CLOUGH, R.
A 7 7880 THS SINGERS 2ND 2 1 EV VOCAL MUSIC ROBBINS E.
A 8 8052 HOMEROOM 2ND 1 1 EV RM 10 SOC STTIPTON, M.


--------------------
Here's the code that I have so far, in a macro that runs out of Excel 2007 and opens Word 2007.

Sub Macro1()
'
Dim oBook As Object
Dim lNextRow As Long
Set appWd = CreateObject("Word.Application")
appWd.Visible = True
Set oDOC = appWd.documents.Open("C:\dump\HS_sched.docx")

Set Focus = oDOC
Dim WordObj As Object

Set WordObj = CreateObject("Word.Basic")

With WordObj
.ToolsMacro Name:="Macro2", Run:=True
End With

Set WordObj = Nothing

ActiveSheet.Paste
ActiveCell.Offset(0, 1).Range("A1").Select


End Sub
-------

So how do I get the Excel macro to go back and forth from Word with the copy/pasting?

 

[SkipVought]

Geez, and he's been around since 2004???

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[pvsmith5]

Guys, I posted on here to try to get help and haven't received much. In fact, I think your comments could be a bit more constructive than so critical and questioning.

At least I posted the final code to try to help out somebody else who might have the same issue. I would hope that would be your same intent.

 

[SkipVought]

"I posted on here to try to get help and haven't received much"

And you have not been very forthcoming with the information requested. You apparently knew enough to arrive at a solution.

"...and has no columns that can be parsed..."

I actually did parse SOME of the data you posted, quite easily. If you would have posted your SECOND example with TGML markup tags, it would have been obvious that there are parsable portions.



Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[fumei]

And I also:

1. parsed most of it quite easily in Word using Range (which I mentioned and you never even bothered to comment or ask any question whatsoever;

2. used ONE Word macro to do it...as I also mentioned was possible...but you never commented or asked any questions about.

So, shrug...what can I say? Thank you (and I mean that sincerely) for posting your "final" code. That is a good thing to do. Although I most certainly would NOT recommmend anyone use it who has the same issue. Why? Because as I tried a few times to get across...it can be done better, faster, easier.

"questioning"???????

Yes absolutely. It is one of the ways people who answer questions improve and learn. The problem Skip and I are having - and yes, I can see how you may take it as critical - is you do not answer well, if at all.

"Guys, I posted on here to try to get help and haven't received much"

That is hooey. If you feel you have not received much help then you are simply not paying attention. Both Skip and I have tried.

Skip is one of the top Excel people here. Look at the MVP list. The man has over 1000 stars. Why? Because he has helped literally thousands of people and is extremely knowledgable. If there is one person to cultivate, especially in regards to Excel, it is Skip. If you feel there is a failure to get help, I am sorry to say that the failure is on your part. Not Skip.

I am not going to blow an ego horn regarding my knowledge of Word, as there are quite a number of people here much better than I am. However, I do know a fair bit, and I will repeat my starting points.

1. I parsed most of it quite easily in Word using Range (which I mentioned and you never even bothered to comment or ask any question whatsoever;

2. I used ONE Word macro to do it...as I also mentioned was possible...but you never commented or asked any questions about.

In fact, as I check back through this...you did not respond to, or answer anything I posted. So, while it is unfortunate that you feel miffed (or whatever), I do not have much in the way of sympathy.

faq219-2884

Gerry
My paintings and sculpture

 

[fumei]

Since 2004 indeed. And you still have not figured out how to use the code tags when you post code.

Hmmmm.

faq219-2884

Gerry
My paintings and sculpture