Often, we require to search many important websites. But to do this, you need to make a .doc file or .txt file including website lists, and search the file copying and pasting every time on the web browser. But the question is, Do you guys have enough time to recall a company name and search by typing the website every time? On the contrary, searching the website should be your mug of tea. Just making a pdf with hyperlinks from Excel export can be your lifesaver.
You can simply make an Excel file inserting all the company data including the web address. Hyperlinking the data may also help to jump to the website directly. However the issue is, the Excel file is editable so that one may lose data during searching from the Excel file. In this case, one may export the Excel file to a PDF file with hyperlinks to save time and reduce reluctance. In this article, we are going to learn to export an Excel file to a PDF file with hyperlinks.
📁 Download Excel File
To practice please download the Excel file from here:
Learn 3 easy techniques to export an Excel file to a Pdf file having hyperlinks. Following that we consider a dataset named Company Details containing Company along with its Logo, and Website link. In the dataset, there are 4 companies named Excelden, Exceldemy, Officewheel, and Outershell. We will use Save as tool, the HYPERLINK function, and VBA Macro to export an Excel file to a PDF file. So, why waste time? Let’s get started.
The easiest way to export an Excel file to a PDF file with hyperlinks is to create a dataset for Excel and then go to the Save option to publish it as a PDF file. Stay tuned with us to explore the steps.
⬇️⬇️ STEPS ⬇️⬇️
- First, go to the File menu of the workbook.
- Then click on the Save As option.
- Next, select This PC to store the file in the computer.
- After that, write the name of the file in the File name cell.
- Further, select the PDF option from the drop-down list of Save as type cell and hit the Save button.
- Lastly, the PDF opens with the hyperlinks after publishing from the Excel file.
2. Activate HYPERLINK Formula in Excel and Convert Them to PDF
Using a simple formula containing the HYPERLINK function one can activate hyperlinks and convert the dataset to a PDF file without any hustle. Please check out to learn the use of the HYPERLINK function.
⬇️⬇️ STEPS ⬇️⬇️
- Primarily, select the blank cell i.e. E6.
- Then type the following formula in the cell E6 including the HYPERLINK function.
- Here clicking on the EXCELDEN cell, the HYPERLINK function redirects the web address and brings you to the website.
- Similarly, type formulas all the company so that one may quickly jump to the website only by clicking the cell.
- Finally, Export as a PDF file following the similar method mentioned earlier in this article.
📕 Read More: 5 Handy Ways to Convert Text to Hyperlink in Excel
Using VBA code we can easily create a PDF file from MS Excel file in Excel. With the help of the Developer option, you can generate a program on your own. Please follow the steps below.
⬇️⬇️ STEPS ⬇️⬇️
- Check first if your Developer option is available or not.
- Secondly, to enable the Developer option, Right-Click on the mouse at the Top Ribbon and select Customize the Ribbon.
- Thirdly, click on Developer and click on the OK button.
- Now Developer mode is on.
- Then, click on the Visual Basic option.
- Next, to insert the VBA code, click on Module from the Insert menu.
- Now, insert the code in the Module of the Microsoft Visual Basic for Applications box.
Sub ExceltoPDF() ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "E:\tanvir\Job\Softeko\Article\305_Export Excel to Pdf with Hyperlinks\Export Excel to Pdf with Hyperlinks Using VBA.pdf" _ , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _ :=False, OpenAfterPublish:=True End Sub
🔨 Code Breakdown
👉 Firstly, ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF means the current sheet is directed to save as a PDF file in the following file location along with the file named Export Excel to Pdf with Hyperlinks Using VBA.pdf.
👉 Secondly, in the VBA code, Filename:= _
“E:\tanvir\Job\Softeko\Article\305_Export Excel to Pdf with Hyperlinks\Export Excel to Pdf with Hyperlinks Using VBA.pdf” _ is the file directory in the computer.
👉 Thirdly, Quality:=xlQualityStandard dictates to optimize for Standard quality.
👉 Fourthly, IncludeDocProperties:=True means document properties included during saving.
👉 Fifthly, IgnorePrintAreas _:=False delineates to set a print area automatically.
👉 Finally, OpenAfterPublish:=True commands to open the PDF file after publishing as PDF.
- Therefore, a PDF file is published including hyperlinks.
Creating a dynamic hyperlink you can jump to any worksheet in a workbook to get specific data. The use of the HYPERLINK function helps to make the process real. Please check out the steps below.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, navigate the blank cell i.e. B6.
- Then go to the Data menu from the Top Ribbon and expand the drop-down list of the Data Tools feature.
- Next, select the Data Validation option from the drop-down list of the Data Validation feature.
- Thus a box named Data Validation appears.
- After that, go to Settings and Navigate to the drop-down list of Allow to select List.
- Further, select range $B$6:$B$9 from the Dataset worksheet and hit the OK button.
- Finally, a drop-down box beside the B6 cell appears and you can select any company from the list.
- However, to link with the company and find the details insert the following formula containing the HYPERLINK function in the C6 cell under the Hyperlink heading.
=HYPERLINK(“#”&”Dataset!B”&(MATCH(B6,Dataset!B6:B9,0)+5),”Click to get Data”)
🔨 Formula Breakdown
👉 # dictates that the worksheet exists in the same workbook.
👉 Dataset!B delineates the B column along with the sheet name.
👉 From (MATCH(B6,Dataset!B6:B9,0)+5); the MATCH function finds the similar data of B6 in range B6:B9 of Dataset sheet. In addition, 5 were added to count from the 6th row of the Dataset sheet.
👉 After that HYPERLINK function types the Click to get Data and selects to jump to the data directly.
- Now, clicking the Click to get Data option you will go to the B8 cell named Officewheel in the Dataset sheet.
📄 Important Notes
🖊️ Enable the Developer menu first to execute VBA code.
🖊️ Use the Range along with the worksheet name carefully during making the drop-down list as well as a dynamic hyperlink in a cell.
📝 Takeaway from This Article
📌 Export Excel worksheet to PDF file including hyperlinks.
📌 Use of the HYPERLINK function to make static hyperlinks in Excel and export to PDF file.
📌 VBA macro to save as PDF file.
📌 HYPERLINK function to create Dynamic hyperlinks to get specific data from other worksheets in Excel.
In this article, we learn to export Excel data including hyperlinks to a PDF file. I hope you enjoyed your learning. Any suggestions, as well as queries, are appreciated. Don’t hesitate to leave your thoughts in the comment section. For better understanding and new knowledge, don’t forget to visit www.ExcelDen.com.