Solved

How hyperlink to Excel file in a specific location

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

623 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