Link to home
Create AccountLog in
Avatar of Mavriikk
Mavriikk

asked on

excel hyperlinnk troubles

At my current place of employment we are having some audits and it has been requested that the inventory of a certain section in the building be copied to a cd.

Currently the excel inventory spreadsheet has the part numbers linked to the photos of the particular items which are stored on a file server.

Is there a way for me to move the excel file to the cd and keep the pictures linked within the spreadsheet. I understand that i would need to move the pictures to the cd as well.

Is is possible to create the identical directory structure on the disc???

Any Help at all would be greatly appreciated. Even a "No" this is not possible would help. Thanks
Avatar of eeeznutz
eeeznutz

You could create the file structure on disc, but depending on how your hyperlinks are setup, they are probably referencing the file server's name, meaning the link would continue to reference the server and not the disc. You could probably find a way to programmatically change a portion of the hyperlink so that it references a folder on the CD instead.
Mavriikk

How many links are there? If not much then why not show the pictures in the column next to the partnumbers?

I had created an add-in few months ago which inserted the pictures from the links to the adjacent cells so that you could have a look at the picture without clicking any links?

Sid
This also reminds me there there was one more part of the add-in which I created  which inserts the pictures in the comments of the cell which has the link. So to see the picture all you need to do was hover your mouse over the relevant cell :)

Sid
Have you considered making a VBA routine that parses the current excel workbook with hyperlinks :

for each hyperlink
  - look up server-directory of file
  - make corresponding cd-directory if needed
  - copy file to cd-directory
  - update hyperlink
save updated workbook to cd-directory

this can be achieved fairly easy, after which you burn the contents of the cd-directory to cd.
assuming that you have only one file server you could use something like

Public Sub process_hyperlinks()
Dim link As Hyperlink
Dim src As String
Dim dst As String
Dim src_base As String
Dim dst_base As String

    '-- enter required source and destination locations here
    src_base = "\\server\folder"
    dst_base = "D:\temp\cd-archive\"
    
    '-- loop through all hyperlinks in the worksheet
    For Each link In ActiveSheet.Hyperlinks
        src = link.Address
        dst = Replace(src, src_base, dst_base)
            
        '-- copy the linked file to new location
        If src <> dst Then
            copy_file src, dst
            '-- update actual hyperlink
            link.Address = dst
            '-- update visible text of hyperlink
            'link.TextToDisplay = dst
        Else
            Debug.Print "Hyperlink already converted or not in source base [" & src & "]"
        End If
    Next link
    
    '-- loop through all manually inserted hyperlinks [=hyperlink(...,...)]
    For Each cel In UsedRange.Cells
        If InStr(cel.Formula, "HYPERLINK") > 0 Then
            src = Mid(cel.Formula, InStr(cel.Formula, "HYPERLINK") + 11) '-- strip header
            src = Left(src, InStr(src, """") - 1) '-- strip footer
            dst = Replace(src, src_base, dst_base)
                                
            '-- copy the linked file to new location
            If src <> dst Then
                copy_file src, dst
                '-- update actual hyperlink
                link.Address = dst
                '-- update visible text of hyperlink
                'link.TextToDisplay = dst
            Else
                Debug.Print "Hyperlink already converted or not in source base [" & src & "]"
            End If
        End If
    Next cel

End Sub

Private Sub copy_file(source As String, destination As String)
Dim fso As Object
Dim dst_path As String
Dim dst_file As String
Dim pos As Integer

    Set fso = CreateObject("Scripting.FileSystemObject")
    
    '-- check for current file status
    If Not fso.fileexists(source) Then MsgBox "Source file does not exist !", vbCritical: Exit Sub
    If fso.fileexists(destination) Then MsgBox "Destination file already exists !", vbCritical: Exit Sub
    
    '-- split destination
    dst_path = Left(destination, InStrRev(destination, "\"))
    dst_file = Mid(destination, InStrRev(destination, "\") + 1)
    
    '-- check if destination directory exists
    If Not fso.folderexists(dst_path) Then
        '-- generate required folders step by step
        For pos = 1 To Len(dst_path)
            If Mid(dst_path, pos, 1) = "\" Then
                If Not fso.folderexists(Left(dst_path, pos)) Then fso.createfolder Left(dst_path, pos)
            End If
        Next pos
    End If

    '-- copy file
    fso.Copyfile source:=source, destination:=destination
    
End Sub

Open in new window

Avatar of Mavriikk

ASKER

Akoster that is what i am looking to do. Pardon my ignorance i am real green in this area. How do i go about making this excellent script run on my file?

Thanks in advance.
Sorry for the not so timely reply, I have been very busy at work...

In order for the script to run you will have to make it do so. Which option suites you best is up to you off course, but in general you have these options :

 - script can be run manually when the end user presses a key combination (eg. Ctrl-Shift-P)
 - script can be run automatically whenever the end user opens the workbook
 - script can be run when the end user presses a button somewhere in a worksheet

When the script is not going to be used very heavily (or even only once), the best option would be to take the following steps :

 - open the workbook
 - press Alt-F11 (to open the visual basic editor)
 - double click on the name of the inventory sheet (on the top-left part of the editor : VBA project / excel objects / sheets)
 - paste the code from above in the (presumably empty) code window (right part of the editor)
 - update the src_base & dst_base variables to match your situation
 - click a line  in the code to place the cursor in the "process_hyperlinks" subroutine
 - press F5 (to run the selected subroutine)

PS> do not pardon yourself for not having knowledge (yet), that's what this website is made for !
Ok I think i am getting there. I copied the string of the pictures into the code and i have run the macro. It doesn't appear to have worked though. When i hover over the hyperlink the box still has the network path.

I am not sure if i am running the macro wrong on the wrong object on the left or what?

This is the only change i had made in the code.


    '-- enter required source and destination locations here
    src_base = "\\nc12-data\SecureEngineerShare\Data Share\Fall River Facility\Nc12 Process\Master Equipment Inventory Database\Pictures"
    dst_base = "D:\temp\cd-archive\"
   
    '-- loop through all hyperlinks in the worksheet
    For Each link In ActiveSheet.Hyperlinks
        src = link.Address
        dst = Replace(src, src_base, dst_base)

thanks,
ASKER CERTIFIED SOLUTION
Avatar of Arno Koster
Arno Koster
Flag of Netherlands image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
The code I provided works, but I am not sure that Mavrikk verified that the link itself was updated while the visual feedback has not been updated. (see comment 34534652)

From my point of view I have correctly answered the question.
Avatar of Tracy
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.