Inserting Cells

[Dawn Crosier]

I am creating a process to generate sheets of Avery 5167 labels. I have a
UserForm where the user identifies the range of text to be inserted on each
label. I have that part figured out - although testing has been pretty
tedious. <smile>

My question is how can I more efficiently insert the labels themselves into
my document? Currently I take the starting number and the ending number,
find the difference between and divide by 80 to know how many sheets. Then
I run the following:

ActiveDocument.Tables(1).Range.Select
If intNumberPages > 1 Then
For counter = 1 To intNumberPages
Selection.InsertCells ShiftCells:=wdInsertCellsShiftDown
Application.StatusBar = "Creating Label Sheet Number: " & counter
Next counter
End If

This process is pretty transparent when there are not many pages to be
inserted. However, since there will be instances when 100 pages worth of
labels will be created, the more pages the longer the period of time.
Currently it takes approximately 6 minutes to generate the sheets and
populate the labels for 93 sheets. I have already turned off
ScreenUpdating - which leaves a very messy screen displayed - bars of dark
gray and white.

Suggestions?

--
Dawn Crosier
"Education Lasts a Lifetime"

This message was sent to a newsgroup. Please post back to the newsgroup so
all may follow the thread.

 

[Jay Freedman]

Hi, Dawn,

Word is notorious for slow manipulation of large tables. See
http://www.mvps.org/word/FAQs/TblsFldsFms/FastTables.htm for some discussion
of speedups. But issues like this make it clear why you should use the mail
merge feature instead.

Keep the data in some more efficient application, such as Excel or Access,
and then use Word to mail merge to labels. That way you'll have a lot more
time to read newsgroups. <grin>

 

[Dawn Crosier]

Thanks Jay,

The hints did help. I sure wish I could figure out how to create a mail
merge on the fly and pretty transparent to the user since all they're
interested in is the final document.

The scope of the project is to allow any user to create a varying range of
Avery 5167 labels. The range includes a text prefix, the ability to have a
beginning number, an ending number and to be able to identify the number of
digits the number includes.

Therefore a typical string result would be: DLC001001 which will then
increment to the end result of DLC019999 or some such number. Each variable
is inserted into a single label.

Then to make matters more complicated, they want the numbers to run down and
then across each sheet / page of labels.

So what I have done is create a UserForm where they can manually key in the
text prefix, the number of digits to include, and then a spin field for both
the starting number and the ending number. I then use a select case to
figure out which cell to put the string into based on the number of the
label.

--
Dawn Crosier
"Education Lasts a Lifetime"

This message was sent to a newsgroup. Please post back to the newsgroup so
all may follow the thread.