{"id":1024,"date":"2015-11-14T05:15:15","date_gmt":"2015-11-14T10:15:15","guid":{"rendered":"http:\/\/regina-whipp.com\/blog\/?p=1024"},"modified":"2016-11-05T22:24:11","modified_gmt":"2016-11-06T02:24:11","slug":"exporting-clickable-hyperlinks","status":"publish","type":"post","link":"https:\/\/regina-whipp.com\/blog\/?p=1024","title":{"rendered":"Exporting clickable Hyperlinks\u2026"},"content":{"rendered":"<p>Generally, I store the path to my file and the name of the File in two separate fields. I do this because if there is a likelihood that the database will be upsized to SQL Server the Hyperlink data type in Access is not supported and I\u2019d have to do it anyway.\u00a0 Not a big deal as in Access as I can use\u2026<\/p>\n<p><a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/office\/ff822080.aspx?f=255&amp;MSPPError=-2147217396\" target=\"_blank\">Application.FollowHyperlink<\/a>\u00a0\u00a0or\u2026<\/p>\n<p><a href=\"http:\/\/allenbrowne.com\/func-GoHyperlink.html\" target=\"_blank\">Allen Browne\u2019s GoHyperlink()<\/a><\/p>\n<p>However, it is a problem if I then want to export then to Adobe, Word or Excel and keep the links\u00a0clickable.\u00a0 To do that requires a little trickery (been wanting to use that word!).<\/p>\n<p><strong>For Adobe (.PDF) and Word (.DOC or .DOCX)&#8230;<\/strong><\/p>\n<ul>\n<li>In your Recordsource (should be a Query) for your Report create a field:<\/li>\n<\/ul>\n<pre class=\"brush: vb; title: ; notranslate\" title=\"\">\r\ntheDocumentLink: IIf(&#x5B;FieldThatHoldsThePath]&lt;&gt;&quot;&quot;,&#x5B;FieldThatHoldsNameOfDocument] &amp; &quot;#&quot; &amp; &#x5B;FieldThatHoldsThePath] &amp; &quot;#&quot;,&quot;&quot;)\r\n<\/pre>\n<ul>\n<li>On the Report add theDocumentLink Control<\/li>\n<li>In the Property Sheet for the Control in the Format tab\u2026<\/li>\n<\/ul>\n<p><strong>Is Hyperlink<\/strong> to Yes<br \/>\n<strong>Display As Hyperlink<\/strong> to If Hyperlink<\/p>\n<p>Now while in Preview of the Report you can select<strong> PDF or XPS<\/strong> or <strong>Word<\/strong> from the <strong>Data<\/strong> tab and your links will be clickable and display whatever text you put in [<span style=\"color: #339966;\">FieldThatHoldsNameOfDocument<\/span>].<\/p>\n<p>&nbsp;<\/p>\n<p><strong>For Excel (.XLS or .XLSX)&#8230;<\/strong><\/p>\n<p>I tend to use Excel Automation because I can control a lot more. I also use Templates to I don\u2019t have to worry about Formatting.\u00a0 In this Template I have two hidden fields that hold the <strong>Path<\/strong> (column B) and the <strong>Name to Display<\/strong> (column C), column F will display the <strong>Hyperlink<\/strong>.<\/p>\n<p>The first thing to is tell it how many rows are in your Recordset. Then I need to add which Row I am starting on.\u00a0 In my case I am starting at Row 5 so I need to add 4 to my Recordset.<\/p>\n<ul>\n<li>Add to your declarations<\/li>\n<\/ul>\n<p>Dim lngLastRow As Long<\/p>\n<ul>\n<li>Then under the CopyFromRecordset line\u00a0add, this is so we can get the row count to apply our formula to<\/li>\n<\/ul>\n<p>&#8216;Add the number of rows from the top the Recordset will be copied from<br \/>\nlngLastRow = rst.RecordCount + 4<\/p>\n<ul>\n<li>Now, for the Formula which you want to be sure to add after your data has pasted, so don&#8217;t add before the CopyFromRecordset\u2026<\/li>\n<\/ul>\n<pre class=\"brush: vb; title: ; notranslate\" title=\"\">\r\n'Column F to display the Hyperlink, column B the Path and column C the Text I want to Display\r\nWith xlWSh\r\nFor i = 5 To lngLastRow\r\nxlWSh.Cells(.Rows.Count, 6).End(xlUp).Offset(1, 0).Formula = &quot;=HYPERLINK(B&quot; &amp; CStr(i) &amp;&quot;, C&quot; &amp; CStr(i) &amp; &quot;)&quot;\r\nNext I\r\nEnd With\r\n<\/pre>\n<p>When you open the Excel spreadsheet your links will be clickable.<\/p>\n<div class=\"pvc_clear\"><\/div>\n<p id=\"pvc_stats_1024\" class=\"pvc_stats all  \" data-element-id=\"1024\" style=\"\"><i class=\"pvc-stats-icon medium\" aria-hidden=\"true\"><svg aria-hidden=\"true\" focusable=\"false\" data-prefix=\"far\" data-icon=\"chart-bar\" role=\"img\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 512 512\" class=\"svg-inline--fa fa-chart-bar fa-w-16 fa-2x\"><path fill=\"currentColor\" d=\"M396.8 352h22.4c6.4 0 12.8-6.4 12.8-12.8V108.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v230.4c0 6.4 6.4 12.8 12.8 12.8zm-192 0h22.4c6.4 0 12.8-6.4 12.8-12.8V140.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v198.4c0 6.4 6.4 12.8 12.8 12.8zm96 0h22.4c6.4 0 12.8-6.4 12.8-12.8V204.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v134.4c0 6.4 6.4 12.8 12.8 12.8zM496 400H48V80c0-8.84-7.16-16-16-16H16C7.16 64 0 71.16 0 80v336c0 17.67 14.33 32 32 32h464c8.84 0 16-7.16 16-16v-16c0-8.84-7.16-16-16-16zm-387.2-48h22.4c6.4 0 12.8-6.4 12.8-12.8v-70.4c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v70.4c0 6.4 6.4 12.8 12.8 12.8z\" class=\"\"><\/path><\/svg><\/i> <img loading=\"lazy\" decoding=\"async\" width=\"16\" height=\"16\" alt=\"Loading\" src=\"https:\/\/regina-whipp.com\/blog\/wp-content\/plugins\/page-views-count\/ajax-loader-2x.gif\" border=0 \/><\/p>\n<div class=\"pvc_clear\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Generally, I store the path to my file and the name of the File in two separate fields. I do this because if there is a likelihood that the database will be upsized to SQL Server the Hyperlink data type in Access is not supported and I\u2019d have to do it anyway. Not a big [&#8230;]<\/p>\n<div class=\"pvc_clear\"><\/div>\n<p id=\"pvc_stats_1024\" class=\"pvc_stats all  \" data-element-id=\"1024\" style=\"\"><i class=\"pvc-stats-icon medium\" aria-hidden=\"true\"><svg aria-hidden=\"true\" focusable=\"false\" data-prefix=\"far\" data-icon=\"chart-bar\" role=\"img\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 512 512\" class=\"svg-inline--fa fa-chart-bar fa-w-16 fa-2x\"><path fill=\"currentColor\" d=\"M396.8 352h22.4c6.4 0 12.8-6.4 12.8-12.8V108.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v230.4c0 6.4 6.4 12.8 12.8 12.8zm-192 0h22.4c6.4 0 12.8-6.4 12.8-12.8V140.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v198.4c0 6.4 6.4 12.8 12.8 12.8zm96 0h22.4c6.4 0 12.8-6.4 12.8-12.8V204.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v134.4c0 6.4 6.4 12.8 12.8 12.8zM496 400H48V80c0-8.84-7.16-16-16-16H16C7.16 64 0 71.16 0 80v336c0 17.67 14.33 32 32 32h464c8.84 0 16-7.16 16-16v-16c0-8.84-7.16-16-16-16zm-387.2-48h22.4c6.4 0 12.8-6.4 12.8-12.8v-70.4c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v70.4c0 6.4 6.4 12.8 12.8 12.8z\" class=\"\"><\/path><\/svg><\/i> <img loading=\"lazy\" decoding=\"async\" width=\"16\" height=\"16\" alt=\"Loading\" src=\"https:\/\/regina-whipp.com\/blog\/wp-content\/plugins\/page-views-count\/ajax-loader-2x.gif\" border=0 \/><\/p>\n<div class=\"pvc_clear\"><\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,63,8],"tags":[64,68],"class_list":["post-1024","post","type-post","status-publish","format-standard","hentry","category-access-tips","category-database-design","category-excel-tips","tag-access-tips","tag-hyperlinks","odd"],"_links":{"self":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1024","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1024"}],"version-history":[{"count":4,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1024\/revisions"}],"predecessor-version":[{"id":1315,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1024\/revisions\/1315"}],"wp:attachment":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1024"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1024"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1024"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}