Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Nov 2015
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Question Print list of PDFs from URLs in Excel columns using Macros or VBA script?

    I have a list of PDFs in one column (Column H) and a list of PDFs in another column (Column I) of an Excel sheet. For example, I need the pdfs in H2 and I2 to print together as these two PDFs need to be in the printed stack together and then H3 and I3 pdfs to print together and on and on. Column H and I starting at row 2 and not sure how many rows I will have as each list is different. These are non hyperlinked URLs typed out in each cell of one column. Looking for a macros or VBA script that I can run anytime I open this Excel workbook that will automatically open the URLs in that column and print them out. Then close the URLs. Does not need to close workbook. I have racked my mind for days over this and just cant seem to get it. Any help would be much appreciated. I hope I explained myself properly. Thanks in advance.

    Excel 2013

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Just to make sure, these "Links" are web links and not paths to pdf's in a local or networked folder. Do you want to view them first or do you just want them printed out? Do you want to select the row to print or do you want each pair in all the rows to print simultaneously?

    Maud

  3. #3
    New Lounger
    Join Date
    Nov 2015
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Just to make sure, these "Links" are web links and not paths to pdf's in a local or networked folder. Do you want to view them first or do you just want them printed out? Do you want to select the row to print or do you want each pair in all the rows to print simultaneously?

    Maud

    They are web URLs but they are not hyperlinked. I do not need to view them. Ideally, it would print H2 and I2 together (together but separate pages),then H3 and I3 together, H4 and I4 together, etc. No selection just print any pdfs in those columns H and I. H2 and I2, H3 and I3, etc. The amount of rows will vary depending on the list I receive. I could have 6 rows of pdfs or 100 rows. The only columns that need to print are the H and I columns and Row 1 is a header row. Not trying to ramble just wanting to make sure I answer your question. Thank you!
    Last edited by Injection_Mold; 2015-11-07 at 13:01.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    IM,

    Place the following code in a standard module. It will download and print the PDFs in each row starting with column H followed By Column I. You will need to change the path to an existing folder that the files are downloaded to:

    ChDir ("C:\Users\Maudibe\Desktop")

    The code will place the "\" at the end of the path. Make sure that the path remains inside double quotes.

    HTH,
    Maud

    Code:
    Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
        "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal _
        szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
    
    Private Declare Function ShellExecute Lib "shell32.dll" 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
    
    Const SW_HIDE As Long = 0
        
    Sub DL_PRNT_PDFS()
    '---------------------------
    'DECLARE AND SET VARIABLES
        Dim PDF_URL As String, PDF_FileName As String
        Dim I As Integer, LastRow As Integer, x As Integer
        Dim CDir As String
    '---------------------------
    'REMEMBER OF PATH AND SET NEW PATH FOR DOWNLOAD
        CDir = CurDir
        ChDir ("C:\Users\Maudibe\Desktop")
        Path = CurDir & "\"
    '---------------------------
    'CYCLE THROUGH ROWS: DOWNLOAD AND PRINT
        LastRow = ActiveSheet.Cells(Rows.Count, "H").End(xlUp).Row
        For I = 2 To LastRow
    '---------------------------
    'COL H
            PDF_URL = Cells(I, "H")
            s = Split(PDF_URL, "/")
            PDF_FileName = s(UBound(s))
            x = URLDownloadToFile(0, PDF_URL, PDF_FileName, 0, 0)
            ShellExecute Application.hWnd, "Print", Path & PDF_FileName, 0&, 0&, SW_HIDE
    '---------------------------
    'COL I
            PDF_URL = Cells(I, "I")
            s = Split(PDF_URL, "/")
            PDF_FileName = s(UBound(s))
            x = URLDownloadToFile(0, PDF_URL, PDF_FileName, 0, 0)
            ShellExecute Application.hWnd, "Print", Path & PDF_FileName, 0&, 0&, SW_HIDE
        Next I
    '---------------------------
    'RETURN CURRENT PATH TO ORIGINAL PATH
        ChDir (CDir)
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2015-11-07 at 19:00.

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    Injection_Mold (2015-11-07)

  6. #5
    New Lounger
    Join Date
    Nov 2015
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts
    This works great! Thank you! A few questions: Is there a way to have it close Adobe after print? Also, can it delete the downloaded files after print? Can I add a button to Excel to run this script?

  7. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    IM,

    Here is the revised code that will download the PDFs to an existing dedicated folder. After printing, Adobe will close and the PDF files in the folder will be deleted.

    Change the path in the following lines to a dedicated folder:

    ChDir ("C:\Users\Maudibe\Desktop\PDFs")

    Kill "C:\Users\Maudibe\Desktop\PDFs\*.pdf"

    See the attached file with a button to run the code.

    HTH,
    Ted

    Code:
    Sub DL_PRNT_PDFS()
    '---------------------------
    'DECLARE AND SET VARIABLES
        Dim strTerminateThis As String
        Dim objWMIcimv2 As Object
        Dim objProcess As Object
        Dim objList As Object
        Dim intError As Integer
        Dim PDF_URL As String, PDF_FileName As String
        Dim I As Integer, LastRow As Integer, x As Integer
        Dim CDir As String
    '---------------------------
    'REMEMBER OF PATH AND SET NEW PATH FOR DOWNLOAD
        CDir = CurDir
        ChDir ("C:\Users\Maudibe\Desktop\PDFs")
        Path = CurDir & "\"
    '---------------------------
    'CYCLE THROUGH ROWS: DOWNLOAD AND PRINT
        LastRow = ActiveSheet.Cells(Rows.Count, "H").End(xlUp).Row
        For I = 2 To LastRow
    '---------------------------
    'COL H
            PDF_URL = Cells(I, "H")
            s = Split(PDF_URL, "/")
            PDF_FileName = s(UBound(s))
            x = URLDownloadToFile(0, PDF_URL, PDF_FileName, 0, 0)
            ShellExecute Application.hWnd, "Print", Path & PDF_FileName, 0&, 0&, SW_HIDE
    '---------------------------
    'COL I
            PDF_URL = Cells(I, "I")
            s = Split(PDF_URL, "/")
            PDF_FileName = s(UBound(s))
            x = URLDownloadToFile(0, PDF_URL, PDF_FileName, 0, 0)
            ShellExecute Application.hWnd, "Print", Path & PDF_FileName, 0&, 0&, SW_HIDE
        Next I
    '---------------------------
    'RETURN CURRENT PATH TO ORIGINAL PATH
        ChDir (CDir)
    '---------------------------------------------
    'CLOSE AND DELETE FILES
        strTerminateThis = "AcroRd32.exe"
        Set objWMIcimv2 = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\.\root\cimv2")
        Set objList = objWMIcimv2.ExecQuery("select * from win32_process where name='" & strTerminateThis & "'")
        For Each objProcess In objList
            On Error Resume Next
            intError = objProcess.Terminate
            On Error GoTo 0
        Next
        On Error Resume Next
        Kill "C:\Users\Maudibe\Desktop\PDFs\*.pdf"
        On Error GoTo 0
    End Sub
    Attached Files Attached Files

  8. The Following User Says Thank You to Maudibe For This Useful Post:

    Injection_Mold (2015-11-09)

  9. #7
    New Lounger
    Join Date
    Nov 2015
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I am getting a "Run-time error '9': Subscript out of range" error. When I click 'Debug' it takes me to: 'PDF_FileName = s(UBound(s))'. I believe this is because some of my rows are blank. Is there a way to have it skip blank rows and continue on? The first test file I was using for this did not have blank rows so there was no error the first time I tested. Today I am testing with a new file and it has a few entries that are blank in column H and I. Is there a way to skip rows that are blank? The rows will only be blank in columns H and I. Thank you!
    Last edited by Injection_Mold; 2015-11-09 at 11:49. Reason: added a comment

  10. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    I_M,

    You're right, empty cells would cause an error. This version has a couple of IF statements that test for empty cells and bypasses the process for those cells if they are.

    HTH,
    Maud
    Attached Files Attached Files

  11. The Following User Says Thank You to Maudibe For This Useful Post:

    Injection_Mold (2015-11-10)

  12. #9
    New Lounger
    Join Date
    Nov 2015
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you for all of your help. Can I just remove a line of code to have it only print Column H or just Column I?

  13. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    I_M

    Instead of altering the code by removing lines of code, here is some revisions by adding code that allows the user to select between 3 option buttons that will print column H (left), column I (right), or both columns (middle). This will enable you to have your cake and eat it too.

    Button.png

    HTH,
    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2015-11-24 at 21:27.

  14. #11
    New Lounger
    Join Date
    Nov 2015
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts
    This is great! You are awesome! One more question and I will leave you alone as you have done such wonderful work here. Anyway to put the button in the ribbon? That way when they open other Excel workbooks the button is not in the way?

  15. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    That way when they open other Excel workbooks the button is not in the way?
    I_M,

    Not sure why the button would be in the way if they open other workbooks. The button is located on one sheet of one specific workbook. Like the sheet that it resides on, if you open another workbook which becomes the forefront, the button will be hidden underneath. In other words, wherever the sheet goes, so will the button.

    You could put this macro on the Quick Access Toolbar (QAT) in Excel but then it would remain present for every workbook that you open. If you accidently click on it, the workbook with the code will unintentionally open. Additionally, you would need to setup the macro button on the QAT of each computer that the workbook may run on.

    If what you want is to do is to move the button off of the sheet then you have three better approaches:
    1. Move the controls to a corner of the worksheet out of the way from the working area.
    2. Instead of assigning the macro to a button, assign it to a keyboard shortcut (ex CTRL-Shift-z).
    3. The other method and the one that I would prefer, is to put the button and the option button set on a small form (the size of the controls as they are now) and set the forms modal property to false. This way you have the option to close the form or keep it open but have the ability to move it out of the way anywhere you like on the screen while you continue to work elsewhere.

    Let me know what you would like to do
    Maud

  16. #13
    New Lounger
    Join Date
    Nov 2015
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I could be using the last xlsm wrong. It is not downloading and printing anything. How should I be using it? The way I am testing it is: I am pulling up Injection_Mold_Revised3.xlsm and also pulling up the workbook I receive. I am then copying data from H and I in my workbook to the H and I columns in Injection_Mold_Revised3.xlsm. Then I try to print and nothing happens. I have a feeling I am doing something wrong here. Do I even need to copy data over? Just confused as to how to use it.
    Last edited by Injection_Mold; 2015-11-17 at 17:04. Reason: added verbage

  17. #14
    New Lounger
    Join Date
    Nov 2015
    Posts
    8
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I cant seem to get any of these to work now. It doesn't error out either. I am lost here.

  18. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Working on it!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •