Unable to Activate HyperLink Calculation ("=HyperLink("+"L:\Folder\SubFolder\Filename.jpg+")"

  • Hello All:


    I have an Excel file with several hundred calculated hyperlinks using a formula link: HyperLink Calculation ("=HyperLink("+"L:\Folder\SubFolder\Filename.jpg+"). I found a vba code snippet: ActiveSheet.Hyperlinks.Add myCell, myCell.Value, which seems to activate the formulas (full vba code attached), but which I click on the resulting hyperlink I get error saying the target file cannot be found.


    Without running the VBA script, I can open the same file, double click each cell and press enter and the hyperlink works as expected. The code is not making any change other that activating the hyperlink. Also note that the cell still shown the full formula (i.e., the =hyperlink part is still visible. When I click inside the cell value and press enter, the =hyperlink part is no longer visible, but I get the same error. Is seems something is there that causing the problem, but I cannot determine the problem.


    Any ideas???


    Thanks for any help.

  • .

    Just off hand, after a quick review of your code ... I would guess the issue is this :


    Code
    1. FileName = "L:\" & Mid(ThisWorkbook.Sheets("Sheet2").Cells(2, 9), 4, 4) & "\" & _
    2. ThisWorkbook.Sheets("Sheet2").Cells(2, 9) & "\" & _
    3. Left(ThisWorkbook.Sheets("Sheet2").Cells(2, 9), 8) & "WFB_StatementDetail.xlsx"


    Is there any way you can get rid of all the MID, ThisWorkBook.Sheets, LEFT etc ? Can you simply refer to the file ?

  • Don't think the Filename formula is the problem. the code I loaded reflected opening the target Workbook/Worksheet from another Workbook. I imported the same code to the target Workbook where the Filename formula is not required. I get the same result... the activate code snippet runs by the resulting hyperlink does not work.


    Any other suggestions.


    Thanks

  • .

    Have you tried doing this :


    Code
    1. FileName = "L:\" & Mid(ThisWorkbook.Sheets("Sheet2").Cells(2, 9), 4, 4) & "\" & _
    2. ThisWorkbook.Sheets("Sheet2").Cells(2, 9) & "\" & _
    3. Left(ThisWorkbook.Sheets("Sheet2").Cells(2, 9), 8) & "WFB_StatementDetail.xlsx"
    4. MsgBox FileName
    5. Exit Sub


    Just out of curiosity to verify the correct path / file is being called ? The error is indicating it can't find the file.


    Also, is the workbook actually there ?

  • I took the Filename formula out of play by importing the Hyperlink Module into the workbook containing the hyperlink calculation. I have attached the simplified code. Same result:-(

  • The Code is nor showing an error the data, below is a subset of the data in Column I which I am trying to get vba to covert to hyperlink to a folder/file location. The "=HyperLink" should not show after vba converts the value to a hyperlink. It works manually cell by cell, but I want vba to do it , because larger files will be coming in great frequency in future.


    =HyperLINK("L:\2020\04 2020 April xxxxx xxxxx xxxxxx\WFB 04 01 20_page_01.jpg")
    =HyperLINK("L:\2020\04 2020 April xxxxx xxxxx xxxxxx\WFB 04 01 20_page_02.jpg")
    =HyperLINK("L:\2020\04 2020 April xxxxx xxxxx xxxxxx\WFB 04 01 20_page_03.jpg")
    =HyperLINK("L:\2020\04 2020 April xxxxx xxxxx xxxxxx\WFB 04 01 20_page_04.jpg")
    =HyperLINK("L:\2020\04 2020 April xxxxx xxxxx xxxxxx\WFB 04 01 20_page_05.jpg")
    =HyperLINK("L:\2020\04 2020 April xxxxx xxxxx xxxxxx\WFB 04 01 20_page_06.jpg")
    =HyperLINK("L:\2020\04 2020 April xxxxx xxxxx xxxxxx\WFB 04 01 20_page_07.jpg")
    =HyperLINK("L:\2020\04 2020 April xxxxx xxxxx xxxxxx\WFB 04 01 20_page_08.jpg")
    =HyperLINK("L:\2020\04 2020 April xxxxx xxxxx xxxxxx\WFB 04 01 20_page_09.jpg")
    =HyperLINK("L:\2020\04 2020 April xxxxx xxxxx xxxxxx\WFB 04 01 20_page_10.jpg")
    =HyperLINK("L:\2020\04 2020 April xxxxx xxxxx xxxxxx\WFB 04 01 20_page_11.jpg")
  • Can you work with this sample macro and makes changes as required so it works with your project ?

    At present it will create a hyperlink to every file/image located in a specific folder, write that hyperlink to a cell in

    Col A, then write the next one to A2, then to A3, etc.


  • The second option would be a non-started. Each .jpg image is quite large, so post it to Excel would be quite bulky.


    As I understand the first option, the code cycles through all the object (.jpg) files and copy the address as a hyperlink in Excel... Right. Seems feasible, but there are quite a number of other attributes on each record in the large file, and I would need to employ some complex logic to ensure the Hyperlink is posting to the correct record.


    I just believe I am missing something very simple to get VBA to activate the calculated hyperlink. Manually double clicking the cell and pressing enter does the job. Cannot understand why the ActiveSheet.Hyperlinks.Add myCell, myCell.Value in vba is not doing the job. I did notice in your procedure you have an option Anchor:=Selection. What is this option? Should I try adding it to my code?


    Thanks,

  • To answer your question ... No.


    1. 'select cell
    2. Range(Cells(i + 1, 1), Cells(i + 1, 1)).Select <--- this selects the next empty cell in the column
    3. 'create hyperlink in selected cell
    4. ActiveSheet.Hyperlinks.Add Anchor:=Selection, <--- this insures the image hyperlink is created in the cell that was selected.

    Cell was selected and "Anchored" for the link creation.