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.

 

[D-Zama]

Still get read only message

I am using a variable (x) to designate the path to the file I want to be read only when it opens...and am running this code in the "SheetChange" event and I still get the read only message...any ideas?

Application.Workbooks.Open Filename:=x, IgnoreReadOnlyRecommended:=True

 

[anchan42]

Might be about one year late but I had have the same problem and it was solved by using ReadOnly:=True.

 

[VBA_basher]

Locked files

Hi, I am still having the problem with the locked file. I know it is locked, but I just want to extract data, not save it. So read-only should be fine.
I use the following:
Set wb = Application.Workbooks.Open(path, ignorereadonlyrecommended:=True, editable:=False, notify:=False, ReadOnly:=True)


But the first thing that happens is the 'Save-as' popup.

Anyone with bright ideas?

Ciao,

 

[anchan42]

This is my code.

Set appExcel = New Excel.Application
appExcel.DisplayAlerts = False
Set wkb = appExcel.Workbooks.Open(Filename:=strFileName, ReadOnly:=True)

 

[VBA_basher]

Your solution gives the same result. Excel gives the popop with Save-As "Copy of strFilename". It almost looks as Excel wants to write to the file during opening, but thinks it is Write-protected. It is not write protected, because when I open 'filename' I am allowed to save.

 

[anchan42]

When in your code that exel try to save as? Have you try to step through the code line by line?
My guess is that Exel try to save as when your code closes the file.

Try to cut your code down to minimum like just open and close without doing anything. If that works, your problem is not the open and close statement.

Perhaps you could post your code here.