1004 Error on Workbooks.Open when run from inside IE

[DS]

Morning all, and happy Wednesday.

Have a slightly bizarre one to ask you fine people about.

The following Workbooks.Open statement forms part of my code.

Workbooks.Open Filename:=TargetPath & TargetBook, Password:="xxx",
WriteResPassword:="xxxx"

where TargetPath & TargetBook are defined previously.

Now, when running this from within Excel itself, it works fine and opens the
desired workbook.
When running from within IE, I get a 1004 ("Method 'Open' of Object
'Workbooks' Failed") - but only when running as part of the full macro. If I
hit debug, it takes me to that line, and hitting F8 to re-run that line in a
fit of frustration sees it work perfectly, and open the required workbook.

Me no understand. Please save what remains of my hair and tell me why this
is happening, and, more importantly, how the *flipping heck* to stop it!!

thanks in advance,

 

[Norman Jones]

Hi Ds,

Without seeing the preceding code, I would
suggest that you try replacing

Workbooks.Open ...

with something like:

xlApp.> Workbooks.Open ...

 

[Norman Jones]

Hi DS,

Correcting a typo, perhaps, also remove
the argument names:

xlApp.Workbooks.Open TargetPath & TargetBook, , , "xxx", "xxxx"

 

[DS]

Hi Norman,

Thanks for your suggestion, unfortunately, preceding with an "xlapp." just
gives me an "Object Required" error.

The code prior to the workbooks.open statement simply defines the TargetPath
and TargetBook - as I said, these work fine from run within Excel, and ok
when stepping through the code when opened in IE, so it doesn't appear to be
these which is causing the problem. For the sake of completion, though, the
full code concerned (edited) is:

TargetPath = "\\server1\shared\Level1\Level2\Level3\DataTables\"
If Range("Sheet1!C2") = "HeaderRequired" Then
TargetBook = "Table1.xls"
Else
TargetBook = "Table2.xls"
End If

Workbooks.Open Filename:=TargetPath & TargetBook, Password:="xxx",
WritePassword:="xxxx"

 

[DS]

And the corrected (without argument names) version also just delivers a 424
Object Required both in Excel and IE...

Appreciate the help - even though we seem to be going backwards here!!!

Hi DS,

Correcting a typo, perhaps, also remove
the argument names:

xlApp.Workbooks.Open TargetPath & TargetBook, , , "xxx", "xxxx"

 

[Norman Jones]

Hi DS,

Have you tried replacing the named arguments
with position values, as in my later response?

 

[DS]

Hi Norman,

Yep, I had, was getting a 424 due to my own stupidity. I just replaced the
"Workbooks.Open..." with "xlapp.workbooks.open..." - without defining xlapp
and creating the Object. Evidently not enough coffee yet.

I've now done so, and the workbook is opening.

Thanks for your help, much appreciated!