Saving Undeletable Files in VBA

[wraa]

Hi All,

I have created a macro that, in the end, saves the file using the following code:

ActiveWorkbook.SaveAs Filename:=oneback & "\" & deptname & "_QT Report_" _
        & reportdateQ & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

This code usually works for saving the file, but we recently had a requirement to make the resulting files undeletable by non-administrators. However, if the administrator denies permissions for the network folder that the files reside in, the Save As code above fails. It looks like the data becomes trapped in a temp file that cannot be deleted under low permission settings. The excel file does save, but it has a size of 0 bytes.

Is there a way to either:

• Configure the Save As code so that it succeeds with minimal permissions?
• Automatically (with VBA) set permissions on each resulting file after it is saved so that the file cannot be deleted?
• Set the security permissions such that the macro can successfully save the file and the resulting file cannot be deleted by non-administrators.

Non-administrators will be running the macro to create/save the files, but should not have permission to delete the resulting file.

Thanks!

Will

 

[ZVI]

Hi Will,
You can try to save workbook on local drive, then copy it to the shared folder by the aid of FileCopy statement or by using of Shell function.
Alternatively with all permissions you can set ReadOnly file attribute using SetAttr statement, but it's not as strong as network permissions.
Regards

 

[wraa]

Thanks a bunch for your help! However, I am still running into problems.

In my code, I am now initially saving the file one folder up in the network, where permissions aren't as strict. This part seems to work. When I attempt to move that file into the secured folder, the code fails (it also fails when testing the code with two unsecured folders). The error that I get is Path/File Access error.

Do you know what is wrong with my code? I also tried similar code with FileCopy, and this did not work either.

 oneback = Left(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, "\") - 1)
    twoback = Left(Left(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, "\") - 1), _
    InStrRev(Left(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, "\") - 1), "\") - 1)
    ChDir ThisWorkbook.Path

    Application.DisplayAlerts = False

    ActiveWorkbook.SaveAs Filename:=twoback & "\" & examplefilename & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        
    Application.DisplayAlerts = True

    Name twoback & "\" & examplefilename & ".xlsx" As oneback & "\" & examplefilename