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

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

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

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

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