Skip a constant number of records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

R Alapatt - 17 Aug 2006 18:32 GMT

I have a data file that lists all the data one below the other. But each
record is a constant number of lines. I need to pick up the address which is
on 5 lines starting every 25th line.

Reply to this Message

Doug Robbins - Word MVP - 17 Aug 2006 21:09 GMT

It is not really practical to try and do that simply with a mail merge. It
would be far better to run a macro over the data source and extract the
necessary information.

What is the data source?

If it were a Word document, you may be able to use the Convert Text to Table
facility under the Table menu to convert the data to a 24 column table and
the insert a row at the top of the table and insert field names into that
row and then just insert the fields from the first five columns into a mail
merge main document.

There are other ways, but the best way will be dependent upon the data
source.

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

>I have a data file that lists all the data one below the other. But each
> record is a constant number of lines. I need to pick up the address which
> is
> on 5 lines starting every 25th line.

Reply to this Message

R Alapatt - 22 Aug 2006 22:12 GMT

Thank you for your response I did not get a notification, hence my entry
again.

My data source is a text file. Each record is 1510 lines. The address is
lines 110 to 115. When you say run a marco ( i am not familiar with macro)
will it create another file with just the address info?

> It is not really practical to try and do that simply with a mail merge. It
> would be far better to run a macro over the data source and extract the
[quoted text clipped - 15 lines]
> > is
> > on 5 lines starting every 25th line.

Reply to this Message

Doug Robbins - Word MVP - 23 Aug 2006 04:25 GMT

Is each record 50 lines as mentioned in your second posting, or 1510 lines
as mentioned in this one. It will make a difference to the code that you
will need in the macro.

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> Thank you for your response I did not get a notification, hence my entry
> again.
[quoted text clipped - 29 lines]
>> > is
>> > on 5 lines starting every 25th line.

Reply to this Message

R Alapatt - 25 Aug 2006 14:17 GMT

It is 1510 lines. the Data is from another software that uses mail merge
(wordperfect) to printout some letters. We need to print out some labels
using the same data.

> Is each record 50 lines as mentioned in your second posting, or 1510 lines
> as mentioned in this one. It will make a difference to the code that you
[quoted text clipped - 33 lines]
> >> > is
> >> > on 5 lines starting every 25th line.

Reply to this Message

Doug Robbins - Word MVP - 25 Aug 2006 21:47 GMT

I don't have a suitable file on which to try this, but if you run a macro
containing the following code when the data document is the activedocument,
it should create a new document with the details of each address in the
cells of a 5 column table. You can then insert a row at the top of the
table into which you insert names to be used for each of the merge fields.

Dim Source As Document, Target As Document
Dim i As Long, j As Long, numrecs As Long
Dim data As Range, record As String
Set Source = ActiveDocument
Set Target = Documents.Add
numrecs = Source.Paragraphs.Count / 1510
For j = 1 To numrecs
For i = 1 To 109 'Delete the first 109 lines from each record
Source.Paragraphs(i).Range.Delete
Next i
record = ""
'Create a string containing the data from the next five lines
For i = 1 To 4
Set data = Source.Paragraphs(i).Range
data.End = data.End - 1
record = record & data.Text & vbTab
Source.Paragraphs(i).Range.Delete
Next i
Set data = Source.Paragraphs(1).Range
data.End = data.End - 1
record = record & data
Target.Range.InsertAfter record & vbCr
Source.Paragraphs(1).Range.Delete
For i = 1 To 1400
Source.Paragraphs(i).Range.Delete 'Delete the remaining lines in
each record
Next i
Next j 'Repeat the process for the next record
Source.Close wdDoNotSaveChanges 'Close the source document without saving it
as all of the data from it has been deleted.
Target.Range.ConvertToTable Separator:=vbTab, NumColumns:=5

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> It is 1510 lines. the Data is from another software that uses mail merge
> (wordperfect) to printout some letters. We need to print out some labels
[quoted text clipped - 43 lines]
>> >> > is
>> >> > on 5 lines starting every 25th line.

Reply to this Message

Rate this thread: