OPENING A FILE USING A CELL REF. 1

etch · Aug 21, 2002

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 · Aug 21, 2002

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...

Code:

Sub OpenSelectedFile()
    Dim sFile As String
    sFile = &quot;I:\tjc\&quot; & Range(&quot;A1&quot;) & &quot;.xls&quot;
    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 · Aug 21, 2002

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 · Aug 21, 2002

You can easily modify it for Word docs:

Code:

Sub OpenSelectedFile()
    Dim sFile As String
    sFile = &quot;I:\tjc\&quot; & Range(&quot;A1&quot;) & &quot;.doc&quot;
    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 · Aug 21, 2002

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 · Aug 21, 2002

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

Code:

Private Declare Function ShellExecute Lib &quot;shell32.dll&quot; Alias &quot;ShellExecuteA&quot; (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 = &quot;I:\tjc\&quot; & Range(&quot;A1&quot;) & &quot;.doc&quot;
    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 · Aug 22, 2002

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

OPENING A FILE USING A CELL REF. 1

etch

Mechanical

dsi

Mechanical

etch

Mechanical

dsi

Mechanical

etch

Mechanical

dsi

Mechanical

etch

Mechanical

Similar threads

Part and Inventory Search

Sponsor