Forum Discussion
Excel hyperlink default software to open FileName .pdf
A hyperlink to a ".pdf" type file is created in an excel cell. Clicking on this hyperlink opens the file. The problem is that the default software to do this is Adobe Acrobat not the default softwa...
To ensure that Excel uses the default software set in your Windows settings to open PDF files, you may need to check and adjust a few settings. Here are some steps you can take to resolve this issue:
Step 1: Verify Default App Settings in Windows
1. Open Default Apps Settings:
- Press Windows + I to open the Settings app.
- Go to Apps > Default apps.
2. Set Default PDF Viewer:
- Scroll down and click on Choose default apps by file type.
- Find .pdf in the list and make sure your preferred PDF viewer (e.g., Microsoft Edge, etc.) is set as the default app for this file type.
Step 2: Check File Association for Hyperlinks in Excel
Excel should respect the default file associations set in Windows. However, if it does not, you can try the following workaround:
Step 3: Repair Office Installation
Sometimes, the issue might be due to a corruption in the Office installation. Repairing Office can fix this.
1. Open Control Panel:
- Press Windows + R, type control, and press Enter.
2. Go to Programs and Features:
- Click on Programs > Programs and Features.
3. Repair Microsoft Office:
- Find Microsoft Office in the list, select it, and click on Change.
- Choose Repair and follow the on-screen instructions.
Step 4: Create a Custom VBA Function to Open PDFs
If the above methods do not work, you can create a custom VBA function in Excel to open PDFs using the default application. Here’s how you can do this:
1. Open the VBA Editor:
- Press Alt + F11 to open the Visual Basic for Applications editor.
2. Insert a New Module:
- In the VBA editor, go to Insert > Module.
3. Paste the Following Code:
Vba Code is untested backup your file.
Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" ( _
ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Sub OpenPDF(filePath As String)
ShellExecute 0, "open", filePath, "", "", 1
End Sub
4. Use the Macro to Open PDFs:
- You can then use this macro to open PDFs from a specific cell. For example:
Vba Code is untested backup your file.
Sub OpenPDFfromCell()
Dim filePath As String
filePath = ThisWorkbook.Sheets("Sheet1").Range("A1").Value ' Adjust the range as needed
OpenPDF filePath
End Sub
Step 5: Assign Macro to a Button (Optional)
1. Insert a Button:
- Go to the Developer tab, click on Insert, and choose Button (Form Control).
2. Assign Macro:
- Draw the button on your sheet, and assign the OpenPDFfromCell macro to it.
By following these steps, you should be able to ensure that PDFs open with your preferred default software when clicking hyperlinks in Excel. The VBA workaround provides a custom solution that overrides Excel’s default behavior. The text, steps and code were created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
NikolinoDE Hello and thank you for your reply. I had tried everything you suggested above Step 4 and nothing worked. So, I turned to Co-Pilot and asked what might be causing excel to ignore the default pdf setting. This led me to shell settings. I was getting outside my knowledge here so I did not proceed but posted my POST instead. I have used VBA and built functions so have some expertise in this area but, your coding is pushing me a bit. I will do some more work in this area as time allows. If I come up with anything useful I will let you know. Thanks again!
I understand that the steps provided initially did not solve the problem and that you're comfortable working with VBA. Since the issue persists even after following the initial steps, it might indeed be related to shell settings or registry settings in Windows that Excel might be relying on.
To dig deeper, let’s focus on ensuring that the VBA solution provided will work effectively and explore possible shell settings or registry modifications. Here’s a more detailed approach:
VBA Solution
First, let's refine the VBA solution to ensure it correctly opens the PDF with the default application:
- Open the VBA Editor:
- Press Alt + F11.
- Insert a New Module:
- In the VBA editor, go to Insert > Module.
- Paste the Following Code:
Vba Code is untested backup your file.
Declare Function ShellExecute Lib "shell32.dll" _ Alias "ShellExecuteA" ( _ ByVal hwnd As Long, _ ByVal lpOperation As String, _ ByVal lpFile As String, _ ByVal lpParameters As String, _ ByVal lpDirectory As String, _ ByVal nShowCmd As Long) As Long Sub OpenPDF(filePath As String) ShellExecute 0, "open", filePath, "", "", 1 End Sub Sub OpenPDFfromCell() Dim filePath As String filePath = ThisWorkbook.Sheets("Sheet1").Range("A1").Value ' Adjust the range as needed OpenPDF filePath End Sub- Use the Macro:
- You can use the OpenPDFfromCell macro to open a PDF file whose path is specified in cell A1 of "Sheet1". Adjust the cell reference as needed.
- Assign the Macro to a Button (Optional):
- Go to the Developer tab, click on Insert, choose Button (Form Control), draw the button on your sheet, and assign the OpenPDFfromCell macro to it.
Registry Settings
Sometimes, shell settings in the registry determine how files are opened. Here’s how you can ensure the default PDF application is set correctly:
- Open Registry Editor:
- Press Windows + R, type regedit, and press Enter.
- Navigate to the Key for PDF File Associations:
- Go to HKEY_CLASSES_ROOT\.pdf.
- Verify or Modify the Default Value:
- Ensure that the (Default) value points to the correct file association. This should match the application set in your Default Apps settings.
- Navigate to the Key for the Application:
- Go to HKEY_CLASSES_ROOT\Applications\ and find your preferred PDF application.
- Verify Command Settings:
- Check if the command under shell\open\command is correct for your preferred PDF application.
Shell Settings
If you suspect shell settings might be affecting this, here’s a command you can run in Command Prompt to reset file associations:
- Open Command Prompt as Administrator:
- Press Windows + X and choose Command Prompt (Admin) or Windows PowerShell (Admin).
- Run the Following Command:
Sh Code is untested.
ftype pdffile="<path_to_your_pdf_viewer_executable> %1"
Replace <path_to_your_pdf_viewer_executable> with the full path to your preferred PDF viewer executable.
Additional Considerations
- Update Excel: Ensure that your Excel is updated to the latest version.
- Check for Conflicting Software: Sometimes third-party software or PDF viewers might conflict with the default settings.
- Reinstall the PDF Viewer: Uninstalling and reinstalling your preferred PDF viewer might reset and enforce the default file associations.
By combining these methods, you should be able to get Excel to respect your default PDF viewer settings. If the issue persists, further investigation into Windows shell settings or more advanced registry tweaks might be necessary.
Hi How am I going - trying ?
I am at the end of my knowledge when I get to your VBA solution.
What does the Shell32.dll do and what are the arguments that are being pased to it?
Since there is closing bracket to the funcction declaration , are ther more arguments?
Is the function What is the significance of the “0” and the “1” in the OpenPDF sub?
My problem is making an excel hyperlink that I create in my excel file use my chosen PDF software.
Is the code intended to open my PDF App and using this dll file is the way to do this in VBA?
Your REGISTRY SETTINGS notes yield the following Screen shots.
1, 2, 3
4
5
Shell Settings Next!
Explanation of ShellExecute Function and VBA Code
The ShellExecute function in the shell32.dll library is used to perform various operations on files, such as opening them with their associated applications. This function is part of the Windows API and is commonly used in VBA to interact with the operating system.
ShellExecute Function Declaration
Vba Code is untested backup your file.
Declare Function ShellExecute Lib "shell32.dll" _ Alias "ShellExecuteA" ( _ ByVal hwnd As Long, _ ByVal lpOperation As String, _ ByVal lpFile As String, _ ByVal lpParameters As String, _ ByVal lpDirectory As String, _ ByVal nShowCmd As Long) As Long- hwnd: Handle to the parent window. Typically set to 0.
- lpOperation: The operation to perform (e.g., "open", "print"). In this case, "open" is used.
- lpFile: The file to operate on (e.g., the path to your PDF file).
- lpParameters: If the lpFile parameter specifies an executable file, lpParameters is a string that specifies the parameters to be passed to the application.
- lpDirectory: The default (working) directory. Typically set to "" (empty string).
- nShowCmd: Flags that specify how an application is to be displayed when it is opened. For example, 1 means "normal" (the application is open and in the foreground).
VBA Code Explanation
Here is the VBA code provided earlier, with comments to explain each part:
Vba Code is untested backup your file.
' Declare the ShellExecute function from the shell32.dll library Declare Function ShellExecute Lib "shell32.dll" _ Alias "ShellExecuteA" ( _ ByVal hwnd As Long, _ ByVal lpOperation As String, _ ByVal lpFile As String, _ ByVal lpParameters As String, _ ByVal lpDirectory As String, _ ByVal nShowCmd As Long) As Long ' Subroutine to open a PDF file Sub OpenPDF(filePath As String) ' Call the ShellExecute function with parameters to open the PDF file ShellExecute 0, "open", filePath, "", "", 1 End Sub ' Subroutine to open a PDF file whose path is specified in a cell Sub OpenPDFfromCell() Dim filePath As String ' Get the file path from cell A1 on Sheet1 filePath = ThisWorkbook.Sheets("Sheet1").Range("A1").Value ' Call the OpenPDF subroutine to open the PDF file OpenPDF(filePath) End SubDeclare Function ShellExecute...: This line declares the external function from the Windows API.
- Sub OpenPDF(filePath As String): This subroutine calls ShellExecute to open the PDF file specified by filePath.
- Sub OpenPDFfromCell(): This subroutine retrieves the PDF file path from cell A1 on Sheet1 and then calls OpenPDF.
Steps to Use the VBA Code
- Open the VBA Editor:
- Press Alt + F11.
- Insert a New Module:
- In the VBA editor, go to Insert > Module.
- Paste the Code:
- Paste the VBA code provided above into the new module.
- Assign Macro to a Button (Optional):
- Go to the Developer tab, click on Insert, choose Button (Form Control), draw the button on your sheet, and assign the OpenPDFfromCell macro to it.
Ensuring Excel Uses the Default PDF Viewer
While the VBA solution provides a way to open PDFs using the default application, it's crucial to ensure that your system settings are correctly configured to use your preferred PDF viewer. Here’s how to double-check and adjust these settings:
Verify Default App Settings in Windows
- Open Default Apps Settings:
- Press Windows + I to open the Settings app.
- Go to Apps > Default apps.
- Set Default PDF Viewer:
- Scroll down and click on Choose default apps by file type.
- Find .pdf in the list and make sure your preferred PDF viewer (e.g., Microsoft Edge, Adobe Reader, etc.) is set as the default app for this file type.
Shell Settings Command
- Open Command Prompt as Administrator:
- Press Windows + X and choose Command Prompt (Admin) or Windows PowerShell (Admin).
- Run the Following Command:
- Ensure the command points to your preferred PDF viewer executable:
Cmd Code is untested.
ftype pdffile="C:\Path\To\Your\PDFViewer.exe %1"Additional Steps
If you still encounter issues, consider the following additional steps:
- Update Excel: Ensure that your Excel is updated to the latest version.
- Check for Conflicting Software: Sometimes third-party software or PDF viewers might conflict with the default settings.
- Reinstall the PDF Viewer: Uninstalling and reinstalling your preferred PDF viewer might reset and enforce the default file associations.
By following these steps and using the provided VBA solution, you should be able to ensure that your Excel hyperlinks open PDFs with your preferred default application. The text, steps and codes were created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
