Solved

How hyperlink to Excel file in a specific location

Posted on 2014-04-02
5
228 Views
Last Modified: 2014-04-02
I hope I can explain this one well enough for the experts to understand.

I have a form which has a field named txtNCMR which is a number field.

When the user clicks on that field I want them to be directed to an Excel file in a specific location and have that Excel file open.

The location of the Excel file(s) is C:\Users\Steve\Desktop\CompanyNameNCMRFiles

This path is held in a setup table named tblSetup in a field named "PreviousNCMRfiles".

So for example if the txtNCMR value on the form is 12345 then I want the user to be directed to C:\Users\Steve\Desktop\CompanyNameNCMRFiles\12345.xls and have that file open for them.

If the file 12345.xls doesn't exist then I'd like them to get a message indicating that there is no such file on record.

Is this possible?
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 

Author Comment

by:SteveL13
ID: 39972536
One more thing I didn't mention... the Excel files MAY have preceding "0's in front of the 12345.  

So if the user clicks on 12345 they will need to be directed to 012345.xls.  But in some cases there would be no preceding "0".
0
 
LVL 5

Expert Comment

by:Steve Dubyo
ID: 39972615
Hi,

The following in the code behind your form should do the trick.

Let me know if that works for you.

Private Sub txtNCMR_Click()

    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("tblSetup")
    rs.MoveFirst
    
    Dim dir As String
    dir = rs!PreviousNCMRfiles
    
    rs.Close
    Set rs = Nothing
    
    Dim path As String
    path = dir & "\" & txtNCMR.Value & ".xls"
    
    If AttemptToOpen(path) = False Then
        path = dir & "0" & "\" & txtNCMR.Value & ".xls"
        If AttemptToOpen(path) = False Then
            MsgBox "No such file on record."
        End If
    End If

End Sub

Private Function AttemptToOpen(path As String) As Boolean
    If Len(dir(path)) = 0 Then
        AttemptToOpen = False
    Else
        Application.FollowHyperlink path
        AttemptToOpen = True
    End If
End Function

Open in new window

0
 

Author Comment

by:SteveL13
ID: 39972659
Hmmm, that didn't work.  Is it because the actual file name is "012345.xls" while the text field on the form reads "12345"


??
0
 
LVL 5

Accepted Solution

by:
Steve Dubyo earned 500 total points
ID: 39972742
I saw your update so it does take leading zeros into account already.  I spotted a typo in my original code though.   "0" & "\" instead of "\" & "0"

Here is the fixed version..

Private Sub txtNCMR_Click()

    Debug.Print txtNCMR.Value

    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("tblSetup")
    rs.MoveFirst
    
    Dim dir As String
    dir = rs!PreviousNCMRfiles
    
    rs.Close
    Set rs = Nothing
    
    Debug.Print dir
    
    Dim path As String
    path = dir & "\" & txtNCMR.Value & ".xls"
    
    If AttemptToOpen(path) = False Then
        path = dir & "\" & "0" & txtNCMR.Value & ".xls"
        If AttemptToOpen(path) = False Then
            MsgBox "No such file on record."
        End If
    End If

End Sub

Private Function AttemptToOpen(path As String) As Boolean

    Debug.Print path
    
    If Len(dir(path)) = 0 Then
        AttemptToOpen = False
    Else
        Application.FollowHyperlink path
        AttemptToOpen = True
    End If
End Function

Open in new window



*also added debug output just in case we still have any problem.
0
 

Author Comment

by:SteveL13
ID: 39973523
Perfect!  Thank you.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question