The first thing I did to try this was manually open a file read-only
while in record-macro mode and save the macro. There is nothing in the
macro to indicate the file should be opened read-only. In fact, if I
then reopen the file using the macro it is NOT opened read-only.

So, the big question is is there no way to open a file read-only via a
macro OR is the 'record macro' function busted. btw - I did check the
open methods and there are no parameters documented to control this
either. I also discovered a 'permissions' object that sounds like if
might help, but there has been nothing recorded in my macro and so don't
see any good examples of how I might use it.

-mark

Workbooks.Open should have a readonly named argument.

Sub testopen()
Const path As String = "C:\temp\test\abccompany#1.xls"

Workbooks.Open Filename:=path, ReadOnly:=True
End Sub



"Mark Seger" wrote:

The first thing I did to try this was manually open a file read-only
while in record-macro mode and save the macro. There is nothing in the
macro to indicate the file should be opened read-only. In fact, if I
then reopen the file using the macro it is NOT opened read-only.

So, the big question is is there no way to open a file read-only via a
macro OR is the 'record macro' function busted. btw - I did check the
open methods and there are no parameters documented to control this
either. I also discovered a 'permissions' object that sounds like if
might help, but there has been nothing recorded in my macro and so don't
see any good examples of how I might use it.

-mark

good guess but wrong one. There is no readonly argument. To verify
this I recorded a macro of opening a file, closed it and added in your
suggestion even though the help for that function doesn't list it (nor
does the object model). Sure enough, when I try to run it I get the
error "Named Argument Not Found".

As I said below in my base posting, when you open a file in readonly
mode NOTHING is recorded in the macro and if you try running the macro
the file does not open read-only. 8-(

-mark

JMB wrote:

Workbooks.Open should have a readonly named argument.

Sub testopen()
Const path As String = "C:\temp\test\abccompany#1.xls"

Workbooks.Open Filename:=path, ReadOnly:=True
End Sub



"Mark Seger" wrote:


The first thing I did to try this was manually open a file read-only
while in record-macro mode and save the macro. There is nothing in the
macro to indicate the file should be opened read-only. In fact, if I
then reopen the file using the macro it is NOT opened read-only.

So, the big question is is there no way to open a file read-only via a
macro OR is the 'record macro' function busted. btw - I did check the
open methods and there are no parameters documented to control this
either. I also discovered a 'permissions' object that sounds like if
might help, but there has been nothing recorded in my macro and so don't
see any good examples of how I might use it.

-mark

Hi Mark,

Workbook.Open does indeed have a ReadOnly argument that can be used the way
JMB suggested. See help.

Workbook also has a ReadOnly property that you can read, eg

bRdOnly = Activeworkbook.readonly

I don't follow what you want or don't want to do. However one thing you
can't do is save a readonly workbook, eg one that's deliberatly been opened
as readonly, opened for a second time, or already open in another instance
of Excel.

If your macro is trying to do that, check the readonly property as above.

Regards,
Peter T

"Mark Seger" wrote in message
...
good guess but wrong one. There is no readonly argument. To verify
this I recorded a macro of opening a file, closed it and added in your
suggestion even though the help for that function doesn't list it (nor
does the object model). Sure enough, when I try to run it I get the
error "Named Argument Not Found".

As I said below in my base posting, when you open a file in readonly
mode NOTHING is recorded in the macro and if you try running the macro
the file does not open read-only. 8-(

-mark

JMB wrote:

Workbooks.Open should have a readonly named argument.

Sub testopen()
Const path As String = "C:\temp\test\abccompany#1.xls"

Workbooks.Open Filename:=path, ReadOnly:=True
End Sub



"Mark Seger" wrote:


The first thing I did to try this was manually open a file read-only
while in record-macro mode and save the macro. There is nothing in the
macro to indicate the file should be opened read-only. In fact, if I
then reopen the file using the macro it is NOT opened read-only.

So, the big question is is there no way to open a file read-only via a
macro OR is the 'record macro' function busted. btw - I did check the
open methods and there are no parameters documented to control this
either. I also discovered a 'permissions' object that sounds like if
might help, but there has been nothing recorded in my macro and so don't
see any good examples of how I might use it.

-mark

I hear what you're saying but maybe we're talking about two different
things and I guess I wasn't clear enough. Remember, I'm trying to do
this via macros. The diagnostic method I'm using to track this down is
to record a macro, perform an operation and then look to see what got
recorded. When I open an xls file I can see that 'workbook.open' is
indeed being executed as you suggest and it does indeed take a ReadOnly
parameter. However, if I open a text file it gets opened with
"Workbook.OpenText" and that doesn't take a ReadOnly parameter.

However loking at your example gives me hope - are you suggesting I
could set the property of a workbook to readonly after I open it? This
is certainly something you can't do via the user interface to excel, or
if you can I couldn't find it.

-mark

Peter T wrote:
Hi Mark,

Workbook.Open does indeed have a ReadOnly argument that can be used the way
JMB suggested. See help.

Workbook also has a ReadOnly property that you can read, eg

bRdOnly = Activeworkbook.readonly

I don't follow what you want or don't want to do. However one thing you
can't do is save a readonly workbook, eg one that's deliberatly been opened
as readonly, opened for a second time, or already open in another instance
of Excel.

If your macro is trying to do that, check the readonly property as above.

Regards,
Peter T

"Mark Seger" wrote in message
...

good guess but wrong one. There is no readonly argument. To verify
this I recorded a macro of opening a file, closed it and added in your
suggestion even though the help for that function doesn't list it (nor
does the object model). Sure enough, when I try to run it I get the
error "Named Argument Not Found".

As I said below in my base posting, when you open a file in readonly
mode NOTHING is recorded in the macro and if you try running the macro
the file does not open read-only. 8-(

-mark

JMB wrote:


Workbooks.Open should have a readonly named argument.

Sub testopen()
Const path As String = "C:\temp\test\abccompany#1.xls"

Workbooks.Open Filename:=path, ReadOnly:=True
End Sub



"Mark Seger" wrote:



The first thing I did to try this was manually open a file read-only
while in record-macro mode and save the macro. There is nothing in the
macro to indicate the file should be opened read-only. In fact, if I
then reopen the file using the macro it is NOT opened read-only.

So, the big question is is there no way to open a file read-only via a
macro OR is the 'record macro' function busted. btw - I did check the
open methods and there are no parameters documented to control this
either. I also discovered a 'permissions' object that sounds like if
might help, but there has been nothing recorded in my macro and so don't
see any good examples of how I might use it.

-mark

Hi Mark

This is what I put in answer to your other question further down the
page:


Good afternoon Mark

Try this

Workbooks.Open Filename:= "C:\TestFile.xls", ReadOnly:=True

The ReadOnly function does seem to be documented, but not to a great
extent. But it works.

It certainlyworks in Excel 2003.

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=376539

Ok I just tried this. Upon closer inspection it looks 'readonly' is a
readonly property, it can't be set. So I'm back to where I started.
But keep those suggestions coming...
-mark

Peter T wrote:

Hi Mark,

Workbook.Open does indeed have a ReadOnly argument that can be used the way
JMB suggested. See help.

Workbook also has a ReadOnly property that you can read, eg

bRdOnly = Activeworkbook.readonly

I don't follow what you want or don't want to do. However one thing you
can't do is save a readonly workbook, eg one that's deliberatly been opened
as readonly, opened for a second time, or already open in another instance
of Excel.

If your macro is trying to do that, check the readonly property as above.

Regards,
Peter T

"Mark Seger" wrote in message
...

good guess but wrong one. There is no readonly argument. To verify
this I recorded a macro of opening a file, closed it and added in your
suggestion even though the help for that function doesn't list it (nor
does the object model). Sure enough, when I try to run it I get the
error "Named Argument Not Found".

As I said below in my base posting, when you open a file in readonly
mode NOTHING is recorded in the macro and if you try running the macro
the file does not open read-only. 8-(

-mark

JMB wrote:


Workbooks.Open should have a readonly named argument.

Sub testopen()
Const path As String = "C:\temp\test\abccompany#1.xls"

Workbooks.Open Filename:=path, ReadOnly:=True
End Sub



"Mark Seger" wrote:



The first thing I did to try this was manually open a file read-only
while in record-macro mode and save the macro. There is nothing in the
macro to indicate the file should be opened read-only. In fact, if I
then reopen the file using the macro it is NOT opened read-only.

So, the big question is is there no way to open a file read-only via a
macro OR is the 'record macro' function busted. btw - I did check the
open methods and there are no parameters documented to control this
either. I also discovered a 'permissions' object that sounds like if
might help, but there has been nothing recorded in my macro and so don't
see any good examples of how I might use it.

-mark

are you suggesting I
could set the property of a workbook to readonly after I open it?

No - the readonly property is read only, as stated in help.

Afraid your additional comments has not shed any light on your issue, at
least not to me!

Regards,
Peter T

"Mark Seger" wrote in message
...
I hear what you're saying but maybe we're talking about two different
things and I guess I wasn't clear enough. Remember, I'm trying to do
this via macros. The diagnostic method I'm using to track this down is
to record a macro, perform an operation and then look to see what got
recorded. When I open an xls file I can see that 'workbook.open' is
indeed being executed as you suggest and it does indeed take a ReadOnly
parameter. However, if I open a text file it gets opened with
"Workbook.OpenText" and that doesn't take a ReadOnly parameter.

However loking at your example gives me hope - are you suggesting I
could set the property of a workbook to readonly after I open it? This
is certainly something you can't do via the user interface to excel, or
if you can I couldn't find it.

-mark

Peter T wrote:
Hi Mark,

Workbook.Open does indeed have a ReadOnly argument that can be used the
way
JMB suggested. See help.

Workbook also has a ReadOnly property that you can read, eg

bRdOnly = Activeworkbook.readonly

I don't follow what you want or don't want to do. However one thing you
can't do is save a readonly workbook, eg one that's deliberatly been
opened
as readonly, opened for a second time, or already open in another
instance
of Excel.

If your macro is trying to do that, check the readonly property as
above.

Regards,
Peter T

"Mark Seger" wrote in message
...

good guess but wrong one. There is no readonly argument. To verify
this I recorded a macro of opening a file, closed it and added in your
suggestion even though the help for that function doesn't list it (nor
does the object model). Sure enough, when I try to run it I get the
error "Named Argument Not Found".

As I said below in my base posting, when you open a file in readonly
mode NOTHING is recorded in the macro and if you try running the macro
the file does not open read-only. 8-(

-mark

JMB wrote:


Workbooks.Open should have a readonly named argument.

Sub testopen()
Const path As String = "C:\temp\test\abccompany#1.xls"

Workbooks.Open Filename:=path, ReadOnly:=True
End Sub



"Mark Seger" wrote:



The first thing I did to try this was manually open a file read-only
while in record-macro mode and save the macro. There is nothing in
the
macro to indicate the file should be opened read-only. In fact, if I
then reopen the file using the macro it is NOT opened read-only.

So, the big question is is there no way to open a file read-only via a
macro OR is the 'record macro' function busted. btw - I did check the
open methods and there are no parameters documented to control this
either. I also discovered a 'permissions' object that sounds like if
might help, but there has been nothing recorded in my macro and so
don't
see any good examples of how I might use it.

-mark

Dominic - As I said before you can only use ReadOnly with
Workbooks.Open NOT Workbooks.OpenText and I have to use OpenText because
I want to open a textfile not an xls file.

Afraid your additional comments has not shed any light on your issue, at
least not to me!

Would it help it I changed my quetion to "how do you open a text file in
read-only mode with a VB macro?" as that is really at the heart of it
all. I don't see any way to do it.

-mark

Would it help it I changed my quetion to "how do you open a text file in
read-only mode with a VB macro?" as that is really at the heart of it
all. I don't see any way to do it.

OK, I get it now.

iso of the OpenText method use Import Text. Possibly into a dummy workbook
that you have previously saved as readonly.

Lot's more info from Chip Pearson

http://www.cpearson.com/excel/imptext.htm

Regards,
Peter T

you can use workbooks.open to open a text file. just set the format named
argument to specify the delimiter you need as well as the readonly argument.

see help for the open method.

"dominicb" wrote:


Hi Mark

This is what I put in answer to your other question further down the
page:


Good afternoon Mark

Try this

Workbooks.Open Filename:= "C:\TestFile.xls", ReadOnly:=True

The ReadOnly function does seem to be documented, but not to a great
extent. But it works.

It certainlyworks in Excel 2003.

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=376539

I appreciate your help but... I can't use workbooks.open because it
doesn't support all the options for opening delimited text files in the
way I need to. For example you can't select whether or not it's to
treat multiple delimeters as one or select mulitple delimeters.
-mark

JMB wrote:

you can use workbooks.open to open a text file. just set the format named
argument to specify the delimiter you need as well as the readonly argument.

see help for the open method.

"dominicb" wrote:


Hi Mark

This is what I put in answer to your other question further down the
page:


Good afternoon Mark

Try this

Workbooks.Open Filename:= "C:\TestFile.xls", ReadOnly:=True

The ReadOnly function does seem to be documented, but not to a great
extent. But it works.

It certainlyworks in Excel 2003.

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=376539

I've solved it in a very sleazy way! These discussions got me thinking
that if excel is able to open a text delimited file as read-only and if
the object model doesn't support that excel must be either using an
undocumented feature (wouldn't be the first time) OR something else is
going on.

While this may not be what's happening, if I set the file permissions to
'read-only' before I try to open it, Excel happily opens it read-only
for me. Then I can reset it to read-write! slick or sick? I'll leave
it to someone else to decide... 9-)

-mark

Mark Seger wrote:

The first thing I did to try this was manually open a file read-only
while in record-macro mode and save the macro. There is nothing in the
macro to indicate the file should be opened read-only. In fact, if I
then reopen the file using the macro it is NOT opened read-only.

So, the big question is is there no way to open a file read-only via a
macro OR is the 'record macro' function busted. btw - I did check the
open methods and there are no parameters documented to control this
either. I also discovered a 'permissions' object that sounds like if
might help, but there has been nothing recorded in my macro and so don't
see any good examples of how I might use it.

-mark

sounds good. i thought about opentext having additional options that open
may not have after i posted.

sounds like a good solution to me (changing the file access before or after
opening the file).

if you have any additional problems with it, you might look at using the
changefileaccess method, which changes the file access after opening the file.



"Mark Seger" wrote:

I've solved it in a very sleazy way! These discussions got me thinking
that if excel is able to open a text delimited file as read-only and if
the object model doesn't support that excel must be either using an
undocumented feature (wouldn't be the first time) OR something else is
going on.

While this may not be what's happening, if I set the file permissions to
'read-only' before I try to open it, Excel happily opens it read-only
for me. Then I can reset it to read-write! slick or sick? I'll leave
it to someone else to decide... 9-)

-mark

Mark Seger wrote:

The first thing I did to try this was manually open a file read-only
while in record-macro mode and save the macro. There is nothing in the
macro to indicate the file should be opened read-only. In fact, if I
then reopen the file using the macro it is NOT opened read-only.

So, the big question is is there no way to open a file read-only via a
macro OR is the 'record macro' function busted. btw - I did check the
open methods and there are no parameters documented to control this
either. I also discovered a 'permissions' object that sounds like if
might help, but there has been nothing recorded in my macro and so don't
see any good examples of how I might use it.

-mark

changefileaccess - good to know but as I'm really doing everything
in perl I can just as easily use perl's chmod function.

This stuff is all pretty slick, but unfortunately if you're not one with
the microsoft environment, as I'm not, you're in trouble. Fortunately
newsgroups are where the 'force' really is...

-mark

JMB wrote:
sounds good. i thought about opentext having additional options that open
may not have after i posted.

sounds like a good solution to me (changing the file access before or after
opening the file).

if you have any additional problems with it, you might look at using the
changefileaccess method, which changes the file access after opening the file.



"Mark Seger" wrote:


I've solved it in a very sleazy way! These discussions got me thinking
that if excel is able to open a text delimited file as read-only and if
the object model doesn't support that excel must be either using an
undocumented feature (wouldn't be the first time) OR something else is
going on.

While this may not be what's happening, if I set the file permissions to
'read-only' before I try to open it, Excel happily opens it read-only
for me. Then I can reset it to read-write! slick or sick? I'll leave
it to someone else to decide... 9-)

-mark

Mark Seger wrote:


The first thing I did to try this was manually open a file read-only
while in record-macro mode and save the macro. There is nothing in the
macro to indicate the file should be opened read-only. In fact, if I
then reopen the file using the macro it is NOT opened read-only.

So, the big question is is there no way to open a file read-only via a
macro OR is the 'record macro' function busted. btw - I did check the
open methods and there are no parameters documented to control this
either. I also discovered a 'permissions' object that sounds like if
might help, but there has been nothing recorded in my macro and so don't
see any good examples of how I might use it.

-mark

Since you really only want the data, how about just opening the text file the
normal way.

Then copy that (now active) worksheet to a different workbook (new or existing)
and close the text file.

Option Explicit
Sub testme()

Dim wkbk1 As Workbook
Dim wkbk2 As Workbook

Workbooks.OpenText Filename:="C:\my documents\excel\test.txt", _
Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=False, _
Space:=True, Other:=True, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1))

Set wkbk1 = ActiveWorkbook

ActiveSheet.Copy

Set wkbk2 = ActiveWorkbook

wkbk1.Close savechanges:=False

End Sub

Mark Seger wrote:

changefileaccess - good to know but as I'm really doing everything
in perl I can just as easily use perl's chmod function.

This stuff is all pretty slick, but unfortunately if you're not one with
the microsoft environment, as I'm not, you're in trouble. Fortunately
newsgroups are where the 'force' really is...

-mark

JMB wrote:
sounds good. i thought about opentext having additional options that open
may not have after i posted.

sounds like a good solution to me (changing the file access before or after
opening the file).

if you have any additional problems with it, you might look at using the
changefileaccess method, which changes the file access after opening the file.



"Mark Seger" wrote:


I've solved it in a very sleazy way! These discussions got me thinking
that if excel is able to open a text delimited file as read-only and if
the object model doesn't support that excel must be either using an
undocumented feature (wouldn't be the first time) OR something else is
going on.

While this may not be what's happening, if I set the file permissions to
'read-only' before I try to open it, Excel happily opens it read-only
for me. Then I can reset it to read-write! slick or sick? I'll leave
it to someone else to decide... 9-)

-mark

Mark Seger wrote:


The first thing I did to try this was manually open a file read-only
while in record-macro mode and save the macro. There is nothing in the
macro to indicate the file should be opened read-only. In fact, if I
then reopen the file using the macro it is NOT opened read-only.

So, the big question is is there no way to open a file read-only via a
macro OR is the 'record macro' function busted. btw - I did check the
open methods and there are no parameters documented to control this
either. I also discovered a 'permissions' object that sounds like if
might help, but there has been nothing recorded in my macro and so don't
see any good examples of how I might use it.

-mark




--

Dave Peterson

"Mark Seger" skrev i melding
...
Would it help it I changed my quetion to "how do you open a text file in
read-only mode with a VB macro?" as that is really at the heart of it
all. I don't see any way to do it.

Mark

Txtfiles are not like that, "opened by another user" el al. Try opening any
textfile in Notepad. Open it again in another instance of notepad. Type and
save in both instances. Open Windows eplorer and delete the file with the
notepads still running. A textfile has no protection whatsoever. So your
code must ensure that it's not deleted or overwritten; Windows just don't
care.

HTH. best wishes Harald

Txtfiles are not like that, "opened by another user" el al. Try opening any
textfile in Notepad. Open it again in another instance of notepad. Type and
save in both instances. Open Windows eplorer and delete the file with the
notepads still running. A textfile has no protection whatsoever. So your
code must ensure that it's not deleted or overwritten; Windows just don't
care.

I don't think that's right. There's no difference between a text file
and some other file other than the bits are in binary - or at least
there shouldn't be. The only reason you're able to open multiple copies
in notepad is because notepad is opening the file in readonly mode,
otherwise you never would have been able to open 2 copies of the same
file with it, which means you have to be very careful when using
notepad. I know I've gotten burned in the past by it when I've
accidentially had multiple instances of the same file opened when I had
too many windows opened for my own good. Most reasonable editors will
NOT let you open multiple copies.

Just to make sure I wrote a 2 line perl script, but I'm sure you could
do this in any language whose default open semanatics are read-only,
that opened a file and did a sleep. Sure enough, if I tried to delete
the file while the script was running I couldn't.

C:\mjsdel \temp\time.txt
C:\temp\time.txt
The process cannot access the file because it is being used by another
process.

-mark

Txtfiles are not like that, "opened by another user" el al. Try opening
any
textfile in Notepad. Open it again in another instance of notepad. Type
and
save in both instances. Open Windows eplorer and delete the file with the
notepads still running. A textfile has no protection whatsoever. So your
code must ensure that it's not deleted or overwritten; Windows just don't
care.

HTH. best wishes Harald

Hi Harald,

I was going to include similar comments in my previous reply to Mark. I
refrained because after a quick check I found that if I open a text file in
Excel with "OpenText", the text file becomes effectively readonly to other
app's.

In Notepad this is only apparent when trying to save the file that's
currently open in Excel. Some other text editors advise on opening the file
that it is ReadOnly.

Also, a text file has readonly, archive and hidden attributes, all of which
can be changed in explorer (right click). I haven't looked into it but I
imagine it's possible to change the readonly attribute of a text file within
a macro before opening it. Not sure if doing that would cause complications
if the file is already open in another app.

Regards,
Peter T

Also, a text file has readonly, archive and hidden attributes, all of which
can be changed in explorer (right click). I haven't looked into it but I
imagine it's possible to change the readonly attribute of a text file within
a macro before opening it. Not sure if doing that would cause complications
if the file is already open in another app.

This shouldn't be a problem as long as 2 things are true:
- you have write access to the file (usually true)
- anyone else who has is opened has done so in readonly mode

The one thing to be aware of is there are 2 different protections are
work here. One is the file access rights (or permissions) which exist
before anyone even opens it. The second is the open mode.

A file whose permission is 'writeable' can be opened in readonly mode,
but a file that doesn't have the 'write' permission (readonly) cannot be
opened in write mode. You would have to change its permission to
'write' before opening it.

-mark

I stand corrected guys.
There are some interesting issues here though: The application protects, or
not, a file against multiuser conflicts. The operating system does, or does
not, a similar thing. If the default behavior is not what we want then we
should know what happens and write code for it.

Besst wishes Harald