×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

OPENING A FILE USING A CELL REF.

thread770-30075

OPENING A FILE USING A CELL REF.

OPENING A FILE USING A CELL REF.

(OP)
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

RE: OPENING A FILE USING A CELL REF.

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) <> "" Then
        Workbooks.Open sFile
    Else
        MsgBox "Can Not Find File:" & 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.

RE: OPENING A FILE USING A CELL REF.

(OP)
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.

RE: OPENING A FILE USING A CELL REF.

You can easily modify it for Word docs:

Sub OpenSelectedFile()
    Dim sFile As String
    sFile = "I:\tjc\" & Range("A1") & ".doc"
    If Dir(sFile) <> "" Then
        Call Shell(sFile, vbNormalFocus)
    Else
        MsgBox "Can Not Find File:" & 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.

RE: OPENING A FILE USING A CELL REF.

(OP)
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?

RE: OPENING A FILE USING A CELL REF.

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) <> "" Then
        Call ShellExecute(0&, vbNullString, sFile, vbNullString, vbNullString, 5)
    Else
        MsgBox "Can Not Find File:" & 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.

RE: OPENING A FILE USING A CELL REF.

(OP)
Thanks alot , it works a treat, another red star.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login

Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now
Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close