Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: save file as read only

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Is there a way to save the xls file as read-only format, using VB?

    Thanks /dan




  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    There's a function in VBA, called

    SetAttr(PathName as String,Attributes as VbFileAttribute)

    which you can use to set the attributes to a file (It has to be closed, if I'm correct)
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes there is : ReadOnlyRecommended:=True

    code example
    Code:
    ActiveWorkbook.SaveAs Filename:="C:Book1.xls", _
            FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
            ReadOnlyRecommended:=True, CreateBackup:=False
    This one is an easy one to find out... just start your macro recorder, save a file and you will get the code needed.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nimrod, that only "suggests" the user to open it as read-only, they still can say, "no", to open it normally !
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  5. #5
    New Member
    Join Date
    May 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Click right on the file name and choose "read only"

  6. #6
    Board Regular
    Join Date
    Jan 2005
    Posts
    150
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    i am also trying to use vba to save a file as read only. is there another method beyond the two listed?

    1. set file attributes
    problem - i'll have to save the file to the new name, close the file, set it to read only, reopen the file, and then close the active workbook.

    2. set readonlyrecommeded attribute
    problem - this will still let someone open the file as read only...

  7. #7
    New Member
    Join Date
    Mar 2002
    Location
    Leicestershire, England
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Save

    Hi

    I've also been trying to save a file as read only. I currently have a macro which range values the data in a master file, and saves the file down in a location that the recipients can access. The problem I had was that sometimes a user was already in the (range valued) file, and my save macro falls over when attempting to save with the same name.

    The only way I've managed to get round this is to save the file with a read/write protect password (via a macro).

    Not ideal, but it works. Hope this helps. See below.

    Application.DisplayAlerts = False

    ActiveWorkbook.SaveAs Filename:="S:\Report\0708\DailySales\SaveTest.xls", _
    FileFormat:=xlNormal, Password:="", WriteResPassword:="test", _
    ReadOnlyRecommended:=False, CreateBackup:=False

  8. #8
    Board Regular Lotus Cars's Avatar
    Join Date
    Feb 2011
    Location
    Norfolk, UK
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: save file as read only

    Hi all,

    Just bumping this up, as I have a similar problem, and wondered if anyone knew of a better way than suggested with the WriteResPassword suggestion, which does work, but I don't really want to password protect the file.

    Any ideas would, as always, be gladly accepted.

    Cheers,

    Lotus Cars

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •