HyperLink help please

malfromcessnock · #1 12-19-2011, 04:07 AM

Hi board members. My first post.

I'm writing up a book listing all my videos etc. I'm busy doing the hyperlinks for each entry and I want to avoid all the clicking.

My videos are on N:\ and everytime I click to find the file/folder/url/image relating to the movie - all in N:\ the damn thing defaults to L:\

I thought and would like it to start looking at the last place I made a hyperlink. I have never made one from L:\

Is there a way to set a default starting place. I have hundreds of videos and each video has 4 links.

I've closed Excel and restarted it.

I've shut down and restarted the computer, but each time it begins the hyperlink process in L:\

Anyone got any ideas, it could save me hours and a busted mouse!

Cheers, Mal

Catalin.B · #2 12-20-2011, 08:23 AM

Code:

sub search ()
Dim vFile As Variant, MyPath As String
MyPath = Application.Substitute(ThisWorkbook.Path, "facturi", "chitantiere\", 1) '"\\Flory\D\FACTURI ,CHITANTE VPC\chitantiere"
CreateObject("WScript.Shell").CurrentDirectory = MyPath
       ChDir MyPath
       vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
      "*.xl*", 1, "Select Excel File", "Open", False)
end sub

if you want all types of files to show simply use Application.GetOpenFileName()

malfromcessnock · #3 12-20-2011, 05:12 PM

Thank you Catlin B for your script - but you're gonna laugh I suppose...

but, where do I put it

Cheers, Mal

Catalin.B · #4 12-20-2011, 11:09 PM

Press alt+F11 to open Visual Basic Editor, on the left side, you will see VBA Project(your file name.xlsx), right click on it, insert new module, then on the right side of the window, paste the codes below.
Activate macro: Excel 2007-2010: press Microsoft Office Button , then click Excel Options. In Trust Center category, click on Trust Center Settings, then click on category Macro Settings.(recomended option- Medium).This way, excel will ask for your permission to activate macro for that file.
As you can see , there are two macros, one will open all file types, the second will open only excel files. Macros:

Code:

Option Explicit
Sub search()
Dim vFile As Variant, MyPath As String
MyPath = ThisWorkbook.Path 'replace with your desired path
CreateObject("WScript.Shell").CurrentDirectory = MyPath
       ChDir MyPath
       'for all files type
       vFile = Application.GetOpenFilename()
End Sub

Sub search2()
Dim vFile As Variant, MyPath As String
MyPath = "\\Flory\D\" 'replace with your desired path
CreateObject("WScript.Shell").CurrentDirectory = MyPath
       ChDir MyPath
'for excel files only
       vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
      "*.xl*", 1, "Select Excel File", "Open", False)
End Sub

malfromcessnock · #5 12-21-2011, 06:40 PM

Catalin.B - I'm so grateful for your help.

I followed your comprehensive instructions to the letter (of course - lol)
Activated the macros in Exel 2007 - however what I found in Trust Centre/Trust Centre Settings/Macro Settings/ there was no "medium level" to be found.

So I chose and clicked the radio button; "enable" and the check box; "trust access to the VBA Project Object Model.

I saved the file as a .xlsm and re-opened the work book. Attempted to get the benefit of your macro and created a hyperlink to one of the listed videos, but it behaved the same as before and didn't open as I expected in the desired directed.

In the macro I replaced the appropriate text with 'N:\video\movies\

More help would be appreciated if you have the time - I understand it's the Holiday Season.

Cheers, Mal

Catalin.B · #6 12-21-2011, 11:07 PM

Maybe i didn't understand what you need.
Try this: (in cells you must enter just the file name with extension, simply click on a cell , then start macro. It will open the file with the path specified in macro, and the name found in the active cell)

Code:

Option Explicit
Private 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 openfiles()
Dim FileName, FilePath As String

    FileName = ActiveCell
FilePath = "E:\catalin\muzica" 'ThisWorkbook.Path  '"N:\video\movies\"
If Len(Dir(FilePath & "\" & FileName)) = 0 Then
MsgBox ("File " & FilePath & "\" & FileName & " not found!")
Exit Sub
    End If
    ActiveWorkbook.FollowHyperlink FilePath & FileName, NewWindow:=True
End Sub
Sub PlayVideo()
  Dim FileName As String
  Dim FilePath As String, retVal
  
    FileName = ActiveCell
    FilePath = "E:\catalin\muzica"
    
    retVal = ShellExecute(0&, "open", FileName, vbNullString, FilePath, 1&)
    
End Sub

malfromcessnock · #7 12-24-2011, 11:43 PM

Thanks for all your help - have a great Holiday Season

Cheers, Mal

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Hyperlink	Mahmuz	Word	2	05-04-2011 10:59 AM
Followed hyperlink question	dth122	PowerPoint	0	06-06-2010 07:15 PM
Hyperlink Warning	gfkbob	Outlook	0	01-23-2010 06:50 AM
Hyperlink	ltvjim	Outlook	0	03-28-2007 10:28 AM
hyperlink	scuba32	Word	0	08-24-2006 07:15 AM