How to print a list of pdf and docs to different types of printers

[Registered55]

Hi,

I have struggled with this process and spent countless hours searching, but I have now hit a dead end, Any help would be great.

At work, we have different types of files (PDF and Word docs) that we need to print, but not all files go to the same printer.

right now, what I have achieved so far is I have a list of the actual file locations in column A row 2

for example

2. \\work\share drive\example\test 01.pdf
3. \\work\share drive\example\new test 01.doc
4. \\work\share drive\example\today 01.doc
5. \\work\share drive\example\today 02.doc
etc.

and in column B, I created a drop-down menu of a list of printers in each row

2. network printer 01
3. network printer 02
4. network printer 02
5. network printer 02

after selecting the relevant printers for each file, what I need now is a macro that will...

send the actual files using locations in column A but send the file to the printers selected in column B cells

it's important though that the printing sequence sent to the printers follows the list in column A.... so the name of the file in cells 02, 03, 04, 05...etc are printed in that order so as not to cause an issue with the physical print orders coming out of the printer. I have read posts that have mentioned if not done correctly, excel can print jobs in a random order or something like that.

I'm not very good at writing code, I can edit code I find on the internet as long as it's not too complex and is close enough to what I need, however after searching for so long, I am stuck, and need some help.

many thanks in advance?

 

[Burrgogi]

I'm puzzled why you would post this kind of question on an Excel forum considering those are MS Word documents. As far as I know, Excel cannot open or process Word documents.

 

[Trevor G]

You could look to record a macro or several to print to a specific printer which should give you the code you need and if you go through selecting different printers you'll get the printer names etc.

You could then post the code to your thread to get the assistance you need. Please remember to use the VBA code tags on the toolbar when posting code as it helps read the code.

There would always be questions like what happens if the printer is off line, what happens about printer queues.

 

[Registered55]

Hi, thank you all so much for responding, i will share a little more and give a screen shot of where i'm currently at.

so in fact the above is actually the start of a project that came from a proof of concept first, which was, can i use excel to print types of files (in this work environment, there are a lot of systems in place that make it difficult)

so i found this macro:

Sub BatchPrintWordDocuments()
  Dim objWordApplication As New Word.Application
  Dim strFile As String
  Dim strFolder As String

  strFolder = InputBox("Enter the folder address", "Folder Address", "For example:E:\test word\test\")
  strFile = Dir(strFolder & "*.doc*", vbNormal)
 
  While strFile <> ""
    With objWordApplication
      .Documents.Open (strFolder & strFile)
      .ActiveDocument.PrintOut
      .ActiveDocument.Close
    End With
    strFile = Dir()
  Wend
 
  Set objWordApplication = Nothing
 
End Sub

the above works great, but it does have some drawbacks, it prints to the systems default printer only, which is fine for some of the work i need to print, but there are other types of prints that no so good.

at this point i realized, excel can print other types of files....following from this i started the above print project.

I will share a print out what i currently have that may make it more clear.

 

[Registered55]

so the macro i posted just needs to be enhanced really allowing pdf to also be included, but use the file locations from column A and sending those file to the printer locations in column B

hopefully this makes more sense now?

 

[Trevor G]

I am not on a network. An example of printing something is shown below which can be adapted to point to a cell for the file and also a cell for the specific printer. I can't test this code for network printers. You could then adapt this for each document in each cell/row

Sub PrintATestSheetorDocument()
Dim myPrinter As String
Dim Printer_name As String 'Declare a name
'You can specify the printer name
Printer_name = "Canon Inkjet MP495 series (Copy 1)" 'Adapt to cell reference
myPrinter = Application.ActivePrinter
'I am using the activesheet as a tester this can be changed to cell reference
ActiveSheet.PrintOut Preview:=False, ActivePrinter:=Printer_name, PrintToFile:=True, PrToFileName:=PSFileName
Application.ActivePrinter = myPrinter
End Sub