Forum Discussion

RoderickSullivan's avatar
RoderickSullivan
Copper Contributor
Jul 05, 2024

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

 

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    RoderickSullivan 

    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.

    • RoderickSullivan's avatar
      RoderickSullivan
      Copper Contributor

      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!

      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor

        RoderickSullivan 

        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.

Resources