Solved Auto open word, excel and pdf file in excel

November 4, 2015 at 07:41:24
Specs: Windows 7
In excel 2010, I entered "=HYPERLINK(VLOOKUP(B3,CTO_DB!A1:B1001,2,0),B3)". How can I make it auto open the file if hyperlink is exist? Thanks

See More: Auto open word, excel and pdf file in excel

November 4, 2015 at 07:49:33
It is a bit complicated, using LOOKUP with HYPERLINK,
see here for an explanation


Report •

November 4, 2015 at 08:04:41
ok, I will let you know how to resolved the problem, I think I need to find out other way such as add macro into the excel as well. Thanks for your input.

Report •

November 16, 2015 at 01:23:00
✔ Best Answer
You can use something like this, this code uses ShellExecute to open the file within your hyperlink

In this case it is refering to Range("A1") only, this is where you hyperlink needs to be, when you run the code from 'Sub Start' it will check if the file exists, then open the file if it does, or do nothing if it doesnt.

If you can explain how you want the code to function i/we can modify to suite, it if a try and see if it helps.

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 Start()
Dim FileName As String
FileName = Range("A1")

If FileExists(FileName) Then
    ShellExecute 0, "Open", FileName, "", "", vbNormalNoFocus
End If

End Sub

Private Function FileExists(sFile As String) As Boolean

    If Dir(sFile) <> "" Then
        FileExists = True
        FileExists = False
    End If

End Function

Report •
Related Solutions

Ask Question