A Question for all of you Macro Gurus

[Glenn]

Here's my situation.

I have in Excel a list of rooms and numbers. For instance:

Room 205 20
Room 206 23
Room 207 24

In word I have a two page document. I have a printer that will allow me to
do back to back, sheet insertion from a different tray, etc.

Here's what I need to do.

I need to print copies of that two page document, based on the number in
each room. (So, I need 20 for Room 205, etc.).

Here's what I would like to do:

Have a macro, or anyway for that matter, that will Print a colored sheet
that says "Room 205 - 20" and then 20 copies, then another colored sheet
that says "Room 206 - 23" and then 23 copies. Etc.

I know I'd have to add a third page to my document (inserted as the first
page) that would have the "Room ....(Merge Fields)". That part of course is
no big deal.

The trick, of course, is the different number of copies.

I have complete faith someone out there can help me!

Thanks!

 

[Doug Robbins]

You will have to workout how to tell the printer which tray to print from,
but for the rest of it, I would just copy the data from Excel into a Word
document, which you should save with the name datadoc.doc for the purposes
of the following macro. Also save the two page document as doccopy.doc (if
you use different names, change the macro accordingly) In the following
macro, you will also need to specify the path to these documents in place of
C:\[folder]. Now, I am assuming that there is a header row in the data and
that the first room number is in the second row of the table. Then the
following macro should do what you want (except for printing the documents
for different sources.

You will find some information on controlling the printer at

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=101

Dim datadoc As Document, divdoc As Document, doccopy As Document, datatable
As Table
Dim i As Long, j As Long
Dim room As Range, copies As Range
'Open the document that contains the roomnumbers and number of copies
Set datadoc = Documents.Open("C:\[folder]\datadoc.doc")
'Open the document of which multiple copies are required
Set doccopy = Documents.Open("C:\[folder]\doccopy.doc")
Set datatable = datadoc.Tables(1)
For i = 2 To datatable.Rows.Count
room = datatable.Cell(i, 1).Range
room.End = room.End - 1
copies = datatable.Cell(i, 2).Range
copies.End = copies.End - 1
Set divdoc = Documents.Add
With divdoc
.Range.InsertAfter room & vbTab & copies
.PrintOut
.Close wdDoNotSaveChanges
End With
doccopy.PrintOut copies:=copies
Next i


--
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

 

[Glenn]

Thanks, but when I run the macro I get a message to debut, and the
highlighted error is:
room = datatable.Cell(i, 1).Range

Do I need to name the table somehow in the first word document?

You will have to workout how to tell the printer which tray to print from,
but for the rest of it, I would just copy the data from Excel into a Word
document, which you should save with the name datadoc.doc for the purposes
of the following macro. Also save the two page document as doccopy.doc
(if you use different names, change the macro accordingly) In the
following macro, you will also need to specify the path to these documents
in place of C:\[folder]. Now, I am assuming that there is a header row in
the data and that the first room number is in the second row of the table.
Then the following macro should do what you want (except for printing the
documents for different sources.

You will find some information on controlling the printer at

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=101

Dim datadoc As Document, divdoc As Document, doccopy As Document,
datatable As Table
Dim i As Long, j As Long
Dim room As Range, copies As Range
'Open the document that contains the roomnumbers and number of copies
Set datadoc = Documents.Open("C:\[folder]\datadoc.doc")
'Open the document of which multiple copies are required
Set doccopy = Documents.Open("C:\[folder]\doccopy.doc")
Set datatable = datadoc.Tables(1)
For i = 2 To datatable.Rows.Count
room = datatable.Cell(i, 1).Range
room.End = room.End - 1
copies = datatable.Cell(i, 2).Range
copies.End = copies.End - 1
Set divdoc = Documents.Add
With divdoc
.Range.InsertAfter room & vbTab & copies
.PrintOut
.Close wdDoNotSaveChanges
End With
doccopy.PrintOut copies:=copies
Next i


--
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

Here's my situation.

I have in Excel a list of rooms and numbers. For instance:

Room 205 20
Room 206 23
Room 207 24

In word I have a two page document. I have a printer that will allow me
to
do back to back, sheet insertion from a different tray, etc.

Here's what I need to do.

I need to print copies of that two page document, based on the number in
each room. (So, I need 20 for Room 205, etc.).

Here's what I would like to do:

Have a macro, or anyway for that matter, that will Print a colored sheet
that says "Room 205 - 20" and then 20 copies, then another colored sheet
that says "Room 206 - 23" and then 23 copies. Etc.

I know I'd have to add a third page to my document (inserted as the first
page) that would have the "Room ....(Merge Fields)". That part of course
is
no big deal.

The trick, of course, is the different number of copies.

I have complete faith someone out there can help me!

Thanks!

 

[Doug Robbins]

What does the error message say?

--
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

Thanks, but when I run the macro I get a message to debut, and the
highlighted error is:
room = datatable.Cell(i, 1).Range

Do I need to name the table somehow in the first word document?

You will have to workout how to tell the printer which tray to print
from, but for the rest of it, I would just copy the data from Excel into
a Word document, which you should save with the name datadoc.doc for the
purposes of the following macro. Also save the two page document as
doccopy.doc (if you use different names, change the macro accordingly)
In the following macro, you will also need to specify the path to these
documents in place of C:\[folder]. Now, I am assuming that there is a
header row in the data and that the first room number is in the second
row of the table. Then the following macro should do what you want
(except for printing the documents for different sources.

You will find some information on controlling the printer at

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=101

Dim datadoc As Document, divdoc As Document, doccopy As Document,
datatable As Table
Dim i As Long, j As Long
Dim room As Range, copies As Range
'Open the document that contains the roomnumbers and number of copies
Set datadoc = Documents.Open("C:\[folder]\datadoc.doc")
'Open the document of which multiple copies are required
Set doccopy = Documents.Open("C:\[folder]\doccopy.doc")
Set datatable = datadoc.Tables(1)
For i = 2 To datatable.Rows.Count
room = datatable.Cell(i, 1).Range
room.End = room.End - 1
copies = datatable.Cell(i, 2).Range
copies.End = copies.End - 1
Set divdoc = Documents.Add
With divdoc
.Range.InsertAfter room & vbTab & copies
.PrintOut
.Close wdDoNotSaveChanges
End With
doccopy.PrintOut copies:=copies
Next i


--
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

Here's my situation.

I have in Excel a list of rooms and numbers. For instance:

Room 205 20
Room 206 23
Room 207 24

In word I have a two page document. I have a printer that will allow me
to
do back to back, sheet insertion from a different tray, etc.

Here's what I need to do.

I need to print copies of that two page document, based on the number in
each room. (So, I need 20 for Room 205, etc.).

Here's what I would like to do:

Have a macro, or anyway for that matter, that will Print a colored sheet
that says "Room 205 - 20" and then 20 copies, then another colored sheet
that says "Room 206 - 23" and then 23 copies. Etc.

I know I'd have to add a third page to my document (inserted as the
first
page) that would have the "Room ....(Merge Fields)". That part of
course is
no big deal.

The trick, of course, is the different number of copies.

I have complete faith someone out there can help me!

Thanks!

 

[Doug Robbins]

It should have been:

Set room = datatable/Cell(i, 1).Range

--
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

Thanks, but when I run the macro I get a message to debut, and the
highlighted error is:
room = datatable.Cell(i, 1).Range

Do I need to name the table somehow in the first word document?

You will have to workout how to tell the printer which tray to print
from, but for the rest of it, I would just copy the data from Excel into
a Word document, which you should save with the name datadoc.doc for the
purposes of the following macro. Also save the two page document as
doccopy.doc (if you use different names, change the macro accordingly)
In the following macro, you will also need to specify the path to these
documents in place of C:\[folder]. Now, I am assuming that there is a
header row in the data and that the first room number is in the second
row of the table. Then the following macro should do what you want
(except for printing the documents for different sources.

You will find some information on controlling the printer at

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=101

Dim datadoc As Document, divdoc As Document, doccopy As Document,
datatable As Table
Dim i As Long, j As Long
Dim room As Range, copies As Range
'Open the document that contains the roomnumbers and number of copies
Set datadoc = Documents.Open("C:\[folder]\datadoc.doc")
'Open the document of which multiple copies are required
Set doccopy = Documents.Open("C:\[folder]\doccopy.doc")
Set datatable = datadoc.Tables(1)
For i = 2 To datatable.Rows.Count
room = datatable.Cell(i, 1).Range
room.End = room.End - 1
copies = datatable.Cell(i, 2).Range
copies.End = copies.End - 1
Set divdoc = Documents.Add
With divdoc
.Range.InsertAfter room & vbTab & copies
.PrintOut
.Close wdDoNotSaveChanges
End With
doccopy.PrintOut copies:=copies
Next i


--
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

Here's my situation.

I have in Excel a list of rooms and numbers. For instance:

Room 205 20
Room 206 23
Room 207 24

In word I have a two page document. I have a printer that will allow me
to
do back to back, sheet insertion from a different tray, etc.

Here's what I need to do.

I need to print copies of that two page document, based on the number in
each room. (So, I need 20 for Room 205, etc.).

Here's what I would like to do:

Have a macro, or anyway for that matter, that will Print a colored sheet
that says "Room 205 - 20" and then 20 copies, then another colored sheet
that says "Room 206 - 23" and then 23 copies. Etc.

I know I'd have to add a third page to my document (inserted as the
first
page) that would have the "Room ....(Merge Fields)". That part of
course is
no big deal.

The trick, of course, is the different number of copies.

I have complete faith someone out there can help me!

Thanks!

 

[Glenn]

Okay, I made that correction...had to add "Set" to the copies = as well.
Now I get another error - Runtime Error 13...mismatch...and it brings me to

doccopy.PrintOut copies:=copies

when I debug.

It should have been:

Set room = datatable/Cell(i, 1).Range

--
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

Thanks, but when I run the macro I get a message to debut, and the
highlighted error is:
room = datatable.Cell(i, 1).Range

Do I need to name the table somehow in the first word document?

You will have to workout how to tell the printer which tray to print
from, but for the rest of it, I would just copy the data from Excel into
a Word document, which you should save with the name datadoc.doc for the
purposes of the following macro. Also save the two page document as
doccopy.doc (if you use different names, change the macro accordingly)
In the following macro, you will also need to specify the path to these
documents in place of C:\[folder]. Now, I am assuming that there is a
header row in the data and that the first room number is in the second
row of the table. Then the following macro should do what you want
(except for printing the documents for different sources.

You will find some information on controlling the printer at

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=101

Dim datadoc As Document, divdoc As Document, doccopy As Document,
datatable As Table
Dim i As Long, j As Long
Dim room As Range, copies As Range
'Open the document that contains the roomnumbers and number of copies
Set datadoc = Documents.Open("C:\[folder]\datadoc.doc")
'Open the document of which multiple copies are required
Set doccopy = Documents.Open("C:\[folder]\doccopy.doc")
Set datatable = datadoc.Tables(1)
For i = 2 To datatable.Rows.Count
room = datatable.Cell(i, 1).Range
room.End = room.End - 1
copies = datatable.Cell(i, 2).Range
copies.End = copies.End - 1
Set divdoc = Documents.Add
With divdoc
.Range.InsertAfter room & vbTab & copies
.PrintOut
.Close wdDoNotSaveChanges
End With
doccopy.PrintOut copies:=copies
Next i


--
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
Here's my situation.

I have in Excel a list of rooms and numbers. For instance:

Room 205 20
Room 206 23
Room 207 24

In word I have a two page document. I have a printer that will allow
me to
do back to back, sheet insertion from a different tray, etc.

Here's what I need to do.

I need to print copies of that two page document, based on the number
in
each room. (So, I need 20 for Room 205, etc.).

Here's what I would like to do:

Have a macro, or anyway for that matter, that will Print a colored
sheet
that says "Room 205 - 20" and then 20 copies, then another colored
sheet
that says "Room 206 - 23" and then 23 copies. Etc.

I know I'd have to add a third page to my document (inserted as the
first
page) that would have the "Room ....(Merge Fields)". That part of
course is
no big deal.

The trick, of course, is the different number of copies.

I have complete faith someone out there can help me!

Thanks!

 

[Glenn]

I took out the : after copies and was able to print without getting that
error, however it isn't working correctly.

No matter what the number is in the table, it is only printing one copy. I
am getting the cover page that gives the Room # and the number of copies
that it should be making.

Okay, I made that correction...had to add "Set" to the copies = as well.
Now I get another error - Runtime Error 13...mismatch...and it brings me
to

doccopy.PrintOut copies:=copies

when I debug.

It should have been:

Set room = datatable/Cell(i, 1).Range

--
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

Thanks, but when I run the macro I get a message to debut, and the
highlighted error is:
room = datatable.Cell(i, 1).Range

Do I need to name the table somehow in the first word document?

You will have to workout how to tell the printer which tray to print
from, but for the rest of it, I would just copy the data from Excel
into a Word document, which you should save with the name datadoc.doc
for the purposes of the following macro. Also save the two page
document as doccopy.doc (if you use different names, change the macro
accordingly) In the following macro, you will also need to specify the
path to these documents in place of C:\[folder]. Now, I am assuming
that there is a header row in the data and that the first room number
is in the second row of the table. Then the following macro should do
what you want (except for printing the documents for different sources.

You will find some information on controlling the printer at

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=101

Dim datadoc As Document, divdoc As Document, doccopy As Document,
datatable As Table
Dim i As Long, j As Long
Dim room As Range, copies As Range
'Open the document that contains the roomnumbers and number of copies
Set datadoc = Documents.Open("C:\[folder]\datadoc.doc")
'Open the document of which multiple copies are required
Set doccopy = Documents.Open("C:\[folder]\doccopy.doc")
Set datatable = datadoc.Tables(1)
For i = 2 To datatable.Rows.Count
room = datatable.Cell(i, 1).Range
room.End = room.End - 1
copies = datatable.Cell(i, 2).Range
copies.End = copies.End - 1
Set divdoc = Documents.Add
With divdoc
.Range.InsertAfter room & vbTab & copies
.PrintOut
.Close wdDoNotSaveChanges
End With
doccopy.PrintOut copies:=copies
Next i


--
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
Here's my situation.

I have in Excel a list of rooms and numbers. For instance:

Room 205 20
Room 206 23
Room 207 24

In word I have a two page document. I have a printer that will allow
me to
do back to back, sheet insertion from a different tray, etc.

Here's what I need to do.

I need to print copies of that two page document, based on the number
in
each room. (So, I need 20 for Room 205, etc.).

Here's what I would like to do:

Have a macro, or anyway for that matter, that will Print a colored
sheet
that says "Room 205 - 20" and then 20 copies, then another colored
sheet
that says "Room 206 - 23" and then 23 copies. Etc.

I know I'd have to add a third page to my document (inserted as the
first
page) that would have the "Room ....(Merge Fields)". That part of
course is
no big deal.

The trick, of course, is the different number of copies.

I have complete faith someone out there can help me!

Thanks!

 

[Doug Robbins]

Word might consider the word copies as a reserved term, so try the following
modified code

Dim datadoc As Document, divdoc As Document, doccopy As Document, datatable
As Table
Dim i As Long, j As Long
Dim room As Range, numcopies As Range
'Open the document that contains the roomnumbers and number of copies
Set datadoc = Documents.Open("C:\[folder]\datadoc.doc")
'Open the document of which multiple copies are required
Set doccopy = Documents.Open("C:\[folder]\doccopy.doc")
Set datatable = datadoc.Tables(1)
For i = 2 To datatable.Rows.Count
Set room = datatable.Cell(i, 1).Range
room.End = room.End - 1
Set numcopies = datatable.Cell(i, 2).Range
numcopies.End = numcopies.End - 1
Set divdoc = Documents.Add
With divdoc
.Range.InsertAfter room & vbTab & numcopies
.PrintOut
.Close wdDoNotSaveChanges
End With
doccopy.PrintOut copies:=val(numcopies)
Next i


--
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 took out the : after copies and was able to print without getting that
error, however it isn't working correctly.

No matter what the number is in the table, it is only printing one copy.
I am getting the cover page that gives the Room # and the number of copies
that it should be making.

Okay, I made that correction...had to add "Set" to the copies = as well.
Now I get another error - Runtime Error 13...mismatch...and it brings me
to

doccopy.PrintOut copies:=copies

when I debug.

It should have been:

Set room = datatable/Cell(i, 1).Range

--
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
Thanks, but when I run the macro I get a message to debut, and the
highlighted error is:
room = datatable.Cell(i, 1).Range

Do I need to name the table somehow in the first word document?

You will have to workout how to tell the printer which tray to print
from, but for the rest of it, I would just copy the data from Excel
into a Word document, which you should save with the name datadoc.doc
for the purposes of the following macro. Also save the two page
document as doccopy.doc (if you use different names, change the macro
accordingly) In the following macro, you will also need to specify the
path to these documents in place of C:\[folder]. Now, I am assuming
that there is a header row in the data and that the first room number
is in the second row of the table. Then the following macro should do
what you want (except for printing the documents for different
sources.

You will find some information on controlling the printer at

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=101

Dim datadoc As Document, divdoc As Document, doccopy As Document,
datatable As Table
Dim i As Long, j As Long
Dim room As Range, copies As Range
'Open the document that contains the roomnumbers and number of copies
Set datadoc = Documents.Open("C:\[folder]\datadoc.doc")
'Open the document of which multiple copies are required
Set doccopy = Documents.Open("C:\[folder]\doccopy.doc")
Set datatable = datadoc.Tables(1)
For i = 2 To datatable.Rows.Count
room = datatable.Cell(i, 1).Range
room.End = room.End - 1
copies = datatable.Cell(i, 2).Range
copies.End = copies.End - 1
Set divdoc = Documents.Add
With divdoc
.Range.InsertAfter room & vbTab & copies
.PrintOut
.Close wdDoNotSaveChanges
End With
doccopy.PrintOut copies:=copies
Next i


--
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
Here's my situation.

I have in Excel a list of rooms and numbers. For instance:

Room 205 20
Room 206 23
Room 207 24

In word I have a two page document. I have a printer that will allow
me to
do back to back, sheet insertion from a different tray, etc.

Here's what I need to do.

I need to print copies of that two page document, based on the number
in
each room. (So, I need 20 for Room 205, etc.).

Here's what I would like to do:

Have a macro, or anyway for that matter, that will Print a colored
sheet
that says "Room 205 - 20" and then 20 copies, then another colored
sheet
that says "Room 206 - 23" and then 23 copies. Etc.

I know I'd have to add a third page to my document (inserted as the
first
page) that would have the "Room ....(Merge Fields)". That part of
course is
no big deal.

The trick, of course, is the different number of copies.

I have complete faith someone out there can help me!

Thanks!