Solved

How hyperlink to Excel file in a specific location

Posted on 2014-04-02
5
225 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
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

785 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