Method 'Open' of object 'workbooks' failed 1

[arst06d]

Hi

We have an excel workbook with a button which runs some vba code to open several other workbooks.

Users are reporting an error 1004: Method 'Open' of object 'workbooks' failed

It seems to be random (some users get it, some don't). If the affected user reboots (closing Excel by itself sometimes works) and retries immediately on startup, then it works.

Any ideas please? Is this a problem with Excel or XP?

Thanks

 

[xlbo]

more likely a problem with the code itself and where it is referencing

Please show the lines of code that throw up this error....without that, no-one is going to be able to help

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[arst06d]

Hi
Here's the code

Sub EMAIL()
    Sheets("PR TR").Select
    Range("A1:I115").Select
    Selection.Copy
    Workbooks.Open Filename:="L:\ACCOUNTS\MIDOFF\2005\Markets\PROP E-Mail File.xls"
    Sheets("PRTR").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Windows("PROPNEW.xls").Activate
    Sheets("PR BD").Select
    Range("A1:I140").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("PROP E-Mail File.xls").Activate
    Sheets("PRBD").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Windows("PROPNEW.xls").Activate
    Sheets("PR CP").Select
    Range("A1:I140").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("PROP E-Mail File.xls").Activate
    Sheets("PRCP").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Windows("PROPNEW.xls").Activate
        Sheets("PRFB").Select
    Range("A1:I107").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("PROP E-Mail File.xls").Activate
    Sheets("PRFB").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Windows("PROPNEW.xls").Activate
    Sheets("MTD").Select
    Range("A1:I49").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("PROP E-Mail File.xls").Activate
    Sheets("SUMMARY").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Range("G12").Select
End Sub

This was recorded by one of the users, so the path on the line

Workbooks.Open Filename:="L:\ACCOUNTS\MIDOFF\2005\Markets\PROP E-Mail File.xls"

is correct for their environment.

I cannot recreate this problem at will. I have tried running this code when someone else has the target file open - no problem, just opens read-only.

The only way I can get a similar error is if I have the target workbook open with an edit in progress. In this instance I first get a message that I will lose changes if I reopen - do I want to proceed. If I say no, then the error 1004 occurs.

This scenario does not appear to be applicale as the user does not get the first message re losing changes.

I'm at a loss.

 

[xlbo]

This was recorded by one of the users

my emphasis

Might be correct for 1 - doesn't mean it is correct for all

1st guess is that Mapped Netwrork drives are not re-mapping correctly for some users - therefore when mapped drive is referenced in the Worbooks.Open line, it fails. When user logs off and on again, drives map correctly and everything works...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[arst06d]

Hi Geoff

The drive mapping will be the same for all users in the department - set by group policy. However, I take your point re lost drive mappings and I'll change to UNC notation.

If this was the cause I would have thought that it would throw a "Path/File not Found" message.

Thanks for your help.

David

 

[xlbo]

no probs - it's got to be that line causing the error as the error relates to opening the workbook...



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question