VBA to Print Hyperlinked PDF based on Cell Reference

[hindotmo12]

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!

[Trebor76]

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

[hindotmo12]

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!

[Trebor76]

Does this do the job?

Please Login or Register  to view this content.

[hindotmo12]

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!

[Trebor76]

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?

[hindotmo12]

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.

[Trebor76]

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

[hindotmo12]

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!

[Trebor76]

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

[hindotmo12]

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!

[Trebor76]

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

Regards,

Robert