Solved

How hyperlink to Excel file in a specific location

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

713 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