MS 2007 files won't open from VBA in Access 2007

[gopherking]

Ok, I have been searching the internet for a possible solution to this issue, but have yet to find a valid solution that will work for me. I am using Application.FollowHyperlink to open files located on our network that are associated with a request in my database. This functionality works great for .pdf, .doc, .xls, .ppt, etc. files but it returns the Runtime Error 490 Cannot open the specified file when a MS 2007 format file is attempted to be opened (.xslx, .docx, etc.). In addition, .msg files also cannot be opened via this method, but I have a work around for this and told my users to deal with it (they respond well to this sometimes).

So, does anyone know of a SIMPLE way to be able to open any type of file via VBA code in an Access 2007 database? I am trying to stay away from having to enter application specific code to handle the different files that do not open using Application.FollowHyperlink command. Below is the code that I am currently using which does work as noted above for most files:

Private Sub Open_Btn_Click()
On Error Goto ErrorHandler
Dim PPCAttachDir As String
Dim PPCAttachFile As String
DIm PPCOpenFile As String

PPCAttachDIR = gUDrive & "\" & Me.RequestID
PPCAttachFile = Me.AttachFile
PPCOpenFile = PPCAttachDir & "\" & PPCAttachFile

Debug.Print PPCOpenFile

If PPCAttachFile Like "*.msg" Then
MsgBox "Outlook Message files cannot be opened from within the database.  You must open the email " & _
"file directly from the attachment folder." & vbcrlf & vbcrlf & "The attachment folder will " & _
"be opened for you upon closing this message box.", vbokonly+vbInformation, "Cannot open .MSG files"
Application.FollowHyperLink PPCAttachDir

ElseIf Len(Dir(PPCOpenFile, vbNormal)) = 0 Then
MsgBox "The file " & PPCAttachfile & " has been moved, renamed, or deleted.  Please click on " & _
"the folder link on the Request Form to verify the file name and update it as needed." _
, vbOkOnly + vbExclamation, "File Not Found"
Goto ExitHere
Else
Application.FollowHyperLink PPCOpenFile, , True
End If

ExitHere:
Exit Sub

ErrorHandler:
Call UnexpectedERror(Err.Number, Err.Description, Err.Source)
Resume ExitHere
EndSub

By the way, the gUDrive is the UNC path to our network drive which is stored as a Global Variable, not the mapped drive letter on the user's PC. This is to ensure that if a user has modified their mappings from the norm, it will still locate the file because of the UNC path being used. It also allows me a simple update to the database if the UNC path is ever changed to a new server (recently happened), so I only need to make one change in the database as opposed to many!!

[gopherking]

Ok, I didn't receive any SIMPLE ways to do this, but I did come up with a solution which is working like a charm for Excel and Word 2007 files (.xlsx and .docx) (thanks to multiple threads and guidelines from others). I figured that I would post it here just in case anyone else can benefit from it. SO ...

FIRST AND FOREMOST --- You must select the Microsoft Excel 12.0 Object Library and the Microsoft Word 12.0 Object Library from the Tools --> References in the Visual Basic editor. Without these selected, the below code WILL NOT WORK!!!

In my button OnClick event, I included the following code:

On Error GoTo ErrorHandler
    Dim FileNm As String
    Dim PPCAttachDir As String
    Dim PPCOpenFile As String
    Dim xlApp As Excel.Application
    
    FileNm = Me.FileName
    PPCAttachDir = "[Enter your static folder structure here or use a Variable (I use a global variable)] " & FileNm
    PPCOpenFile = PPCAttachDir & "\" & PPCAttachFile
    
Debug.Print PPCOpenFile
    
    If Len(Dir(PPCOpenFile, vbNormal)) = 0 Then
        MsgBox "The file " & PPCAttachFile & " has been moved, renamed, or deleted.  Please click on " & _
            "the folder link on the Request Form to verify the file name and update it as needed." _
            , vbOKOnly + vbExclamation, "File Not Found"
            GoTo ExitHere
        ElseIf PPCAttachFile Like "*.xlsx*" Then
            Call OpenXLSXFile(PPCOpenFile)      'Located in basAttachments
            GoTo ExitHere
            ElseIf PPCAttachFile Like "*.docx*" Then
                Call OpenDOCXFile(PPCOpenFile)      'Located in basAttachments
                GoTo ExitHere
                ElseIf PPCAttachFile Like "*.msg" Then
                   'I still haven't figured out how to open .msg files yet, so I have this work around for my users
                    MsgBox "Outlook Message files cannot be opened from within the database.  You must " & _
                    "open the email file directly from the attachment folder." & vbCrLf & vbCrLf & "The " & _
                    "attachment folder will be opened for you upon closing this message box.", _
                    vbOKOnly + vbInformation, "Cannot Open .MSG files"
                Application.FollowHyperlink PPCAttachDir
                Else
                    Application.FollowHyperlink PPCOpenFile
    End If
ExitHere:
    Exit Sub
ErrorHandler:
    Call UnexpectedError(Err.Number, Err.Description, Err.Source)   'Located in basMsgBoxes
    Resume ExitHere
End Sub

So, the two specific calls in the code above are probably what you are wondering what I did. Ok, here is the code that is run when the OpenXLSXFile(PPCOpenFile) is called:

Sub OpenXLSXFile(OpenFile As String)
    Dim xlApp As Excel.Application
    
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    xlApp.Workbooks.Open OpenFile, True, False
    Set xlApp = Nothing
    
End Sub

And here is the code for when the OpenDOCXFile(PPCOpenFile) is called:

Sub OpenDOCXFile(OpenFile As String)
    Dim wdApp As Word.Application
    
    Set wdApp = CreateObject("Word.Application")
    wdApp.Visible = True
    wdApp.Documents.Open OpenFile, True, False
    Set wdApp = Nothing
End Sub

I hope that this will help anyone else looking for a solution. Unfortunately I wasn't able to pull together a simple/all in one solution, but this one is pretty simple in that if I find any other files that I can't open, I should be able to add another If Statement to my main code and then add a new Module Procedure to be called to open the file.