+ Reply to Thread
Results 1 to 12 of 12

VBA to Print Hyperlinked PDF based on Cell Reference

  1. #1
    Registered User
    Join Date
    07-25-2019
    Location
    San Diego, CA
    MS-Off Ver
    16.0.11727.20222
    Posts
    34

    VBA to Print Hyperlinked PDF based on Cell Reference

    Hey all!

    I have an ambitious project that far exceeds my VBA knowledge and I'm not even sure is possible.

    I have a log in which I record samples of various materials. These materials have item #s assigned to them. Each material also has a unique specification sheet saved as a PDF, which needs to be printed every time a sample is taken. I've made a table of each spec sheet's file path to create hyperlinks, allowing us to open and print the files. However, we collect upwards of 20 samples per day, and I'm hoping to save more time by automatically printing the hyperlinks of selected files without having to open and print them one at a time.

    The sample log is located on Sheet 1, and would use A2-A76 as a reference for vlookup on the hyperlink table, located on Sheet "Hyperlinks" A2:D1455, though the list will definitely grow, and the file paths are listed in column D. I'm hoping to put an "X" in column K on Sheet 1 to indicate which rows need the spec sheets printed. I think some combination of If, Then, Vlookup and a Loop would work, assuming printing a PDF from Excel is even possible, but I haven't had much luck. Can anyone provide some guidance/starting points?

    Thank you!
    Last edited by hindotmo12; 06-25-2020 at 11:42 AM.

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    2007
    Posts
    3,427

    Re: VBA to Print Hyperlinked PDF based on Cell Reference

    Hi hindotmo12,

    Try this (just change the names of the relevant sheets where I've marked the line 'Change to suit' if required):

    Please Login or Register  to view this content.
    Regards,

    Robert
    Last edited by Trebor76; 06-16-2020 at 08:05 PM.
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  3. #3
    Registered User
    Join Date
    07-25-2019
    Location
    San Diego, CA
    MS-Off Ver
    16.0.11727.20222
    Posts
    34

    Re: VBA to Print Hyperlinked PDF based on Cell Reference

    Hi Robert,

    Thank you for getting back to me, I appreciate it.

    I'm getting a Compile Error that says "The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute."

    I googled the error and found this:

    https://docs.microsoft.com/en-us/pre...ting-vba-macro

    It tells me to ignore the code in the 64-bit version, however, I am doing this at work and, due to firewalls, am unable to get the 64-bit downloader without IT permission. I'm going to contact them to see if I can get it updated, but won't be able to test it til that is done.

    I'll update here when I am able to do so.

    Thanks again!

  4. #4
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    2007
    Posts
    3,427

    Re: VBA to Print Hyperlinked PDF based on Cell Reference

    Does this do the job?

    Please Login or Register  to view this content.
    Last edited by Trebor76; 06-17-2020 at 08:45 PM.

  5. #5
    Registered User
    Join Date
    07-25-2019
    Location
    San Diego, CA
    MS-Off Ver
    16.0.11727.20222
    Posts
    34

    Re: VBA to Print Hyperlinked PDF based on Cell Reference

    Hi Trebor76,

    I'm getting "Compile Error: Sub or Function not defined" at Call apiShellExecute.

    Also, I've never seen the # used in VBA before - Am I correct in assuming those were instructions meant for me and not intended as code? Like, use the Private Declare under #If VBA7 if I have VBA7 or the Private Declare under #Else if I don't? And I would delete the # lines?

    I tried both, the #Else gave me the same error I mentioned in my response to Robert, and the second gave me the Compile Error code mentioned above.

    Unfortunately I haven't been able to get permission from IT to download the 64-bit version, so I'm still waiting on that.

    Thanks again!

  6. #6
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    2007
    Posts
    3,427

    Re: VBA to Print Hyperlinked PDF based on Cell Reference

    I'm getting "Compile Error: Sub or Function not defined" at Call apiShellExecute.
    Remove the text "api" from this line...

    Please Login or Register  to view this content.
    ...so it reads just like this:

    Please Login or Register  to view this content.
    Also, I've never seen the # used in VBA before - Am I correct in assuming those were instructions meant for me and not intended as code?
    No, they are part of the code. To be honest I've not seen the hash used before like that but that's how it is on the website I've noted in the comments for that block of code.

    Another alternative is to print the PDF's using "SendKeys" but they are "clunky" in my humble opinion (NumLock is switched off each time they're used for example).

    is the strPath variable being set correctly?
    Last edited by Trebor76; 06-18-2020 at 08:42 PM.

  7. #7
    Registered User
    Join Date
    07-25-2019
    Location
    San Diego, CA
    MS-Off Ver
    16.0.11727.20222
    Posts
    34

    Re: VBA to Print Hyperlinked PDF based on Cell Reference

    Hi Trebor,

    So, having left the code as is, and deleting only the api from that line, I no longer get an error code, but nothing prints, either. The strPath looks correct; pulling the reference from Column A on Sheet 1 and referencing Sheet "Hyperlinks" and printing the file path in Column D, so that doesn't appear to be an issue.

    The only oddity that I notice is that this line:

    Please Login or Register  to view this content.
    Is red, but since there isn't an error with it, I assume it's the same thing mentioned in the source:
    https://stackoverflow.com/questions/...64-bit-windows

    Which states "Note that in the old VB6, PtrSafe isn't even a valid command, so it'll appear in red as though you have a compile error, but it won't actually ever give an error because the compiler will skip the first part of the if block."

    However, the red line he is referencing, as seen in the image he posted, is actually the #If statement:
    Please Login or Register  to view this content.
    And not the #Else statement, as it appears in my code, but since it's not throwing an error, I assume it's red for the same reason.

  8. #8
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    2007
    Posts
    3,427

    Re: VBA to Print Hyperlinked PDF based on Cell Reference

    Try this which uses ShellCommand to print the PDF's and the task bar to close it if it wasn't originally open:

    Please Login or Register  to view this content.
    Regards,

    Robert

  9. #9
    Registered User
    Join Date
    07-25-2019
    Location
    San Diego, CA
    MS-Off Ver
    16.0.11727.20222
    Posts
    34

    Re: VBA to Print Hyperlinked PDF based on Cell Reference

    Hi Robert,

    Sorry for the delayed response.

    I'm getting a "Run-time error '91': Object variable or With block variable not set." at this line:

    Please Login or Register  to view this content.
    I haven't had much luck finding solutions on my end either. I've attached the file if you'd like to take a look; there's no sensitive information on it, so my boss is fine with it.

    Thanks again for your help!
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    2007
    Posts
    3,427

    Re: VBA to Print Hyperlinked PDF based on Cell Reference

    I thought only the path was in Col. D of the Hyperlinks tab (my bad).

    As long as the full path in Col. D of the Hyperlinks tab exists exactly as listed as there's at least one "x" in Col. K of Sheet1 this should do the job:

    Please Login or Register  to view this content.
    Sheet1 is protected so I can't test thoroughly.

    Regards,

    Robert

  11. #11
    Registered User
    Join Date
    07-25-2019
    Location
    San Diego, CA
    MS-Off Ver
    16.0.11727.20222
    Posts
    34

    Re: VBA to Print Hyperlinked PDF based on Cell Reference

    Hi Robert,

    That did it! Thank you so much! You've made my co-worker's life miles easier. I'm going to study the heck out of it so I can figure out how it works. I really appreciate your help!

  12. #12
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    2007
    Posts
    3,427

    Re: VBA to Print Hyperlinked PDF based on Cell Reference

    You're welcome. If you could mark the thread as solved it would be appreciated.

    Regards,

    Robert

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Print out a file from hyperlinked cell
    By plans in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-02-2015, 12:44 PM
  2. Print All Hyperlinked Files in Specified Range
    By tbc76 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-21-2012, 11:17 AM
  3. Print a variable range based on cell reference
    By aondrusek in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-09-2012, 09:27 PM
  4. Print content of hyperlinked PDFs
    By banyard2000 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-13-2012, 03:06 AM
  5. Print a Hyperlinked HTML file
    By LizS in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-28-2006, 06:25 AM
  6. How do I print a hyperlinked document from a cell in excel
    By Queenshero in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-29-2005, 03:06 AM
  7. [SOLVED] How do I print hyperlinked documents from a cell in excel
    By Queenshero in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-29-2005, 03:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1