[Marla 5670]

I am trying to transform text outputted from an old legacy "roll and scroll"
database system to a table in Word. Search and replace are not adequate. I
need to do multiple searches for colons, copy text from one field to a table
in another document, return, search to the next colon, copy text, etc.

There are over 700 pages of this data. I would like to record one macro
action to copy one complete record, then add a loop to get it to repeat the
data extraction/copying for the rest of the records in the doc.

Do I simply add the loop statement to the end of the macro? The Repeat
Action command did not work. I just want to run the same macro over and over
from the current cursor location.

Any ideas are much appreciated!

[Marla 5670]

BTW, I used Do Until True...Loop--with bad results. Two more questions:
Is there a way to say Do Until End of File?
Also, is there a keystroke to stop a repeating macro?

"Marla 5670" wrote:

I am trying to transform text outputted from an old legacy "roll and scroll"
database system to a table in Word. Search and replace are not adequate. I
need to do multiple searches for colons, copy text from one field to a table
in another document, return, search to the next colon, copy text, etc.

There are over 700 pages of this data. I would like to record one macro
action to copy one complete record, then add a loop to get it to repeat the
data extraction/copying for the rest of the records in the doc.

Do I simply add the loop statement to the end of the macro? The Repeat
Action command did not work. I just want to run the same macro over and over
from the current cursor location.

Any ideas are much appreciated!

[Daiya Mitchell]

Requests for help writing code for macros are better directed to a group
that specializes in Word VBA, aka Word Programming, for instance:

[you may need to re-wrap these URLs if they are not clickable]

http://www.microsoft.com/office/comm...g=microsoft.pu
blic.word.vba.general&lang=en&cr=US

If just beginning with macros, this group is for beginners:

http://www.microsoft.com/communities...aspx?dg=micros
oft.public.word.vba.beginners&lang=en&cr=US

There are many more people in those groups who have the ability to answer
your question, so you will probably get a better and faster answer there.


On 10/18/06 9:12 AM, "Marla 5670" wrote:

BTW, I used Do Until True...Loop--with bad results. Two more questions:
Is there a way to say Do Until End of File?
Also, is there a keystroke to stop a repeating macro?

"Marla 5670" wrote:

I am trying to transform text outputted from an old legacy "roll and scroll"
database system to a table in Word. Search and replace are not adequate. I
need to do multiple searches for colons, copy text from one field to a table
in another document, return, search to the next colon, copy text, etc.

There are over 700 pages of this data. I would like to record one macro
action to copy one complete record, then add a loop to get it to repeat the
data extraction/copying for the rest of the records in the doc.

Do I simply add the loop statement to the end of the macro? The Repeat
Action command did not work. I just want to run the same macro over and over
from the current cursor location.

Any ideas are much appreciated!

--
Daiya Mitchell, MVP Mac/Word
Word FAQ: http://www.word.mvps.org/
MacWord Tips: http://word.mvps.org/Mac/WordMacHome.html
What's an MVP? A volunteer! Read the FAQ: http://mvp.support.microsoft.com/

[Greg Maxey]

Marla,

Assuming you have a document with a single table and single row saved
as C:\DataStore, you could run a macro similar to the following. Note,
what I don't know is what you want done specifically when you find the
":". I have provided options to extend the found range a set number of
characters or until an ending flag is reached and then put that data in
the table.

HTH

Sub Scratchmacro()
Dim oRng As Word.Range
Dim oStore As Word.Document
Set oRng = ActiveDocument.Range
Set oStore = Documents.Open("C:\DataStore.doc")
With oRng.Find
.Text = ":"
While .Execute
'Extend found range a set number of characters, words, etc.
oRng.MoveEnd wdCharacter, 10
'or until an ending flag e.g. "@"
'oRng.MoveEndUntil Cset:="@", Count:=wdForward
oStore.Tables(1).Rows.Last.Range.Text = oRng.Text
oStore.Tables(1).Rows.Add
oRng.Collapse wdCollapseEnd
Wend
End With
oStore.Tables(1).Rows.Last.Delete
oStore.Save
End Sub


Marla 5670 wrote:
I am trying to transform text outputted from an old legacy "roll and scroll"
database system to a table in Word. Search and replace are not adequate. I
need to do multiple searches for colons, copy text from one field to a table
in another document, return, search to the next colon, copy text, etc.

There are over 700 pages of this data. I would like to record one macro
action to copy one complete record, then add a loop to get it to repeat the
data extraction/copying for the rest of the records in the doc.

Do I simply add the loop statement to the end of the macro? The Repeat
Action command did not work. I just want to run the same macro over and over
from the current cursor location.

Any ideas are much appreciated!

[Marla 5670]

Thanks for the reply. Let me try to clarify. Unfortunately, it is a massive
text file I captured from a data listing on a terminal emulator. Each record
takes up a whole page with lots of blank lines, spaces, and dashes and
control characters in between. I can strip those out with a search and
replace, but I need a clean way to repeat that macro.

The second pass requires formatting selected data into a table format. There
are about 8 fields in this file that I need to capture and place in a table.
I think I can record a macro that grabs the data for one record, and copies
to a table somewhere else, but I don't know how to repeat until the EOF
and/or stop a macro (manual override) that is running.

I need to be able to run the same report whenever I need to, convert to a
table, then sort So this needs to be repeatable. I have never created a table
through VB code.

I am trying to grab ONLY these fields: Entry Name, Number, Entry Date,
Priority, submitted by, description, impact, recommendation.

Great web site, BTW! Thanks for any further assistance you may give...


The Data looks like this:
--------------------------------
ENTRY NAME GOES HERE
Entry Number : 99999 Entry Date: JAN 99,9999
Priority : XXXXXXXXXXXXXXXXXX
Category:
Status : XXXXXXXXXXXXXXXXXX Resolved Date:
Submitted By : XXXXX Submitted Date:
Package: XXXXX
Message #:

Description:
DESCRIPTION GOES HERE

Impact:
IMPACT GOES HERE

Recommendation:
RECOMMENDATION GOES HERE

"Greg Maxey" wrote:

Marla,

Assuming you have a document with a single table and single row saved
as C:\DataStore, you could run a macro similar to the following. Note,
what I don't know is what you want done specifically when you find the
":". I have provided options to extend the found range a set number of
characters or until an ending flag is reached and then put that data in
the table.

HTH

Sub Scratchmacro()
Dim oRng As Word.Range
Dim oStore As Word.Document
Set oRng = ActiveDocument.Range
Set oStore = Documents.Open("C:\DataStore.doc")
With oRng.Find
.Text = ":"
While .Execute
'Extend found range a set number of characters, words, etc.
oRng.MoveEnd wdCharacter, 10
'or until an ending flag e.g. "@"
'oRng.MoveEndUntil Cset:="@", Count:=wdForward
oStore.Tables(1).Rows.Last.Range.Text = oRng.Text
oStore.Tables(1).Rows.Add
oRng.Collapse wdCollapseEnd
Wend
End With
oStore.Tables(1).Rows.Last.Delete
oStore.Save
End Sub


Marla 5670 wrote:
I am trying to transform text outputted from an old legacy "roll and scroll"
database system to a table in Word. Search and replace are not adequate. I
need to do multiple searches for colons, copy text from one field to a table
in another document, return, search to the next colon, copy text, etc.

There are over 700 pages of this data. I would like to record one macro
action to copy one complete record, then add a loop to get it to repeat the
data extraction/copying for the rest of the records in the doc.

Do I simply add the loop statement to the end of the macro? The Repeat
Action command did not work. I just want to run the same macro over and over
from the current cursor location.

Any ideas are much appreciated!

[Greg Maxey]

Marla,

A tall order considering you are the one the one who can see the
problems ;-)

Have you tried my CleanUp text addin?

http://gregmaxey.mvps.org/Clean_Up_Text.htm

Perhaps that will help clear up some of the interfering mess.


The code I should you will stop when it no longer finds the search
string (or at least it does here with my limited testing).

CTRL+ALT+Break should stop a macro that is running.

ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=4,
NumColumns:=3
is an example of code to add a table with VBA.



Marla 5670 wrote:
Thanks for the reply. Let me try to clarify. Unfortunately, it is a massive
text file I captured from a data listing on a terminal emulator. Each record
takes up a whole page with lots of blank lines, spaces, and dashes and
control characters in between. I can strip those out with a search and
replace, but I need a clean way to repeat that macro.

The second pass requires formatting selected data into a table format. There
are about 8 fields in this file that I need to capture and place in a table.
I think I can record a macro that grabs the data for one record, and copies
to a table somewhere else, but I don't know how to repeat until the EOF
and/or stop a macro (manual override) that is running.

I need to be able to run the same report whenever I need to, convert to a
table, then sort So this needs to be repeatable. I have never created a table
through VB code.

I am trying to grab ONLY these fields: Entry Name, Number, Entry Date,
Priority, submitted by, description, impact, recommendation.

Great web site, BTW! Thanks for any further assistance you may give...


The Data looks like this:
--------------------------------
ENTRY NAME GOES HERE
Entry Number : 99999 Entry Date: JAN 99,9999
Priority : XXXXXXXXXXXXXXXXXX
Category:
Status : XXXXXXXXXXXXXXXXXX Resolved Date:
Submitted By : XXXXX Submitted Date:
Package: XXXXX
Message #:

Description:
DESCRIPTION GOES HERE

Impact:
IMPACT GOES HERE

Recommendation:
RECOMMENDATION GOES HERE

"Greg Maxey" wrote:

Marla,

Assuming you have a document with a single table and single row saved
as C:\DataStore, you could run a macro similar to the following. Note,
what I don't know is what you want done specifically when you find the
":". I have provided options to extend the found range a set number of
characters or until an ending flag is reached and then put that data in
the table.

HTH

Sub Scratchmacro()
Dim oRng As Word.Range
Dim oStore As Word.Document
Set oRng = ActiveDocument.Range
Set oStore = Documents.Open("C:\DataStore.doc")
With oRng.Find
.Text = ":"
While .Execute
'Extend found range a set number of characters, words, etc.
oRng.MoveEnd wdCharacter, 10
'or until an ending flag e.g. "@"
'oRng.MoveEndUntil Cset:="@", Count:=wdForward
oStore.Tables(1).Rows.Last.Range.Text = oRng.Text
oStore.Tables(1).Rows.Add
oRng.Collapse wdCollapseEnd
Wend
End With
oStore.Tables(1).Rows.Last.Delete
oStore.Save
End Sub


Marla 5670 wrote:
I am trying to transform text outputted from an old legacy "roll and scroll"
database system to a table in Word. Search and replace are not adequate. I
need to do multiple searches for colons, copy text from one field to a table
in another document, return, search to the next colon, copy text, etc.

There are over 700 pages of this data. I would like to record one macro
action to copy one complete record, then add a loop to get it to repeat the
data extraction/copying for the rest of the records in the doc.

Do I simply add the loop statement to the end of the macro? The Repeat
Action command did not work. I just want to run the same macro over and over
from the current cursor location.

Any ideas are much appreciated!

[Marla 5670]

That's a very clever add-in, and I'll use it in the future, but I have groups
of lines that start with control characters. I dealt with the cleanup part;
it's the data extraction that is tough for me. I've done other programming,
but don't know VBA.

I think this problem more closely resembles the one that your Extract
Formfield Data macro solves, but I don't have real form fields (just text
based fields), and all of the data is on one big file, not separate ones. I
could dump the data into Access, if I could figure out how to modify the
macro. Don't have much more time to spend time on this today. Thanks again
for your suggestions.
Marla

"Greg Maxey" wrote:

Marla,

A tall order considering you are the one the one who can see the
problems ;-)

Have you tried my CleanUp text addin?

http://gregmaxey.mvps.org/Clean_Up_Text.htm

Perhaps that will help clear up some of the interfering mess.


The code I should you will stop when it no longer finds the search
string (or at least it does here with my limited testing).

CTRL+ALT+Break should stop a macro that is running.

ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=4,
NumColumns:=3
is an example of code to add a table with VBA.



Marla 5670 wrote:
Thanks for the reply. Let me try to clarify. Unfortunately, it is a massive
text file I captured from a data listing on a terminal emulator. Each record
takes up a whole page with lots of blank lines, spaces, and dashes and
control characters in between. I can strip those out with a search and
replace, but I need a clean way to repeat that macro.

The second pass requires formatting selected data into a table format. There
are about 8 fields in this file that I need to capture and place in a table.
I think I can record a macro that grabs the data for one record, and copies
to a table somewhere else, but I don't know how to repeat until the EOF
and/or stop a macro (manual override) that is running.

I need to be able to run the same report whenever I need to, convert to a
table, then sort So this needs to be repeatable. I have never created a table
through VB code.

I am trying to grab ONLY these fields: Entry Name, Number, Entry Date,
Priority, submitted by, description, impact, recommendation.

Great web site, BTW! Thanks for any further assistance you may give...


The Data looks like this:
--------------------------------
ENTRY NAME GOES HERE
Entry Number : 99999 Entry Date: JAN 99,9999
Priority : XXXXXXXXXXXXXXXXXX
Category:
Status : XXXXXXXXXXXXXXXXXX Resolved Date:
Submitted By : XXXXX Submitted Date:
Package: XXXXX
Message #:

Description:
DESCRIPTION GOES HERE

Impact:
IMPACT GOES HERE

Recommendation:
RECOMMENDATION GOES HERE

"Greg Maxey" wrote:

Marla,

Assuming you have a document with a single table and single row saved
as C:\DataStore, you could run a macro similar to the following. Note,
what I don't know is what you want done specifically when you find the
":". I have provided options to extend the found range a set number of
characters or until an ending flag is reached and then put that data in
the table.

HTH

Sub Scratchmacro()
Dim oRng As Word.Range
Dim oStore As Word.Document
Set oRng = ActiveDocument.Range
Set oStore = Documents.Open("C:\DataStore.doc")
With oRng.Find
.Text = ":"
While .Execute
'Extend found range a set number of characters, words, etc.
oRng.MoveEnd wdCharacter, 10
'or until an ending flag e.g. "@"
'oRng.MoveEndUntil Cset:="@", Count:=wdForward
oStore.Tables(1).Rows.Last.Range.Text = oRng.Text
oStore.Tables(1).Rows.Add
oRng.Collapse wdCollapseEnd
Wend
End With
oStore.Tables(1).Rows.Last.Delete
oStore.Save
End Sub


Marla 5670 wrote:
I am trying to transform text outputted from an old legacy "roll and scroll"
database system to a table in Word. Search and replace are not adequate. I
need to do multiple searches for colons, copy text from one field to a table
in another document, return, search to the next colon, copy text, etc.

There are over 700 pages of this data. I would like to record one macro
action to copy one complete record, then add a loop to get it to repeat the
data extraction/copying for the rest of the records in the doc.

Do I simply add the loop statement to the end of the macro? The Repeat
Action command did not work. I just want to run the same macro over and over
from the current cursor location.

Any ideas are much appreciated!