Print Batching

[RossCarroll]

I have been using Excel for a long time but am very new macros and would consider myself a novice at coding even.

I have been trying to write a macro to basically batch print multiple documents available in web hyperlinks. I'm not even sure if what I am trying to do is within Excel's capabilities.

In my line of work there is a plethora of H&S documentation that requires to be regularly printed. What documentation required is dependent on the specifics of the given project. I figure that if I created a spreadsheet with a hyperlinked list of all document templates, I could develop a system to efficiently print selected documents without manually opening each document and doing so traditionally.

How I would envision this macro working is by looking at hyperlinks in Column A. For each hyperlink, the macro would open the document, print it and close the document. Number of copies would be dictated by numerical value in Column B (i.e. no value = 0 copies, 1 = 1 copy, 10 = 10 copies etc.) The hyperlink present on each line would only open in the macro if Column B > 0.

Am I silly for even trying to do this? I think such a system could be of great value in my workplace and would be appreciated by colleagues and management. I am slowly accepting defeat though as coding this is clearly beyond my capability and AI software has proved to be more infuriating that helpful in this circumstance. I have attached an image of the template I have made to show visually how the spreadsheet would look.

Any thoughts, insight or assistance would be greatly appreciated!

 

[John_w]

This should be possible, depending on the type of document (PDF, Excel workbook, Word document, etc) because an Excel macro can run the default Print application for each type of file, as long as that Print application prints the file without any user intervention (i.e. it doesn't display a dialogue). The default Print application is what is run when you right-click a file in File Explorer and choose the 'Print' option from the context menu; a macro can do the same using the "ShellExecute" API function.

Also, what type of hyperlinks are they? =HYPERLINK formulas or inserted via the Insert Hyperlink dialogue?

 

[RossCarroll]

This should be possible, depending on the type of document (PDF, Excel workbook, Word document, etc) because an Excel macro can run the default Print application for each type of file, as long as that Print application prints the file without any user intervention (i.e. it doesn't display a dialogue). The default Print application is what is run when you right-click a file in File Explorer and choose the 'Print' option from the context menu; a macro can do the same using the "ShellExecute" API function.

Also, what type of hyperlinks are they? =HYPERLINK formulas or inserted via the Insert Hyperlink dialogue?

Hi John,

Thank you for taking the time to offer some insight.

The bulk of the documents are PDF. Possibly a few Word and Excel docs here and there.

The style of hyperlink is the typical insert method. I didn't even know hyperlinks could be achieved via formula!

Kind regards

Ross

 

[John_w]

See if this macro works for you. It loops through the visible cells in column A (your screenshot indicates that rows could be filtered) and if the cell contains a hyperlink it prints that file on the default printer for the number of copies in column C.

Option Explicit
 
#If VBA7 Then
    Private Declare PtrSafe Function ShellExecute Lib "shell32" Alias "ShellExecuteA" (ByVal hwnd As LongPtr, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#Else
    Private Declare Function ShellExecute Lib "shell32" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#End If

Private Const SW_HIDE As Long = 0&


Public Sub Print_Hyperlinked_Files()

    Dim columnAcells As Range, docCell As Range
    Dim n As Long
   
    'Get column A cells (A1:A<last data row>) on active sheet
   
    With ActiveSheet
        Set columnAcells = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
    End With
   
    'Loop through visible column A cells
   
    For Each docCell In columnAcells.SpecialCells(xlCellTypeVisible)
        If docCell.Hyperlinks.Count > 0 Then
            'Print number of copies specified in column C
            For n = 1 To docCell.Offset(, 2).Value
                ShellExecute_Print_File docCell.Hyperlinks(1).Address
            Next
        End If
    Next
   
End Sub


Private Sub ShellExecute_Print_File(file As String, Optional PrinterName As String)
    If PrinterName = "" Then
        ShellExecute Application.hwnd, "Print", file, vbNullString, 0&, SW_HIDE
    Else
        ShellExecute Application.hwnd, "PrintTo", file, Chr(34) & PrinterName & Chr(34), 0&, SW_HIDE
    End If
End Sub

If you want to print the files on a specific printer then specify the Windows printer name as the 2nd argument here:

ShellExecute_Print_File docCell.Hyperlinks(1).Address, "Windows Printer Name"

Note that when printing PDFs the PDF application (Acrobat on my machine) stays open and you have to close it manually at the end. Closing this window can be automated but more code is needed.

PS - I suggest pausing the printer whilst testing to avoid wasting paper, etc.

 

[RossCarroll]

See if this macro works for you. It loops through the visible cells in column A (your screenshot indicates that rows could be filtered) and if the cell contains a hyperlink it prints that file on the default printer for the number of copies in column C.

Option Explicit
 
#If VBA7 Then
    Private Declare PtrSafe Function ShellExecute Lib "shell32" Alias "ShellExecuteA" (ByVal hwnd As LongPtr, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#Else
    Private Declare Function ShellExecute Lib "shell32" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#End If

Private Const SW_HIDE As Long = 0&


Public Sub Print_Hyperlinked_Files()

    Dim columnAcells As Range, docCell As Range
    Dim n As Long
  
    'Get column A cells (A1:A<last data row>) on active sheet
  
    With ActiveSheet
        Set columnAcells = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
    End With
  
    'Loop through visible column A cells
  
    For Each docCell In columnAcells.SpecialCells(xlCellTypeVisible)
        If docCell.Hyperlinks.Count > 0 Then
            'Print number of copies specified in column C
            For n = 1 To docCell.Offset(, 2).Value
                ShellExecute_Print_File docCell.Hyperlinks(1).Address
            Next
        End If
    Next
  
End Sub


Private Sub ShellExecute_Print_File(file As String, Optional PrinterName As String)
    If PrinterName = "" Then
        ShellExecute Application.hwnd, "Print", file, vbNullString, 0&, SW_HIDE
    Else
        ShellExecute Application.hwnd, "PrintTo", file, Chr(34) & PrinterName & Chr(34), 0&, SW_HIDE
    End If
End Sub

If you want to print the files on a specific printer then specify the Windows printer name as the 2nd argument here:

ShellExecute_Print_File docCell.Hyperlinks(1).Address, "Windows Printer Name"

Note that when printing PDFs the PDF application (Acrobat on my machine) stays open and you have to close it manually at the end. Closing this window can be automated but more code is needed.

PS - I suggest pausing the printer whilst testing to avoid wasting paper, etc.

Hi John,

I ran a test of this macro but there seems to be a problem with line 6 unfortunately, see attached.

Ross

 

[John_w]

I don't get that compiler error on my 64-bit Excel. It suggests the compiler is using the #Else part instead of the #If VBA7 part, which does have the PtrSafe attribute which the error complains about.

Try replacing:

#If VBA7 Then
    Private Declare PtrSafe Function ShellExecute Lib "shell32" Alias "ShellExecuteA" (ByVal hwnd As LongPtr, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#Else
    Private Declare Function ShellExecute Lib "shell32" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#End If

with:

Private Declare PtrSafe Function ShellExecute Lib "shell32" Alias "ShellExecuteA" (ByVal hwnd As LongPtr, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As LongPtr

I've corrected the function's return data type from Long to LongPtr.

 

[RossCarroll]

Hi John,

I gave this a bash but the macro unfortunately doesn't actually seem to do anything once I run it.

Ross

 

[John_w]

The default Print application is what is run when you right-click a file in File Explorer and choose the 'Print' option from the context menu

Can you print the files manually with the above method? If so, then VBA should be able to print using ShellExecute.

Try changing the "PrintTo" to "Print" in this line:

        ShellExecute Application.hwnd, "PrintTo", file, Chr(34) & PrinterName & Chr(34), 0&, SW_HIDE