Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2013
    Posts
    163

    Unanswered: Open A file from a particular location

    Dear Seniors,

    I am a new user to Access and I am creating a Document Tracking database for my Project Use. I have created table with details like Transmittal Number, Date and Reply Status.

    I need to open the PDF Transmittal file from a same location when I click the transmittal Number.

    I had used the Application.Hyperlink option, It opens the Folder location but not the file.

    Could anybody help me to sort out this issue

    Thanks and Regards
    R. Vadivelan

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If you mean FollowHyperlink, that's what I would use. If it only opened the folder, you must not have specified the file name. Not being able to see your code, hard to fix.
    Paul

  3. #3
    Join Date
    Oct 2013
    Posts
    163
    Hi Paul,

    Thanks for your quick reply. Actually I am not fully aware of Application.Followhyperlink Function. I had searched in this forum and found the following code.

    Function OpenPDF(Folder, File)

    Dim strAddress As String
    strAddress = Nz(Folder)
    strAddress = strAddress & IIf(strAddress = "", "", "/") & Nz(File)
    strAddress = strAddress & IIf(strAddress = "", "", ".pdf")
    If strAddress = "" Then
    MsgBox "There is no text to search."
    Else
    Application.FollowHyperlink "D:\01_Projects\05_FLNG Indonesia\DCC\TR to CPY\PIMS PDF Transmittal\" & strAddress
    End If

    End Function

    D:\01_Projects\05_FLNG Indonesia\DCC\TR to CPY\PIMS PDF Transmittal\ - Path of my File Location

    AF09A-SAI-INM-TR-0178 - File name

    I had used the above code in trial and error, First time it opened the file location.
    Next time it opens lot of pdf file at the same time.

    I have created a table with list of transmittal. Now I want to click on the Transmittal Number in query so that it opens that particular PDF file.

    I don't how to create a code to open a file from a particular location.

    Thanks and Regards
    R. Vadivelan

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Store the full path (path + name + extension) of the files you want be able to open.
    2. Paste this code in a new standard module:
    Code:
    Private Enum enSW
        SW_HIDE = 0
        SW_NORMAL = 1
        SW_MAXIMIZE = 3
        SW_MINIMIZE = 6
    End Enum
    
    Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
    
    Sub OpenDoc(ByVal Document As String)
    
        Dim x As Long
    
        If Len(Dir(Document)) > 0 Then
            x = ShellExecute(Application.hWndAccessApp, vbNullString, Document, vbNullString, "C:\", enSW.SW_NORMAL)
        Else
            MsgBox "Cannot find: " & Document, vbExclamation, "File not found."
        End If
    
    End Sub
    3. You can now open any document the type of which is registered (.doc, .txt, .pdf, .xls, ...) by using:
    Code:
        OpenDoc "C:\Sinndho\Doc IT\10_things_toolkit.pdf"
    Have a nice day!

  5. #5
    Join Date
    Oct 2013
    Posts
    163
    Hi Sinndho,

    Thanks for your quick reply. However I am not able to understand this code fully since I am a Beginner in Access.

    Could you please explain me Where I have to save the file Path.

    My File Path is "D:\01_Projects\05_FLNG Indonesia\DCC\TR from CPY\" & FileName (FileName based on Field values)

    After Pasting the Code in Module I got the following function.

    ShellExecute(«hwnd», «lpOperation», «lpFile», «lpParameters», «lpDirectory», «nShowCmd»)

    Could you please give me a example how to store the file path and file name.

    Herewith I had enclosed my Table for your review and further assistance.

    Thanks and Regards
    R. Vadivelan
    Attached Files Attached Files

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by velu130486 View Post
    Could you please explain me Where I have to save the file Path.

    My File Path is "D:\01_Projects\05_FLNG Indonesia\DCC\TR from CPY\" & FileName (FileName based on Field values)
    Not sure to understand what you mean with "FileName based on Field values". Which field? You must have the full path (Folder + file name + file extension) to call the OpenDocument function.

    Quote Originally Posted by velu130486 View Post
    After Pasting the Code in Module I got the following function.

    ShellExecute(«hwnd», «lpOperation», «lpFile», «lpParameters», «lpDirectory», «nShowCmd»)
    I don't know what you're talking about. What do you mean by "Pasting the Code in Module"? If you copy the code I posted, then paste it in a new standard module, this new standard should contain the text you copied/pasted and nothing else.
    Quote Originally Posted by velu130486 View Post
    Could you please give me a example how to store the file path and file name.
    For a simple demo, I added a column in the table TRANS, type: Text(255).
    Then I created a form with a combobox:
    Name: Combo_Documents
    RowSourceType: Table/Query
    RowSource:
    Code:
    SELECT TRANS.[Client Document Identification], TRANS.DocumentFile FROM TRANS ORDER BY TRANS.[Client Document Identification];
    ColumnCount: 2
    BoundColumn: 2
    I then added a command button:
    Name: OpenDoc
    Caption: Open Document
    OnClick: [Event Procedure]

    In the module of the form, I created the following event handler for the command button:
    Code:
    Private Sub OpenDoc_Click()
    
        OpenDocument Me.Combo_Documents.Value
        
    End Sub
    I renamed the function OpenDoc in the standard module to OpenDocument because OpenDoc caused a reference conflict. So the standard module now looks like:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Enum enSW
        SW_HIDE = 0
        SW_NORMAL = 1
        SW_MAXIMIZE = 3
        SW_MINIMIZE = 6
    End Enum
    
    Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
    
    Public Sub OpenDocument(ByVal Document As String)
    
        Dim x As Long
    
        If Len(Dir(Document)) > 0 Then
            x = ShellExecute(Application.hWndAccessApp, vbNullString, Document, vbNullString, "C:\", enSW.SW_NORMAL)
        Else
            MsgBox "Cannot find: " & Document, vbExclamation, "File not found."
        End If
    
    End Sub
    To open a document, I select it in the combo then press the OpenDoc command button.

    If you want to store the different parts of the document name (folder, file name) in different columns, you'll need to concatenate those parts and pass the result of the concatenation to the OpenDocument() function. It's hard to be more specific because the database you posted contains nothing but a table. Moreover, the names of the columns in that table do not help to understand what they are supposed to contain.
    Have a nice day!

  7. #7
    Join Date
    Oct 2013
    Posts
    163
    Hi Sinndho,

    Thanks for your detailed reply.

    Quote Originally Posted by Sinndho View Post
    Not sure to understand what you mean with "FileName based on Field values". Which field? You must have the full path (Folder + file name + file extension) to call the Open Document function.

    Previously I was using the excel to maintain a Transmittal register with Hyperlinks (i.e.) when I click the transmittal Number It will open the corresponding PDF. When I exported the excel to Access then the Hyperlinks are dead. Then I goggled and understood that I need to put # in front and back of Hyperlinks and it was working in access also. But in the hyperlink column the entire Hyperlink path is displaying. My objective is to reduce the length of the File path to a Short text.

    For ex
    My File path is "D:\01_Projects\05_FLNG Indonesia\DCC\TR from CPY\" & FileName.pdf" ---- I want to display this as "Filename" or "OpenDocument" etc.,

    I don't know what you're talking about. What do you mean by "Pasting the Code in Module"? If you copy the code I posted, then paste it in a new standard module, this new standard should contain the text you copied/pasted and nothing else.

    For a simple demo, I added a column in the table TRANS, type: Text(255).
    Then I created a form with a combobox:
    Name: Combo_Documents
    RowSourceType: Table/Query
    RowSource:
    Code:
    SELECT TRANS.[Client Document Identification], TRANS.DocumentFile FROM TRANS ORDER BY TRANS.[Client Document Identification];
    ColumnCount: 2
    BoundColumn: 2
    I then added a command button:
    Name: OpenDoc
    Caption: Open Document
    OnClick: [Event Procedure]

    In the module of the form, I created the following event handler for the command button:
    Code:
    Private Sub OpenDoc_Click()
    
        OpenDocument Me.Combo_Documents.Value
        
    End Sub
    I renamed the function OpenDoc in the standard module to OpenDocument because OpenDoc caused a reference conflict. So the standard module now looks like:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Enum enSW
        SW_HIDE = 0
        SW_NORMAL = 1
        SW_MAXIMIZE = 3
        SW_MINIMIZE = 6
    End Enum
    
    Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
    
    Public Sub OpenDocument(ByVal Document As String)
    
        Dim x As Long
    
        If Len(Dir(Document)) > 0 Then
            x = ShellExecute(Application.hWndAccessApp, vbNullString, Document, vbNullString, "C:\", enSW.SW_NORMAL)
        Else
            MsgBox "Cannot find: " & Document, vbExclamation, "File not found."
        End If
    
    End Sub
    To open a document, I select it in the combo then press the OpenDoc command button.

    Thanks for your code. I will try the same.

    If you want to store the different parts of the document name (folder, file name) in different columns, you'll need to concatenate those parts and pass the result of the concatenation to the OpenDocument() function. It's hard to be more specific because the database you posted contains nothing but a table. Moreover, the names of the columns in that table do not help to understand what they are supposed to contain.
    I could understand from the above that I need to create a Form with all the fields, then I need to select the file number in combo box and click the command button. But I have only one query in the OpenDocument(), How to store my file path in this function...This was little bit confusing for me

    Any how I will give a try and update you by tomorrow.

    Thanks and Regards
    R. Vadivelan

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by velu130486 View Post
    I could understand from the above that I need to create a Form with all the fields, then I need to select the file number in combo box and click the command button. But I have only one query in the OpenDocument(), How to store my file path in this function...This was little bit confusing for me
    You can have a columns containing the file name and a column containing the path to the file (the former name).

    If the file name is unique (i.e. 2 files cannot have the same name), you can only display the file name, in a textbox of a form bound to the table, in a listbox or combobox. When a (unique) file name is selected for opening a document, you can retrieve it's forder name using (here with a textbox):
    Code:
    Dim strFullPath As String
    Dim strFolderName As String
    
    strFolderName = DLookup("FolderName", "Tbl_Files", "FileName='" & Me.Text_FileName.Value & "'")     ' Text_FileName is the name of the textbox.
    If Right(strFullPath, 1) <> "\" And Left(Me.Text_FileName.Value, 1) <> "\" Then 
        strFolderName = strFolderName & "\"
    End If
    strFullPath = strFolderName & Me.Text_FileName.Value
    OpenDocument strFullPath
    Have a nice day!

  9. #9
    Join Date
    Oct 2013
    Posts
    163
    Dear Seniors,

    I had created a Command button to Open a PDF file in Form with help of some coding. But however the result is not successful.

    Could you please check the coding and advice me where I had made a mistake.

    Attached sample file.

    Thanks and Regards
    R. Vadivelan
    Attached Files Attached Files

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    one thing to bear in mind when using a drive letter within a path to a file location AND in a multi user environment AND the file is on a remote disk is that you probably shouldnt' use a drive id but instead a server share

    so instead of
    D:\01_Projects\05_FLNG Indonesia\DCC\TR to CPY\PIMS PDF Transmittal\
    replace the D: with the server name, say you erver was called myserver
    \\myserver\01_Projects\05_FLNG Indonesia\DCC\TR to CPY\PIMS PDF Transmittal\
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Me.Form.Text47 is an invalid reference. It should be Me.Text47.

    A solution was provided to open a document, why don't you use it or post questions about it if there is something that does not work for you?

    For several reasons I won't expose here, using function Shell() is a bad idea. Use the FollowHyperLink method or the OpenDocument() procedure described sooner in this thread.
    Ex:
    Code:
    MyPath = "D:\01_Projects\05_FLNG Indonesia\DCC\TR to CPY\PIMS PDF Transmittal\" & Me.Text47.Value
    OpenDocument MyPath
    Or:
    Code:
    MyPath = "D:\01_Projects\05_FLNG Indonesia\DCC\TR to CPY\PIMS PDF Transmittal\" & Me.Text47.Value
    FollowHyperlink MyPath
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •