Reply
 
Thread Tools Display Modes
  #1   Report Post  
Maria74
 
Posts: n/a
Default In mail merge (word & excel) how do i get the data to read top to.

to bottom in word labels? The information/data taken from excel puts it in
alphabetical order reading left to right.
  #2   Report Post  
Doug Robbins
 
Posts: n/a
Default

If you use a catalog or directory type mailmerge to get the data into the
form of word table to which you add a row at the top into which you insert
the names of the mergefields and then run this macro with that document
open, it will sort the table so that if you then save it and use it as the
data source for your label mailmerge, the labels will be in the order that
you want:

' Macro to assign numbers to data source so that it can be sorted to cause
labels to print down columns
Dim Message, Title, Default, labelrows, labelcolumns, i As Integer, j As
Integer, k As Integer
Message = "Enter the number of labels in a row" ' Set prompt.
Title = "Labels per Row" ' Set title.
Default = "3" ' Set default.
' Display message, title, and default value.
labelcolumns = InputBox(Message, Title, Default)
Message = "Enter the number of labels in a column" ' Set prompt.
Title = "Labels per column" ' Set title.
Default = "5" ' Set default.
labelrows = InputBox(Message, Title, Default)
ActiveDocument.Tables(1).Columns.Add
BeforeColumn:=ActiveDocument.Tables(1).Columns(1)
ActiveDocument.Tables(1).Rows(1).Range.Cut
k = 1
For i = 1 To ActiveDocument.Tables(1).Rows.Count - labelcolumns
For j = 1 To labelrows
ActiveDocument.Tables(1).Cell(i, 1).Range.InsertBefore k + (j - 1) *
labelcolumns
i = i + 1
Next j
k = k + 1
i = i - 1
If k Mod labelcolumns = 1 Then k = k - labelcolumns + labelcolumns *
labelrows
Next i
ActiveDocument.Tables(1).Sort FieldNumber:="Column 1"
ActiveDocument.Tables(1).Rows(1).Select
Selection.Paste
ActiveDocument.Tables(1).Columns(1).Delete

--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP
"Maria74" wrote in message
...
to bottom in word labels? The information/data taken from excel puts it in
alphabetical order reading left to right.



  #3   Report Post  
Maria74
 
Posts: n/a
Default

Thanks, that did the trick!

"Doug Robbins" wrote:

If you use a catalog or directory type mailmerge to get the data into the
form of word table to which you add a row at the top into which you insert
the names of the mergefields and then run this macro with that document
open, it will sort the table so that if you then save it and use it as the
data source for your label mailmerge, the labels will be in the order that
you want:

' Macro to assign numbers to data source so that it can be sorted to cause
labels to print down columns
Dim Message, Title, Default, labelrows, labelcolumns, i As Integer, j As
Integer, k As Integer
Message = "Enter the number of labels in a row" ' Set prompt.
Title = "Labels per Row" ' Set title.
Default = "3" ' Set default.
' Display message, title, and default value.
labelcolumns = InputBox(Message, Title, Default)
Message = "Enter the number of labels in a column" ' Set prompt.
Title = "Labels per column" ' Set title.
Default = "5" ' Set default.
labelrows = InputBox(Message, Title, Default)
ActiveDocument.Tables(1).Columns.Add
BeforeColumn:=ActiveDocument.Tables(1).Columns(1)
ActiveDocument.Tables(1).Rows(1).Range.Cut
k = 1
For i = 1 To ActiveDocument.Tables(1).Rows.Count - labelcolumns
For j = 1 To labelrows
ActiveDocument.Tables(1).Cell(i, 1).Range.InsertBefore k + (j - 1) *
labelcolumns
i = i + 1
Next j
k = k + 1
i = i - 1
If k Mod labelcolumns = 1 Then k = k - labelcolumns + labelcolumns *
labelrows
Next i
ActiveDocument.Tables(1).Sort FieldNumber:="Column 1"
ActiveDocument.Tables(1).Rows(1).Select
Selection.Paste
ActiveDocument.Tables(1).Columns(1).Delete

--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP
"Maria74" wrote in message
...
to bottom in word labels? The information/data taken from excel puts it in
alphabetical order reading left to right.




  #4   Report Post  
Sharona
 
Posts: n/a
Default

I am trying to follow the directions here but I MUST be missing something. I
got into macros in excel (which is the program my data is in), I copied this
macros and pasted it in and tried to run but I keep getting syntax errors. I
have never delt directly with Macros before. HELP!!


"Doug Robbins" wrote:

If you use a catalog or directory type mailmerge to get the data into the
form of word table to which you add a row at the top into which you insert
the names of the mergefields and then run this macro with that document
open, it will sort the table so that if you then save it and use it as the
data source for your label mailmerge, the labels will be in the order that
you want:

' Macro to assign numbers to data source so that it can be sorted to cause
labels to print down columns
Dim Message, Title, Default, labelrows, labelcolumns, i As Integer, j As
Integer, k As Integer
Message = "Enter the number of labels in a row" ' Set prompt.
Title = "Labels per Row" ' Set title.
Default = "3" ' Set default.
' Display message, title, and default value.
labelcolumns = InputBox(Message, Title, Default)
Message = "Enter the number of labels in a column" ' Set prompt.
Title = "Labels per column" ' Set title.
Default = "5" ' Set default.
labelrows = InputBox(Message, Title, Default)
ActiveDocument.Tables(1).Columns.Add
BeforeColumn:=ActiveDocument.Tables(1).Columns(1)
ActiveDocument.Tables(1).Rows(1).Range.Cut
k = 1
For i = 1 To ActiveDocument.Tables(1).Rows.Count - labelcolumns
For j = 1 To labelrows
ActiveDocument.Tables(1).Cell(i, 1).Range.InsertBefore k + (j - 1) *
labelcolumns
i = i + 1
Next j
k = k + 1
i = i - 1
If k Mod labelcolumns = 1 Then k = k - labelcolumns + labelcolumns *
labelrows
Next i
ActiveDocument.Tables(1).Sort FieldNumber:="Column 1"
ActiveDocument.Tables(1).Rows(1).Select
Selection.Paste
ActiveDocument.Tables(1).Columns(1).Delete

--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP
"Maria74" wrote in message
...
to bottom in word labels? The information/data taken from excel puts it in
alphabetical order reading left to right.




  #5   Report Post  
Doug Robbins
 
Posts: n/a
Default

The macro was written to be run on a table in a Word document. Copy and
paste the data from your Excel spreadsheet into a Word document and then run
the macro when that document is the active document.


See the article "What do I do with macros sent to me by other newsgroup
readers to help me out?"
http://www.word.mvps.org/FAQs/Macros...eateAMacro.htm


--
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
"Sharona" wrote in message
...
I am trying to follow the directions here but I MUST be missing something.
I
got into macros in excel (which is the program my data is in), I copied
this
macros and pasted it in and tried to run but I keep getting syntax errors.
I
have never delt directly with Macros before. HELP!!


"Doug Robbins" wrote:

If you use a catalog or directory type mailmerge to get the data into the
form of word table to which you add a row at the top into which you
insert
the names of the mergefields and then run this macro with that document
open, it will sort the table so that if you then save it and use it as
the
data source for your label mailmerge, the labels will be in the order
that
you want:

' Macro to assign numbers to data source so that it can be sorted to
cause
labels to print down columns
Dim Message, Title, Default, labelrows, labelcolumns, i As Integer, j As
Integer, k As Integer
Message = "Enter the number of labels in a row" ' Set prompt.
Title = "Labels per Row" ' Set title.
Default = "3" ' Set default.
' Display message, title, and default value.
labelcolumns = InputBox(Message, Title, Default)
Message = "Enter the number of labels in a column" ' Set prompt.
Title = "Labels per column" ' Set title.
Default = "5" ' Set default.
labelrows = InputBox(Message, Title, Default)
ActiveDocument.Tables(1).Columns.Add
BeforeColumn:=ActiveDocument.Tables(1).Columns(1)
ActiveDocument.Tables(1).Rows(1).Range.Cut
k = 1
For i = 1 To ActiveDocument.Tables(1).Rows.Count - labelcolumns
For j = 1 To labelrows
ActiveDocument.Tables(1).Cell(i, 1).Range.InsertBefore k + (j -
1) *
labelcolumns
i = i + 1
Next j
k = k + 1
i = i - 1
If k Mod labelcolumns = 1 Then k = k - labelcolumns + labelcolumns *
labelrows
Next i
ActiveDocument.Tables(1).Sort FieldNumber:="Column 1"
ActiveDocument.Tables(1).Rows(1).Select
Selection.Paste
ActiveDocument.Tables(1).Columns(1).Delete

--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a
paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP
"Maria74" wrote in message
...
to bottom in word labels? The information/data taken from excel puts it
in
alphabetical order reading left to right.








  #6   Report Post  
Sharona
 
Posts: n/a
Default



"Sharona" wrote:

I am trying to follow the directions here but I MUST be missing something. I
got into macros in excel (which is the program my data is in), I copied this
macros and pasted it in and tried to run but I keep getting syntax errors. I
have never delt directly with Macros before. HELP!!


"Doug Robbins" wrote:

If you use a catalog or directory type mailmerge to get the data into the
form of word table to which you add a row at the top into which you insert
the names of the mergefields and then run this macro with that document
open, it will sort the table so that if you then save it and use it as the
data source for your label mailmerge, the labels will be in the order that
you want:

' Macro to assign numbers to data source so that it can be sorted to cause
labels to print down columns
Dim Message, Title, Default, labelrows, labelcolumns, i As Integer, j As
Integer, k As Integer
Message = "Enter the number of labels in a row" ' Set prompt.
Title = "Labels per Row" ' Set title.
Default = "3" ' Set default.
' Display message, title, and default value.
labelcolumns = InputBox(Message, Title, Default)
Message = "Enter the number of labels in a column" ' Set prompt.
Title = "Labels per column" ' Set title.
Default = "5" ' Set default.
labelrows = InputBox(Message, Title, Default)
ActiveDocument.Tables(1).Columns.Add
BeforeColumn:=ActiveDocument.Tables(1).Columns(1)
ActiveDocument.Tables(1).Rows(1).Range.Cut
k = 1
For i = 1 To ActiveDocument.Tables(1).Rows.Count - labelcolumns
For j = 1 To labelrows
ActiveDocument.Tables(1).Cell(i, 1).Range.InsertBefore k + (j - 1) *
labelcolumns
i = i + 1
Next j
k = k + 1
i = i - 1
If k Mod labelcolumns = 1 Then k = k - labelcolumns + labelcolumns *
labelrows
Next i
ActiveDocument.Tables(1).Sort FieldNumber:="Column 1"
ActiveDocument.Tables(1).Rows(1).Select
Selection.Paste
ActiveDocument.Tables(1).Columns(1).Delete

--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP
"Maria74" wrote in message
...
to bottom in word labels? The information/data taken from excel puts it in
alphabetical order reading left to right.



I will give this a try. Thanks so much.

Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Mail Merge Word 2003 Issue S.Nicks Mailmerge 5 February 20th 09 02:55 AM
Insert excel data into word via mail merge Jane Mailmerge 1 December 22nd 04 11:53 PM
Word Field Codes in Excel data file mranz Mailmerge 7 December 11th 04 10:02 AM
Word mail merge data loss Gibson Mailmerge 1 December 8th 04 02:30 PM
Mail merge error occurs when filtering Excel data source Dave Mailmerge 1 December 2nd 04 11:46 PM


All times are GMT +1. The time now is 12:29 AM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"