Results 1 to 2 of 2
  1. #1
    gopherking is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    71

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

    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:

    Code:
    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!!
    Last edited by gopherking; 02-14-2012 at 09:00 AM. Reason: Modified code to wrap within screen.

  2. #2
    gopherking is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2011
    Posts
    71
    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:

    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:

    Code:
    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:

    Code:
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Importing files into Access 2007
    By rjani1 in forum Access
    Replies: 6
    Last Post: 07-19-2011, 09:33 AM
  2. Replies: 6
    Last Post: 10-14-2010, 08:33 AM
  3. Access 2007 open *.hlp
    By Mclaren in forum Programming
    Replies: 4
    Last Post: 08-17-2010, 06:44 AM
  4. Upload files to access 2007 through web
    By karthikcoep in forum Programming
    Replies: 0
    Last Post: 08-23-2009, 10:04 PM
  5. What Database is Open? (Access 2007)
    By jhrBanker in forum Access
    Replies: 3
    Last Post: 06-06-2009, 09:00 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums