Solved

How hyperlink to Excel file in a specific location

Posted on 2014-04-02
5
223 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
  • 3
  • 2
5 Comments
 

Author Comment

by:SteveL13
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Perfect!  Thank you.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now