VBA Open & Combine List of Hyperlinks to Saved PDF's

  • I'm a long time lurker, first time poster. I've been trying to figure this out and to be completely honest I'm not even sure that it's possible. I'm going to do my best to explain what I'm trying to accomplish and would love any advice, ideas, code to try, etc!

    I have an Excel spreadsheet that has a list of invoices on it. The invoice number is in Column A and then I have a hyperlink to the pdf invoice on my file server in Column B. I have 200+ rows and they are constantly changing so figuring out how to automate this would save so much time. I need (1) pdf document that contains all of the actual pdf files that are linked in Column B. I'm currently using a vba script to open all of the hyperlinks (only 50 at a time because that is the max it will allow me to open at once) then I'm going into Acrobat Pro and combining all open files. I do that for each batch of 50 files then combine all of the "portfolios" once I'm done to end up with my single pdf of all of the invoices on my list. The files are not named according to the invoice number, I basically batch export a large group of invoices to a zip file and use the document index to get the links to the files in my file server. I then take the list of invoices I need and run a macro to delete all rows that don't contain the invoice numbers on my list in sheet 2. All of that works fine, I'm just getting stuck at the open and combine part. It can save it if that's easier but I could also save it if it would just combine all of the files into one document.

    Any thoughts? Is it possible?

    I appreciate you taking the time to read my long post!

  • I found something online that I was able to tweak to make work, I still want to work on cleaning it up a bit and I'd like to try to fix the loop in the Combine macro to take out the actual number of rows it looks at but it runs and combines the files in the order I need them to be combined in which was the main goal! I had to also run a different macro to extract the path from the hyperlink so basically my data is now set up with the path to the files in Column A then the invoice number in Column B. Side note, I've researched and tried multiple ideas but am not able to get excel to give me the full path, it just gives me the folder and name of the file, so I used a different macro to add the beginning of the path to each cell. Not super efficient but effective. Not sure how to get around that to be honest. I tried unchecking update links on exit and tried changing the path in options to x to prompt it to give the full file path but neither of those worked.

    Then run this macro to use the function:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!