Macro to save excel worksheets as pdfs and change hyperlinks to work in pdf format

[crnadeau4]

Hi everyone,

I am looking for a macro that will enable me to do the following:

1) Change the hyperlinks (functions) in a certain column to non-function hyperlinks (so that they will be compatible and clickable in pdf format)
2) Hide certain columns before the excel worksheets are saved as pdfs
3) And finally save each of the worksheets (in the workbook) as pdf files in a certain file folder location in my drive
4) In addition: when the pdf files save, I would like the file to be titled according the middle content of the header, along with the revision number located in the bottom left-most corner of the footer.

Just to be clear (because my computer lingo is pretty bad) in step 1: I have a huge amount of hyperlink formulas/functions that open the corresponding file when clicked. These hyperlinks will work in excel, but I have found that they will NOT work when converted to pdf.
Therefore I am trying to convert all of these at once (with a macro) into hyperlinks that will still link to the same location but will work when saved as a pdf.

Please let me know if I am not being clear enough and please somebody - try to help me !

Thanks a lot :]

[TMS]

You're using a cell value (from column C) to get the displayed value. Clearly, this means nothing in the .pdf file. However, the HYPERLINK is what gives you the link from the cell to the .pdf file.

I think you'd need to replace the cell reference, for example, C2, with something that the .pdf file will recognise as a link.

I think this would look something like: =HYPERLINK(AJ2 & C2 & " " & "Rev" & " " & I2 & ".pdf", "<file:\\" & AJ2 & C2 & " " & "Rev" & " " & I2 & ".pdf" & ">")

In this scenario, you have the hyperlink that works in Excel and a fully qualified file link which I *think" will be recognised as a hyperlink in a .pdf file. Maybe


Regards, TMS

[crnadeau4]

Hmm..I think that's a really good idea, however I have tried it and the the file link that is generated by the formula still shows up in the pdf as static text. I think its the exact same problem as what I have now because even though your formula generates a recognized path it is still coming from an excel formula - therefore not understood as a "clickable" link by adobe software.

I have another question for you though (kinda of an idea to fix the problem):


First off, if you hover the cursor over any of the linked cells in excel, a box appears showing the destination of the hyperlink.
Would it be possible to have a macro that takes that destination (that has been generated by the formula) and inputs that path into a hyperlink in another column that is created by "insert>hyperlink" - as oppose to what I now have (using the actual hyperlink formula that is NOT recognized by adobe software).

Does this make sense and do you think it would be a possible solution?

I just do not want to have to individually create an "insert>hyperlink" path for each item (as I have about 1000 of them in the sensitive dataset).