Broadcast Printing 1

[kikboxr777]

I am trying to print a single file to multiple printers at the same time on an internal network (broadcast printing). I cannot seem to find how to do it without a thrid party solution. Does anyone know how to do this with the Microsoft Office product directly?

Thanks.

P.S. For all of those that respond....Keep in mind that my level of understanding macros is limited at best....(Wink) Step by step would be appreciated.

 

[CautionMP]

So we need to create a macro that:[ol][li]Changes the [tt]ActivePrinter[/tt] property.[/li][li]Prints a document.[/li][li]Then repeats this for a number of printers.[/li][/ol]

I assume you have the file you want to print, now we need to determine the list of printers you want to send the file to, and find a mechanism to store this list of printers. There are a couple of ways to do this[ul][li]Store the list of printers in an Excel worksheet.[/li][li]Store the list in a Word document (probably in a table).[/li][li]In a text file (if it dosen't change very often)[/li][li]Iterate through the installed printers in your Printers folder (this is the hardest to code but the easiest to use).[/li][/ul]

A couple of questions for you.[ol][li]What type of MS Office document are you printing?[/li][li]Are you printing multiple files from different programs?[/li][li]What Office program are you most comfortable using?[/li][li]Will you be running the process or will it be pushed out to end users?[/li][/ol]

Instant programmer, just add coffee.

 

[kikboxr777]

CautionMP...Thanks for your quick response to this problem.

What is happening here is that I repeatedly have to send a "schedule" that is created in Excel to 6 different fire stations on an internal network. After I prepare it I would like to click a button (preferably) and have it sent to the six stations instead of having to send to one at a time from the Print function and then selecting the printer to send it to. I would like the macro to be part of the excel program so anything that I prepare in excel can be sent to those 6 stations.

In response to your questions:

What type of MS Office document are you printing?

It is an excel spreadsheet that is updated at least 2 or 3 times per day (if not more) depending on vacation, sickness, etc.


Are you printing multiple files from different programs?
We create a new file each day to store on our server for a period of time...But we only need to print one file per day to all Six Stations.

What Office program are you most comfortable using?
I am comfortable Word, Excel, (some access). If you are talking about code...I am not overly familiar with that much of it.

Will you be running the process or will it be pushed out to end users?
The only computer that will need to do this function is mine at the main firehouse. So end users will not be able to do this function.

Thanks Again for your help.

 

[PHV]

Why not playing with the Workbook.SendMail method ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kikboxr777]

PH,

Not sure what the workbook.sendmail method is. I tried to use the help feature but was unable to find any information on it. Please explain. I am open to suggestions that will work.

kik

 

[CautionMP]

Sorry for the delay, that work thing.

Here is a starting point. Since there are only six printers and I suspect they change very rarely I went ahead and stored them in the code that way you can store the macro in GENERAL.XLS (I think that's what it's called) and will be available everytime you open Excel or you can save this in the workbook that actually has the schedule.

Public Sub BroadcastPrint()
'Create a container for the ActiveSheet
Dim wksActive As Worksheet
'Create a 'counter', will use to navigate the Printers() array
Dim intPrinter As Integer
'Create a container for the Printer Names
Dim Printers(5) As String

'Assign the sheet
Set wksActive = ActiveSheet

'Add the printer names (Zero based array)
Printers(0) = "Adobe PDF"
Printers(1) = "GhostscriptPDF"
Printers(2) = "lp"
Printers(3) = "Database Fax"
Printers(4) = "Printer5"
Printers(5) = "Printer6"

'Create a loop that will perform the printing
For intPrinter = 0 To UBound(Printers)
  'Perform the PrintOut method leaving the
  'From, To, Copies, Preview arguments blank
  wksActive.PrintOut , , , , Printers(intPrinter)
Next intPrinter

'Clean up
Set wksActive = Nothing
End Sub

Keep in mind that my level of understanding macros is limited at best

Ok, now what do you do with this code? It needs to be copied into your workbook, easiest way for non-code people:[ol][li]In your Workbook goto Tools => Macro => Record New Macro...[/li][li]In the Record Macro dialog click OK (default macro name should be Macro1)[/li][li]Type any text into a cell[/li][li]Locate the Macro toolbar and click Stop Recording[/li][li]In your Workbook goto Tools => Macro => Macros...[/li][li]Select Macro1 and click Edit[/li][/ol]
You should be looking at the Macro Editor and should see something that starts [tt][blue]Public Sub[/blue] Macro1()[/tt] and ends [tt][blue]End Sub[/blue][/tt], copy the above code block and paste it in under the [tt][blue]End Sub[/blue][/tt]. You will need to update the following lines (text in Bold) to reflect your real printer names (they must be exactly what appears in your Printers folder).[tt]
Printers(0) = "Adobe PDF"
Printers(1) = "GhostscriptPDF"
Printers(2) = "lp"
Printers(3) = "Database Fax"
Printers(4) = "Printer5"
Printers(5) = "Printer6"[/tt]
Now save the workbook and you should be ready to go. To fire the macro you can Tools => Macro => Macros..., select [tt]BroadcastPrint[/tt] and click Run, or create a Menu bar that points to this Macro, or...

One last note, if I mispelled the printer name in the code Excel would print it to my default Printer.

Hope this helps,
CMP

Instant programmer, just add coffee.

 

[kikboxr777]

CautionMP...You are the Shiznizzle!!!

It worked perfectly after I finally found the true printer name. It first started printing all to the default printer like you noted but I finally had to go and print a test page on each printer to finally get the real name because they have a few false ones listed...REally weird.

Thanks again!

Kik