Open any file in any application from VBA

  • Hello everyone ,
    I would like to write a function which would basically allow the user to open any file from Excel.
    I've got a user form which records the path and filename of a range of documentation, which will be in Excel, Word, PPT, PDF, HTML,... etc.
    I would like to have some code which would just open the file as if it had been double-clicked in windows.


    I've seen below code which is used for 32 bit not for 64 bit , then I tried to convert it to 64 bit but still not working so i hope someone can help me with this as I'm still leaning excel vba .
    --------------------


    Private Declare PtrSafe Function GetDesktopWindow _
    Lib "user32" () As LongPtr


    Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" _
    Alias "ShellExecuteA" ( _
    ByVal hwnd As LongPtr, _
    ByVal lpOperation As String, _
    ByVal lpFile As String, _
    ByVal lpParameters As String, _
    ByVal lpDirectory As String, _
    ByVal nShowCmd As LongPtr) _
    As LongPtr


    Private Const SW_HIDE As LongPtr = 0
    Private Const SW_NORMAL As LongPtr = 1
    Private Const SW_MAXIMIZE As LongPtr = 3
    Private Const SW_MINIMIZE As LongPtr = 6


    '//
    '//
    '// ShellExecute API.
    '// Use Windows file associations to provide the easiest way to
    '// process operations > Open, Print,Play or Explore
    '// by passing just the FullPathFilename of the item.
    '//
    '//


    Public Function ShellOper(strFileExe As String, _
    Optional strOperation As String, _
    Optional nShowCmd As Double) As LongPtr


    Dim hWndDesk As LongPtr


    hWndDesk = GetDesktopWindow()
    If Len(strOperation) = 0 Then strOperation = "Open"
    If Len(Dir(strFileExe)) = 0 Then GoTo ErrH


    '// Failure >> <=32
    ShellOper = ShellExecute(hWndDesk, strOperation, strFileExe, 0, 0, nShowCmd)
    If ShellOper <= 32 Then
    MsgBox "Couldn't " & strOperation & " " & strFileExe
    End If


    Exit Function
    ErrH:
    ShellOper = -1
    End Function



    Sub Tester()
    Dim Ret
    '// Substitute here your Doc full path
    Ret = ShellOper("C:Intel\Logs", "Open", SW_MAXIMIZE)



    '// Typical errors
    '-1 No File Exists
    '0 The operating system is out of memory or resources.
    'SE_ERR_FNF = 2 The specified file was not found.
    'SE_ERR_PNF = 3 The specified path was not found.
    'SE_ERR_ACCESSDENIED = 5 The operating system denied access to the specified file.
    'SE_ERR_OOM = 8 There was not enough memory to complete the operation.
    'ERROR_BAD_FORMAT = 11 The .EXE file is invalid (non-Win32 .EXE or error in .EXE image).
    'SE_ERR_SHARE = 26 A sharing violation occurred.
    'SE_ERR_ASSOCINCOMPLETE = 27 The filename association is incomplete or invalid.
    'SE_ERR_DDETIMEOUT = 28 The DDE transaction could not be completed because the request timed out.
    'SE_ERR_DDEFAIL = 29 The DDE transaction failed.
    'SE_ERR_DDEBUSY = 30 The DDE transaction could not be completed because other DDE transactions were being processed.
    'SE_ERR_NOASSOC = 31 There is no application associated with the given filename extension.
    'SE_ERR_DLLNOTFOUND = 32 The specified dynamic-link library was not found.


    End Sub


    ===================================





    Thanks

  • This macro assumes that the full path is included in the userform location, example: C:\Test\SampleFile.pdf

    Code
    Sub OpenFile()
        ThisWorkbook.FollowHyperlink Address:=Range("A1"), NewWindow:=True
    End Sub


    Replace Range("A1") with the location of the path and file name information. Perhaps you can adapt the macro to suit your needs.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • thank you for your try .. let me explain more , I will build user form with one text box and command button then there is one main path folder like C:Intel\Logs then this path has sub folders and this is updated every day by adding folders , so what I want is to write the file name in text box then click button to go directly to subfolder as per the text box input and by double click I can open the file either pdf or word or excel or outlook etc....

  • Can you attach a copy of your file?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Try the attached file. In the CommandButton1_Click macro, change the file path where indicated to suit your needs. I've added a button with related code to the userform and also added a UserForm_Initialize macro.

    Files

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi Sir , thank you again for try , sorry I was sick 3 days ago , when I tried this its open the pdf by click but the issue is the userform is disappeared after the search and keeping the old value and also I receive a security alarm about hyper link is it possible to hide it and also if there is a way to make small icon in blow taskbar for the user form , sorry for boring you, many many thanks for your big help .

  • Try this version:


    The only way you can eliminate the security warning message is by editing your registry. I would not recommend doing that because if it is not done properly, it can freeze up your computer. Also could you explain what you mean when you say

    Quote

    [SIZE=13px]if there is a way to make small icon in blow taskbar for the user form[/SIZE]

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Thank you for your help , but last thing can you help to convent the code I post it earlier to 64 , to try both way.. really appreciated your help .

  • If you mean convert to 64 bit, I'm afraid I don't have the expertise to do that. :(

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • You are very welcome. :)

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!