OPENING A FILE USING A CELL REF. 1

[etch]

I THINK THIS IS POSSIBLE BUT IM NOT SURE HOW TO DO IT,

I want to open a file, based of a value put in a cell A1.

Say for instance i type in a part number, i want to call up that work instruction file from a directory. Now i think a Hyperlink should play some part in it, but not really sure.

i

 

[dsi]

You can use a simple macro. You can initiate it several ways. You could run it from a button on the sheet or run it when cell A1 is changed. If you are not familiar with VBA, just hollar...

Sub OpenSelectedFile()
    Dim sFile As String
    sFile = "I:\tjc\" & Range("A1") & ".xls"
    If Dir(sFile) <> &quot;&quot; Then
        Workbooks.Open sFile
    Else
        MsgBox &quot;Can Not Find File:&quot; & vbCrLf & sFile
    End If
End Sub

DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[etch]

Thanks alot, this is a great bit of code, i have wanted something like this for a while. Unfortunately i forgot to mention i inheritred this system, and while i have developed this, the document i wish to open is a .DOC file.

But this code has great application for other things im trying to do. Once again thanks alot.

 

[dsi]

You can easily modify it for Word docs:

Sub OpenSelectedFile()
    Dim sFile As String
    sFile = "I:\tjc\" & Range("A1") & ".doc"
    If Dir(sFile) <> &quot;&quot; Then
        Call Shell(sFile, vbNormalFocus)
    Else
        MsgBox &quot;Can Not Find File:&quot; & vbCrLf & sFile
    End If
End Sub

DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[etch]

AH Ha, see i tried this but it tries to import it into exel, and come up with "file not valid" , i see you have modifed the code, but now i get error in line

Call Shell(sFile, vbNormalFocus)
it comes up with file not found, now the file is there, and the file path in line 3 is correct. any ideas?

 

[dsi]

Oops! The Shell method only opens .exe files. Use this instead. Note, you will have to declare the function outside of the subroutine.

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 OpenSelectedFile()
    Dim sFile As String
    sFile = "I:\tjc\" & Range("A1") & ".doc"
    If Dir(sFile) <> &quot;&quot; Then
        Call ShellExecute(0&, vbNullString, sFile, vbNullString, vbNullString, 5)
    Else
        MsgBox &quot;Can Not Find File:&quot; & vbCrLf & sFile
    End If
End Sub

DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

 

[etch]

Thanks alot , it works a treat, another red star.