Method Open of Object Workbooks Failed

[akforsyt]

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?

 

[BrianB]

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 :

    Workbooks.Open "F:\book1.xls"
    DoEvents
    Application.Wait Now + TimeValue("00:00:05")  ' wait 5 seconds (might work without this line)

 

[akforsyt]

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.

 

[Andrew Poulsom]

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

http://support.microsoft.com/kb/178510/

 

[akforsyt]

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.

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

 

[Andrew Poulsom]

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

 

[akforsyt]

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?

 

[Andrew Poulsom]

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

 

[PatrickBirch]

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?

 

[BrianB]

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.