#1
|
|||
|
|||
HyperLink help please
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
Thank you Catlin B for your script - but you're gonna laugh I suppose...
but, where do I put it Cheers, Mal |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |