none
VBA Run time error 1004 Cannot access Read Only Document RRS feed

  • Question

  • I have a Excel(2010) workbook with three worksheets of data.  I have VBA code to save one of the worksheets to a .CSV file at the root of the hard drive. Use case: click button to run macro:
    1 - Prompts to save the worksheet.
    2 - Displays the worksheet to be saved.
    3 - Then popup error message: Run time error "1004" Cannot Access Read Only Document.
    4 - program hangs with a copy of the worksheet that is to be saved.  It hangs on the ActiveWorkbook.SavesAs line.
    I was able to force a debug and locate the line that is hanging:
    Response = MsgBox(Msg, Style, title, Help, Ctxt)
        If Response = vbYes Then
        Application.DisplayAlerts = False
        Worksheets("Teams").Copy
    ActiveWorkbook.SaveAs Filename:="C:\Team_Stats_" & Format(Now(), "YYYYMMDDhhmmss") & ".csv", FileFormat:=xlCSV,    CreateBackup:=False
        ActiveWorkbook.Close
        Application.DisplayAlerts = True
    Else
        Exit Sub
    End If

    Wednesday, March 6, 2013 1:37 AM

Answers

All replies

  • Default setup for computers is usually Read only for root directory. You can confirm if this is the problem by substituting the following to see if it will save in the same directory as the macro workbook.

    Ensure that you save the file in normal Macro enabled format first before running the code otherwise it will close after saving as csv without saving the amendments to the VBA code.

     ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & _
            "\Team_Stats_" & Format(Now(), "YYYYMMDDhhmmss") & _
            ".csv", FileFormat:=xlCSV, CreateBackup:=False


    Regards, OssieMac

    Wednesday, March 6, 2013 7:03 AM
  • Hi PCTony,

    Have you got any update?

    Good day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by PCTony Tuesday, March 12, 2013 1:12 AM
    Friday, March 8, 2013 4:37 AM
    Moderator
  • Yoyo Jiang:  thank you so much for the response.  I tested the code and it resolved the issue.  Brgds..
    Tuesday, March 12, 2013 1:12 AM
  • HI PCTony,

     I also have the same issue but only after i upgrade from window 2003 to 2007 office suite.

    I debuged and found this issue here: see underlined

     Rows("2:2").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Delete Shift:=xlUp
        Rows("1:1").Select
        Selection.AutoFilter
        Columns("A:A").Select
        Selection.Delete Shift:=xlToLeft
           
        Cells.Select
        ChDir "C:\"
        ActiveWorkbook.SaveAs Filename:="C:\dailyupload2.txt", FileFormat:=xlText, _
            CreateBackup:=False
        ActiveWorkbook.SaveAs Filename:= _

    Would you be able to help with a fix for this.

    Thanks in advance

    Thursday, March 27, 2014 8:25 PM