Problem reading Excel mail attachments in Access

[PeDa]

I and others share a multi-user Access application that saves Excel attachments from mail in a Public Folder (to which we all have access, I am the manager) to a scratch file (our personal W:-drive) and then reads the data in the saved spreadsheet into an Access table. This works fine if I do it, but all my colleagues get an Access Denied failure message at the Save AsFile statement (apparently because the previous Answer.xls has been saved as Read Only, and cannot be overwritten).

For Each Item In olMyPublicFolder.Items
    For Each olAtmt In Item.Attachments
      If Right(olAtmt.FileName, 4) = ".xls" Then
        MyFilename = "W:\Answer.xls"
        olAtmt.SaveAsFile MyFilename
        ReadOneSpreadsheet (MyFilename)
      End If
    Next olAtmt
Next Item

Any suggestions appreciated

 

[jrbarnett]

If you're talking just the old style Read Only file attribute (Rather than NTFS or other network file system permissions), look at the FileAttr command.
This will let you change it.

If it is file system permissions then I suggest changing the rights on that folder so that anybody who uses that application has permission to write to it at both the folder and share level (Change permissions as a minimum).

John

 

[PeDa]

Hello John,

Thank you for this. Interposing a

SetAttr "W:\Antwoord.xls", vbNormal

seems to have solved this problem (luckily, as changing users' permissions would have involved a complicated fight with the IT department).

Best wishes,

Peter