Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Method Open of Object Workbooks Failed

This is a discussion on Method Open of Object Workbooks Failed within the Excel Questions forums, part of the Question Forums category; I need some help with this error. I have some VBA code in Outlook that runs whenever a task reminder ...

  1. #1
    New Member
    Join Date
    Apr 2009
    Posts
    18

    Question Method Open of Object Workbooks Failed

    I need some help with this error. I have some VBA code in Outlook that runs whenever a task reminder goes off. The code is supposed to open a workbook, send email based on the info in the workbook, then close the workbook and quit excel.

    The task reminders are set to go off every two hours. Sometimes this will work fine for days and then randomly (as far as I can tell) I get the error: "Method Open of Object Workbooks failed". If I press Debug and then Run without changing anything at all, it works fine.

    I am using Outlook 2007 and Excel 2007. The workbook is a shared workbook on a network drive.

    I can post code if needed, but will have to go to that computer.

    Why do I occasionally get this error when the workbook definitely exists and has the same name and path? What does entering debug mode do that then allows the macro to run without error?

  2. #2
    Board Regular
    Join Date
    Feb 2003
    Location
    Luton, England.
    Posts
    8,115

    Default Re: Method Open of Object Workbooks Failed

    A common problem is that server speed changes depending on how many people using it. So your macro may run better at 3.00am

    A worksheet takes time to open, so it could be that the code tries to run the next line before it has finished. I could be wrong.

    A simple thing that often works is to stop the code for a couple of seconds to allow things to happen (depending how long the workbook takes to open). So try adding code :
    Code:
        Workbooks.Open "F:\book1.xls"
        DoEvents
        Application.Wait Now + TimeValue("00:00:05")  ' wait 5 seconds (might work without this line)
    Regards
    BrianB (using XL2003 & 2010)
    Most problems occur from starting at the wrong place.
    Use a cup of coffee to speed up all Windows processes.
    It is easy until you know how.
    **FORMATTED/COMMENTED CODE IS MORE LIKELY TO GET A REPLY

  3. #3
    New Member
    Join Date
    Apr 2009
    Posts
    18

    Default Re: Method Open of Object Workbooks Failed

    Thanks for the response. This is really starting to annoy me.
    The macro has to run every two hours, so just running at 3:00 AM is not really an option.

    I added DoEvents a couple of days ago and still have this issue.
    Today I added the wait time. I'll see how that goes.
    It does seem very logical that the time it takes to open the file could be the issue.

    In the meantime, I am open to other suggestions, if anyone has any.

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    68,848

    Default Re: Method Open of Object Workbooks Failed

    Can you post your Outlook code please? It may be this:

    http://support.microsoft.com/kb/178510/
    Microsoft MVP - Excel

  5. #5
    New Member
    Join Date
    Apr 2009
    Posts
    18

    Default Re: Method Open of Object Workbooks Failed

    The code is below. I tried to take out lots of the unnecessary parts. There is another part that sorts messages into Outlook folders. I left everything that relates to Excel.

    This code gets activated when an outlook reminder goes off.


    Code:
    Private Sub Application_Reminder(ByVal Item As Object)
        Dim InboxItem As Object
        Dim myForward As Object
        Dim Inbox As MAPIFolder
        Dim DestinationFolder As MAPIFolder
        Dim exlSht As Worksheet
        Dim FolderName As String
        Set Inbox = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
        Dim LRall As Long
        Dim objOLApp As Outlook.Application
        Set objOLApp = New Outlook.Application
       
       
        If Item.Subject = "send emails" Then
       
        strFilepath = "Path\Workbook.xlsm"
            If strFilepath = False Then
                Excel.Application.Quit
                Exit Sub
            End If
        Set exlSht = Excel.Application.Workbooks.Open(strFilepath).Worksheets("Worksheet")
       
        LRall = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        For i = 2 To LRall
        For Each InboxItem In Inbox.Folders("FolderName").Items
            Debug.Print InboxItem
            If InboxItem.SenderName = "specificsender@domain.com" Then
                If InboxItem.Subject = "Subject " & Cells(i, 4).Value & "-" & Cells(i, 5).Value Then
                    InboxItem.UnRead = False
                    Set myForward = InboxItem.forward
                    myForward.BCC = Cells(i, 7).Value
                    myForward.Send
                    Cells(i, 8) = Cells(i, 8) + 1
                    Cells(i, 9).Value = Date
                   
                    Set Conf = InboxItem.forward
                    With Conf
                        .To = Cells(i, 3).Value
                        .Subject = "Subject"
                        .Body = "Body"
                        .Send
                        Cells(i, 19).Value = Cells(i, 19) + 1
                    End With
                End If
     
            If InboxItem.Subject = "Different Subject " & Cells(i, 4).Value & "-" & Cells(i, 5).Value Then
                InboxItem.UnRead = False
                Cells(i, 8) = Cells(i, 8) + 1
                Cells(i, 9).Value = Date
               
                Set Conf = InboxItem.forward
                
                With Conf
                .To = Cells(i, 3).Value
                .Subject = "Subject"
                .Body = "Body"
                .Send
                Cells(i, 19).Value = Cells(i, 19) + 1
                End With
           
            End If
            End If
            Next InboxItem
        Next i
     
    ActiveWorkbook.Save
    Excel.Application.Quit
    Set exlSht = Nothing
    End If
     
    End Sub

  6. #6
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    68,848

    Default Re: Method Open of Object Workbooks Failed

    Is that code in Outlook? If so there are several Excel properties that need qualifying, eg: Cells and ActiveWorkbook.
    Microsoft MVP - Excel

  7. #7
    New Member
    Join Date
    Apr 2009
    Posts
    18

    Default Re: Method Open of Object Workbooks Failed

    Yes. This is all in Outlook.
    By qualifying, do you mean using....
    Workbook().Sheet().Cell instead of just saying Cell

    Where can I find out more about what needs qualified and how to do this?
    Also, would this cause the error I am receiving?

  8. #8
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    68,848

    Default Re: Method Open of Object Workbooks Failed

    All the Excel properties/methods need to be fully qualified. Did you read the link I posted? Here's another one:

    http://support.microsoft.com/kb/319832
    Microsoft MVP - Excel

  9. #9
    New Member
    Join Date
    Jun 2008
    Posts
    23

    Default Re: Method Open of Object Workbooks Failed

    just a shot in the dark, but as it's a shared workbook etc.. could it be that someone else already has the file open thus meaning the code can not open it?

  10. #10
    Board Regular
    Join Date
    Feb 2003
    Location
    Luton, England.
    Posts
    8,115

    Thumbs down Re: Method Open of Object Workbooks Failed

    It really highlights that we cannot always assume that our code is correct, even if it produces the correct results.

    ANDREW
    Thanks for posting the MS references. I have never come across that.

    AKFORSYT
    My 2 pennyworth (2 cents the rest of the world)
    The code you have posted seems more appropriate to use in Excel because it makes an explicit new instance of Outlook

    Dim objOLApp As Outlook.Application
    Set objOLApp = New Outlook.Application

    this is not really needed because Outlook is running already.


    but does not make an explicit instance of Excel

    Dim objXL As Excel.Application
    Set objXL = New Excel.Application

    Because it is running in Outlook you also need to qualify all references to Excel and its objects with objXL.
    So this line is a double error possibility :-

    LRall = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Firstly you have not qualified which sheet to use for Find (although it assumes Activesheet), secondly you have not qualified with the Excel object. So this should be

    LRall = exlSht.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Having read the MS KB article it seems that it is safer to use late binding so the final code will include :-

    Dim objXL as Object
    Dim exlSht as Object

    Set objXL = New Excel.Application
    Set exlSht = objXL.Workbooks.Open(strFilepath).Worksheets("Worksheet")

    LRall = exlSht.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row


    I am still not sure not sure if that last line should be this, or not (my guess is not because the XL link has been made already) :-

    LRall = objXL.exlSht.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    I think we would be interested to know how this turns out, because in the past I have assumed that similar problems were due to incorrect closure of the "child" (external) application. Although the remedy has been to attend to that. The KB article hints that this is part of the overall problem.

    Which brings me to another point. In this example you need to make sure that the instances of Excel and the objects you create are removed, otherwise they remain in memory. It would seem especially necessary in this case, where you are automatically running the code several times.

    So :

    XLobj.ActiveWorkbook.Save
    XLobj.ActiveWorkBook.Close
    XLobj.Quit
    Set exlSht=Nothing
    Set XLobj = Nothing

    If you decide you do need another instance of Outlook, you need to do the same for that too.
    Regards
    BrianB (using XL2003 & 2010)
    Most problems occur from starting at the wrong place.
    Use a cup of coffee to speed up all Windows processes.
    It is easy until you know how.
    **FORMATTED/COMMENTED CODE IS MORE LIKELY TO GET A REPLY

Page 1 of 2 12 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com