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]

Hi,

"...another macro in Word..."

What's the logic for determining the macro to run?

Your code does not indicate that you are doing ANY copy 'n' paste. Please supply.

What is the logic for subsequent c & p?

Please post these answers, and then we can address the iteraitve process.

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

 

[pvsmith5]

You are exactly right. I had forgotten to show how I was asking the Word macros to copy from the Word document. Since I want the Name, I'm doing a search on the "," and then spacing over with the select to pick up the dates.

Thank you for your help.


Sub Macro2()
'

Dim sData As String
Selection.Find.ClearFormatting
With Selection.Find
.Text = ","
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.MoveUp Unit:=wdLine, Count:=1
Selection.MoveDown Unit:=wdLine, Count:=1
Selection.MoveUp Unit:=wdLine, Count:=1
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.MoveRight Unit:=wdCharacter, Count:=60, Extend:=wdExtend
Selection.Copy

sData = Selection

End Sub

Sub Macro1()
'
Selection.MoveRight Unit:=wdCharacter, Count:=60, Extend:=wdExtend
End Sub

 

[SkipVought]

Well you only answered one question; that your macro is copying.

Only ONE macro?

You have given absolutely NO information regarding pasting. Please answer ALL the questions that were posted.

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

 

[pvsmith5]

Actually, there are two macros that I showed. In all there will be probably a dozen. But I thought two would show enough of what I needed. After the Word macro runs to copy in the Word document, I have the pasting in the Excel macro, where I assumed that it would work best for the Excel worksheet. Then after pasting, the Excel macro needs to go back to the Word document, run the next macro and come back and paste the clipboard contents of that macro selection.

The Excel macro runs when I run it--no other logic to launch it. And if I need to keep resetting it in each Word block or moving it down a row at a time, I'll do it--a loop would be nice, but I won't mind the manual headache.

Hope that answers more clearly what needs to happen here. Thanks for your help.

 

[SkipVought]

So you must be clear about how you will go about choosing which macro to run during each loop iteration, correct?

So when you paste in Excel, do you get one row or a fixed number of rows or a variable number of rows pasted?

Where is the paste going in Excel?

This is the first time hat I took time to look at your data example. It seems that the spacing is wierd. Please repost again, this time, using Process TGML markup, the tt code should work. (Search this web page for Process TGML

I might suggest a different copy and paste process, that would COPY one or more LINES of text in Word and then after the Paste in Excel, use Data > Text to columns... to parse into columns. It seems to me that it might greatly simplify.

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

 

[pvsmith5]

Here is the entire part of the actual record. Yes, the spacing is weird and that is the problem. If I could simply do an import and put all the data into columns, I would. But there is nothing about this data that permits easy parsing.

That is why I need a set of macros in Excel and Word that can do the following:

Open in Excel in a blank worksheet. Then a Word macro for selecting in this example, DOE, JOHN A, searching for the comma since that is where the name can be found. The macro spaces back to select the entire name. Then the focus needs to go back to Excel, the A1 cell, and the name is pasted.

I have that working.

The next macro code would be written in Excel to fire the next Word macro to copy the next set of numbers, 999999999, and then that would be copied into the worksheet at cell A2.

The next macro code would be written in Excel to run a Word macro to copy the next date, 4/23/2008, and then this piece would be pasted in the worksheet cell A3. This continues for about 10 interations and then the end of the record would be signaled by some of the garbage in the next record or "2006-07". The Excel worksheet would be moved down a row and the next round of data would be pasted in cell B1.

So that's as complete as I can be at describing what I need done. Thanks for your help.

--------------------



E&a3L(s0p16.67h8.5v0T&l7.3C&k6.95H 2006-07


Jack AND Jill DOE (999)999-7908 (999)999-2247


JOYCE DOE - GRANDMOTHER (999)999-2331


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.

 

[SkipVought]

Did you search for Process TGML in this page???

It seems that the example that you pasted was not done with the TT tgml code.

Your COLUMNS do not line up. Are they real columns in Word?

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

 

[pvsmith5]

Yes, I saw the Process TGML. I did not want the example pasted with the TT tgml code because it needs to represent exactly the way the Word doc looks, which it does. And yes, the columns do not line up at all. That is exactly the way it looks in the original Word doc, which is the problem.

Skip, if you're willing to help with the problem that I've posted, or have any ideas on how to write the macro, I'm open. But what you see is what there is that I'm working with.

 

[SkipVought]

It seems to me that the version of data that you are working with is a copy of a copy at best.

Who generated the version that you have. Tabular data is supposed to line up in COLUMNS. SOMEONE has really messed up the process. I'd move heaven and earth to find a RELIABLE data source. I've been around the business world long enough (over 40 yesar) to know that copies of copies of copies happen, when the source, with a bit of research, is often attainable. Trace it BACK to the SOURCE.

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

 

[pvsmith5]

Yep. Did that too. This is a 1980s version of a DOS-database and this is the ONLY available electronic extract of data that I can get, as a TXT export.

Thus, that brings me back to trying to copy/paste pieces of the record into a spreadsheet. Do you have any ideas about how to jump from Excel to Word to try to get some usable data?

 

[SkipVought]

I still know nothing about the Excel sheet that is your destination.

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

 

[pvsmith5]

OK, Skip. You win. I give up. Sorry I posted.

 

[SkipVought]

OK,

SomeSheetObject.SomeRangeObject.PasteSpecial xlPasteValues

That's about as exact as I can get withour GUESSING at exactly what you have. Does that help?

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

 

[fumei]

You gave too easy.

1. WHY are you doing multiple macros?

"The next macro code would be written in Excel to fire the next Word macro to copy the next set of numbers, 999999999, and then that would be copied into the worksheet at cell A2."

I am trying to follow the logic. Both Word and Excel can handle multiple instructions.

2. Using selection is not good. Use a Range in Word. "The macro spaces back to select the entire name. " This is NOT required. You do NOT need to "space back".

3. WHERE - precisely - in that hodgepodge are the paragraph marks? This could be very important, and knowing that could make your search/extraction much easier.

4. You do NOT need to do that shifting back and forth in focus. "Then the focus needs to go back to Excel, the A1 cell, and the name is pasted. " Information can pass between the application, but actual focus does not need to. That is what automation is for!

5. You have some odd things in your code.

"Set Focus = oDOC"


"Set WordObj = CreateObject("Word.Basic")"


faq219-2884

Gerry
My paintings and sculpture

 

[pvsmith5]

Here's the code that actually works, in case anybody else would like to see it. What this does is from an Excel spreadsheet, it launches a Word document that has come from a print output and has no columns that can be parsed, with Word macros that select data, such as name, SSN, birthdate, and pastes each piece of data into an Excel spreadsheet row. When the Word macros hit the bottom of the loop, they go to the next part of the header row; the Excel macro creates a new row. It works exactly like it is supposed to.

Here is the Excel macro:
Sub Macro1()

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

Set Focus = oDOC
Dim WordObj As Object

Set WordObj = CreateObject("Word.Basic")

Dim wsS1 As Worksheet
Dim lRows As Long
Dim Iint As Integer

Set wsS1 = Worksheets("Sheet1")
lRows = wsS1.Range("B65536").End(xlUp).Row

For i = 1 To 279

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

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


ActiveCell.Range("A1").Select
ActiveSheet.Paste


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

ActiveCell.Range("B1").Select
ActiveSheet.Paste


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

ActiveCell.Range("C1").Select
ActiveSheet.Paste

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

ActiveCell.Range("D1").Select
ActiveSheet.Paste

ActiveCell.Offset(Selection.Rows.Count, 0).EntireRow.Insert
Rows(ActiveCell.Row).Offset(Selection.Rows.Count - 1, 0).Copy
Range("A" & ActiveCell.Offset(Selection.Rows.Count, 0).Row).PasteSpecial (xlPasteFormats)
Range("B" & ActiveCell.Row).Value = Range("B" & ActiveCell.Offset(-1, 0).Row).Value + 1
Application.CutCopyMode = False


Next i


End Sub

And here are the Word Macros that it calls:
Sub Macro1()
'
' Macro1 Macro
' Go to "&a3L"
'
Selection.Find.ClearFormatting
With Selection.Find
.Text = "&a3L"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
End Sub
Sub Macro2()
'
' Macro2 Macro
' Go to "," and get the Student's Name
'
Selection.Find.ClearFormatting
With Selection.Find
.Text = ","
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindAsk
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.MoveUp Unit:=wdLine, Count:=1
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.MoveRight Unit:=wdCharacter, Count:=60, Extend:=wdExtend
Selection.Copy
End Sub
Sub Macro3()
'
' Macro3 Macro
' Go over and copy the SSN
'
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.MoveRight Unit:=wdCharacter, Count:=10, Extend:=wdExtend
Selection.Copy
End Sub
Sub Macro4()
'
' Macro4 Macro
' Go over and get the graduation date
'
Selection.MoveRight Unit:=wdCharacter, Count:=5
Selection.MoveRight Unit:=wdCharacter, Count:=10, Extend:=wdExtend
Selection.Copy
End Sub
Sub Macro5()
'
' Macro5 Macro
' Get the birth date
'
Selection.MoveRight Unit:=wdCharacter, Count:=28
Selection.MoveRight Unit:=wdCharacter, Count:=9, Extend:=wdExtend
Selection.Copy
End Sub

 

[fumei]

If you could, could please answer my question?

What is this for?

Set Focus = oDOC

And again, using Selection is a poor way to action things in Word.

However, if you are happy with what you have, and it works...well, I guess that is that.

BTW: when posting code, please use the code tags.

faq219-2884

Gerry
My paintings and sculpture

 

[SkipVought]

E&a3L(s0p16.67h8.5v0T&l7.3C&k6.95H 2006-07


Jack AND Jill DOE (999)999-7908 (999)999-2247


JOYCE DOE - GRANDMOTHER (999)999-2331


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.
[tt]
E&a3L(s0p16.67h8.5v0T&l7.3C&k6.95H 2006-07


Jack AND Jill DOE (999)999-7908 (999)999-2247


JOYCE DOE - GRANDMOTHER (999)999-2331


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.
[/tt]


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

 

[SkipVought]

The above posting illustrates the DIFFERENCE between a careless PASTE and using TGML markup tags.

It seems that SOME of the data IS in columnar format, and that chunk...
[tt]
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.
[/tt]
could be parsed into columns quite easily using Data > Text to columns... via a FIXED WIDTH option. Turn on your macro recorder and record just that process.


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

 

[fumei]

Aaaaak! Wide post! Wide post!

I think you had better give up on this one Skip. It does not seem either of us are getting through.

faq219-2884

Gerry
My paintings and sculpture