Opening a ReadOnly file in Excel VBA

[Demon]

hi people - hope you can help with this seemingly simple
bit of code!

i have the line...

Application.Workbooks.Open (JobListPath)

which obviously opens a workbook. but the workbook it
opens is readonly and i want to disable the "Readonly
dialog box" that pops up. so i added...

Application.DisplayAlerts = False
Application.Workbooks.Open (JobListPath)

but this does not seem to work, so i changed the line to...

Application.Workbooks.Open
(JobListPath,IgnoreReadOnlyRecommended:=true)

but the syntax checker is complaining!

what am i doing wrong!?!?!

thanks in advance...

demon ^waaaah^

 

[Shunt]

Try this:

Application.Workbooks.Open _
Filename:=JobListPath, IgnoreReadOnlyRecommended:=True

Shunt

 

[Tom Ogilvy]

syntax rules say you don't enclose arguments in parentheses unless you use
the Call statement or you are calling a function and it is returning a
value. Since you are not doing that, you shouldn't use parentheses around
your arguments.

 

[Demon]

hmmm i never knew that - it works ok with other functions.
i have been programming in VBA for years and just picked
up stuff along the way - i suppose that is the trouble
with self-teaching ;op

thanks

 

[Myrna Larson]

It didn't work because you put the argument(s) in parentheses.

"Help" isn't much help on this issue, but...

(1) All methods are really functions, and return a value.

(2) In VBA you can choose to ignore the return value of a function, i.e. treat it as a Sub.

(3) If you are treating the method as a function and you want to capture the returned value in a
variable, you would use the parentheses, just as you would when calling any other function, i.e.

Set WB = Workbooks.Open(FileName:=.....,IgnoreReadOnlyRecommended:=True)

(4) If you don't care about the return value, i.e. you are treating this as a Sub and you don't
have that leading "Set WB = ", then you must NOT use parentheses.

(5) If you ignore point #4 and use the parentheses without the "Set WB =", they tell VB to pass
the argument "ByCopy", i.e. create a copy of the argument and pass that copy by reference. The
purpose is to mimic a ByVal argument. If you do that, you have to put each argument inside its
own set of parentheses.

(6) You didn't get into trouble with the 1st statement, "Application.Workbooks.Open
(JobListPath)", because you supplied only one argument and you didn't use the named argument
syntax.

In summary, the rules are

1. If you are calling a VBA Sub (or a treating a Method as a Sub), you use parentheses around
the arguments ONLY if you use the Call keyword, i.e. Call Workbooks.Open(.....). If you write it
without using Call, you don't use parens.

2. If you are calling a Function, you must put the arguments in parens.