Excel hyperlink default software to open FileName .pdf

[Roderick Sullivan]

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 software I have set in my Settings > Apps > Default Apps.

It seems that Excel has a mind of its own when it comes to this function/action (Opening a Hyperlink to a ".pdf " file elsewhere in the computer).

This is a new recent problem as for many years the default setting was always used.

How to get excel to use the default software as it used to do? Help please!

[#2]

@Roderick Sullivan 

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.

[#3]

@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!

[#4]

@Roderick Sullivan 

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:

1. Open the VBA Editor:
• Press Alt + F11.
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

Sub OpenPDFfromCell()
    Dim filePath As String
    filePath = ThisWorkbook.Sheets("Sheet1").Range("A1").Value ' Adjust the range as needed
    OpenPDF filePath
End Sub

1. 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.
2. 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:

1. Open Registry Editor:
• Press Windows + R, type regedit, and press Enter.
2. Navigate to the Key for PDF File Associations:
• Go to HKEY_CLASSES_ROOT\.pdf.
3. 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.
4. Navigate to the Key for the Application:
• Go to HKEY_CLASSES_ROOT\Applications\ and find your preferred PDF application.
5. 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:

1. Open Command Prompt as Administrator:
• Press Windows + X and choose Command Prompt (Admin) or Windows PowerShell (Admin).
2. 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.

[#5]

@NikolinoDE 

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!

[#6]

@Roderick Sullivan 

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 Sub

Declare 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

1. Open the VBA Editor:
• Press Alt + F11.
2. Insert a New Module:
• In the VBA editor, go to Insert > Module.
3. Paste the Code:
• Paste the VBA code provided above into the new module.
4. 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

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, Adobe Reader, etc.) is set as the default app for this file type.

Shell Settings Command

1. Open Command Prompt as Administrator:
• Press Windows + X and choose Command Prompt (Admin) or Windows PowerShell (Admin).
2. 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.

[#7]

@NikolinoDE 

Thank you for your explanation of ShellExecute Function and VBA Code. I will work my way through your notes.

Did you have any comments on the pictures showing my registry settings?

Regards Roderick