Run-time Error '1004': Cannot access read-only document 'filename.xls'

[crookesa]

Hi,

I have some VBA code where I create a new workbook for the active sheet and save that individual sheet to a file a close it.
It works as expected the first time I run it but if the file exists and I run the macro again I get a run time error.

Have done a bit of looking online and found things to try and no one them have solved the issue.

Any help would be appreciated.

With 'Application.DisplayAlerts = False' the error I get is "Run-time error '1004': Method 'SaveAs' of object '_Workbook' failed"
With 'Application.DisplayAlerts = True' the error I get is "Run-time error '1004': Cannot access read-only document 'filename.xls'"

The code is below

Sub Save_All_As_Sheets()


  Application.ScreenUpdating = False
  Application.DisplayAlerts = False


  Dim WshtNames As Variant
  Dim WshtNameCrnt As Variant
  Dim MySheet As Worksheet


  WshtNames = Array("1", "2")


  For Each WshtNameCrnt In WshtNames
    With Worksheets(WshtNameCrnt)
      Set MySheet = Worksheets(WshtNameCrnt)
      MySheet.Activate
    
      F_Name = Format(Range("G1").Value, "dd mmm")
      MName = F_Name & ".xls"


    ActiveSheet.Copy
      With ActiveSheet.UsedRange
          .Copy
          .PasteSpecial xlValues
          .PasteSpecial xlFormats
      End With
    Application.CutCopyMode = False
    
    ActiveWorkbook.SaveAs FileName:=MName, FileFormat:=xlWorkbookNormal
    
    ......Some more code to create sheet
    
    Windows(MName).Close savechanges:=True
    Windows("Roster Template - February 2018.xls").Activate
    Range("A1").Select
        End With
    
  Next WshtNameCrnt
  
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
End Sub

 

[crookesa]

Re: Run-time Error '1004': Cannot access read-only docoument 'filename.xls'

Any got any help on this?

 

[crookesa]

Re: Run-time Error '1004': Cannot access read-only docoument 'filename.xls'

Update to this. Still not resolved the issue but it only seems to be happening on OSX systems. I tried to replicate it on a Windows system and no error.

Still looking for a solution though?

 

[Fluff]

Re: Run-time Error '1004': Cannot access read-only docoument 'filename.xls'

If you're on a Mac have a look here https://www.rondebruin.nl/mac/mac020.htm

 

[crookesa]

Thanks @Fluff, will take a read. I’m coding it on a Mac at home but the end user will be used on Windows. Might be best to code it on a VM.

 

[Fluff]

Not having a Mac & never having used a VM, I can't be sure, but there is still a good chance that won't work.
Both paths & fileformats are different between Mac & PC