保存VBA之前检查文件夹权限 [英] Check Folder Permissions Before Save VBA

问题描述

我已经创建了一个用户表单，可以打开excel文件并打开&隐藏excel.关闭用户表单时，将保存&关闭excel文件.但是，excel文件有两种类型的用户.

I Have created a user form that will open an excel file open & hide the excel. When closing the user form will save & close the excel file. However, there are two types of users of the excel file.

1. 编辑器-将数据输入文件的人
2. 查看器-正在查看文件的人.

具有excel文件的文件夹仅允许编辑器"保存.(其他人没有写权限).因此，如果用户对该文件夹没有权限，就必须避免保存部分.有任何想法吗?我的用户表单关闭事件代码在这里.

The folder which has the excel file only allow "Editors" to save. (Others have no permission to write). Therefore, I have to avoid save part if the user has no wright permission to the folder. Any ideas? My code for the close event of user form is here.

Private Sub UserForm_QueryClose (Cancel As Integer, CloseMode As Integer)

If CloseMode = vbFormControlMenu Then

If ws.AutoFilterMode Then ws.AutoFilterMode = False
ws.Columns("F:H").Copy
ws.Activate
ws.Range("F1").Select
Application.DisplayAlerts = False
Selection.PasteSpecial Paste:=xlPasteValues
Application.DisplayAlerts = True
Application.CutCopyMode = False
Application.Visible = True
ActiveWorkbook.CheckCompatibility = False
ThisWorkbook.Close savechanges:=True
ActiveWorkbook.CheckCompatibility = True
End If
End Sub

W表示工作表的声明名称.

Ws Denoted the declared name for the worksheet.

编辑

我尝试过&找到了另一种方法来克服这种情况.但是，这不是解决方案.是获取结果的肮脏方法.请参见下面的代码.

I have tried & found an alternative method to overcome the situation. However, this is not the solution & is a dirty method to get the result. Please see below code.

Private Sub UserForm_QueryClose (Cancel As Integer, CloseMode As Integer)
On Error Resume Next
If CloseMode = vbFormControlMenu Then
If ws.AutoFilterMode Then ws.AutoFilterMode = False
ws.Columns("F:H").Copy
ws.Activate
ws.Range("F1").Select
Application.DisplayAlerts = False
Selection.PasteSpecial Paste:=xlPasteValues
Application.DisplayAlerts = True
Application.CutCopyMode = False
Application.Visible = True
ActiveWorkbook.CheckCompatibility = False
ThisWorkbook.Save
ThisWorkbook.Close savechanges:=False
ActiveWorkbook.CheckCompatibility = True
End If

End Sub

在上面的代码中，我已经跟踪了在查看器&的保存过程中生成的错误.通过使用跳到下一行<出现错误后继续恢复代码.

On above code I have tracked error generated during the save process of viewers & jump to next line by using on error resume next.

推荐答案

这将检查工作簿文件夹的访问列表，以查看用户名是否出现在列表中.如果是这样，则保存文件.

This checks the access list of the workbook's folder to see if the user's name appears in the list. If it does, then save the file.

If Instr(1, Environ("USERNAME"), CreateObject("WScript.Shell").Exec("CMD /C ICACLS """ & _
ThisWorkbook.Path & """").StdOut.ReadAll) > 0 Then ThisWorkbook.Save

通过打开命令提示符，通过它运行ICACLS命令并读取该命令的输出来执行此操作.然后，它使用InStr()方法查看用户名是否出现在该输出中.

It does this by opening a command prompt, running the ICACLS command through it and reading the output from that command. Then it uses the InStr() method to see if the username appears in that output.

这篇关于保存VBA之前检查文件夹权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！