If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
A Question for all of you Macro Gurus
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! |
#2
|
|||
|
|||
A Question for all of you Macro Gurus
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/P...cle.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" wrote in message ... 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! |
#3
|
|||
|
|||
A Question for all of you Macro Gurus
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? "Doug Robbins" wrote in message ... 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/P...cle.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" wrote in message ... 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! |
#4
|
|||
|
|||
A Question for all of you Macro Gurus
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 "Glenn" wrote in message ... 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? "Doug Robbins" wrote in message ... 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/P...cle.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" wrote in message ... 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! |
#5
|
|||
|
|||
A Question for all of you Macro Gurus
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 "Glenn" wrote in message ... 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? "Doug Robbins" wrote in message ... 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/P...cle.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" wrote in message ... 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! |
#6
|
|||
|
|||
A Question for all of you Macro Gurus
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. "Doug Robbins" wrote in message ... 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 "Glenn" wrote in message ... 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? "Doug Robbins" wrote in message ... 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/P...cle.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" wrote in message ... 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! |
#7
|
|||
|
|||
A Question for all of you Macro Gurus
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. "Glenn" wrote in message ... 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. "Doug Robbins" wrote in message ... 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 "Glenn" wrote in message ... 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? "Doug Robbins" wrote in message ... 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/P...cle.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" wrote in message ... 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! |
#8
|
|||
|
|||
A Question for all of you Macro Gurus
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 "Glenn" wrote in message ... 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. "Glenn" wrote in message ... 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. "Doug Robbins" wrote in message ... 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 "Glenn" wrote in message ... 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? "Doug Robbins" wrote in message ... 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/P...cle.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" wrote in message ... 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! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Question about macro | Jason | General Discussion | 0 | April 27th, 2005 10:37 PM |
simple Macro question | Jan Schmidt | New Users | 2 | December 1st, 2004 09:15 PM |
Word question: macro for bullets with double spacing between bullets? | -Steve-Krause- | General Discussions | 3 | December 1st, 2004 09:02 PM |
macro question | barb | Worksheet Functions | 2 | September 10th, 2004 07:56 PM |
Excel Gurus = want a macro in 1 workbook to get info from another workbook = Read please | harry | Worksheet Functions | 1 | December 20th, 2003 01:32 AM |