忽略受密码保护的Excel文件 [英] Ignore Excel Files That Are Password Protected

问题描述

我正在迭代一个充满了.xlsx文件的目录。我正在使用VBA打开它们并调整几列。其中一些是要求密码，我不知道，所以我只是想跳过这些文件，并转到下一个。如何在VBA中实现这一点？我尝试下面但没有这样的运气。

 设置opensBook = Workbooks.Open（File，IgnoreReadOnlyRecommended：= True）
  

这是一个错误的图像，我得到 - 如何完全忽略工作簿或VBA点击取消提示

解决方案

您可以尝试传递（已知无效）密码，并使用错误处理忽略失败

这样的事情

  Sub Demo（）
 Dim openedBook As Workbook 
 Dim sFile As String 
 
 sFile =C：\Your\File\Name.xlsx
 
设置openedBook =没有，以防openBook被设置设置
 
错误恢复下一步
设置opensBook = Workbooks.Open（_ 
文件名：= sFile，_ 
 IgnoreReadOnlyRecommended：= True，_ 
密码：=！）
 
发生错误GoTo 0 
 
如果没有打开的书是没有，然后
'做你的东西
 Else 
'仅用于演示目的
 MsgBoxFile& sFile& 密码保护
结束如果
 
结束子
  

I am iterating a directory full of .xlsx files. I am using VBA to open them and resize a few columns. Some of them are asking for a password, that I do not know, so I just want to skip these files and move onto the next. How in VBA can this be achieved? I tried the below but no such luck.

Set openedBook = Workbooks.Open(File, IgnoreReadOnlyRecommended:=True)

Here is an image of the error I get -- how can I either ignore the workbook completely or VBA to click "Cancel" on the prompt?

解决方案

You could try passing a (known invalid) password and use error handling to ignore the failure

Something like this

Sub Demo()
    Dim openedBook  As Workbook
    Dim sFile As String

    sFile = "C:\Your\File\Name.xlsx"

    Set openedBook = Nothing ' in case openedBook was previously set to somthing

    On Error Resume Next
    Set openedBook = Workbooks.Open( _
      FileName:=sFile, _
      IgnoreReadOnlyRecommended:=True, _
      Password:="!")

    On Error GoTo 0

    If Not openedBook Is Nothing Then
        ' do your stuff
    Else
        ' For demo purposes only
        MsgBox "File " & sFile & " is password protected"
    End If

End Sub

这篇关于忽略受密码保护的Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！