时间戳

首页 > 解决方案 > 很简单，但这不会在受保护的 Excel 工作表上输入密码

excel - 很简单，但这不会在受保护的 Excel 工作表上输入密码

问题描述

我试图让一个宏运行 150 个 Excel 工作簿，每个工作簿中有 5 个工作表，并在一个特定工作表上输入密码。

我已经删除了宏所做的其他内容，但是如果我删除了密码部分，宏会按应有的方式遍历所有文件。这意味着我必须手动输入密码。

只是不接受密码，使工作表受到保护。

这是代码：

Option Explicit

Sub Examnew()    
    Dim rCell As Range, rRng As Range             'define loop names    
    Dim wbmaster As Workbook                      'name for master workbook    
    Dim wbtarget As Workbook                      'name for student workbook    
    Set wbmaster = ActiveWorkbook                 'set the name for the master

    'Student numbers in cells B3:B64 WARNING SET TO 2 STUDENTS ONLY FOR TEST
    'NOTE that st Nums are in col B with a duplicate in col A to collect results.

    Set rRng = wbmaster.Sheets("studentlist").Range("B3:B4”)

    For Each rCell In rRng '<                | loop through "students" range
        '<                                     | now open Student exam workbook and set to name "wbtarget"
        Workbooks.Open ("/Users/tester/Final_V1/" & rCell.Value & ".xlsx")

        Set wbtarget = Workbooks(rCell.Value & ".xlsx")
        Sheets("ANSWERS").Unprotect "Coursework2019"

        'Other stuff normally here…   
        wbtarget.Close (True) '<            | now save and close the student file...

    Next rCell   '<                          | next student number
End Sub

为任何帮助而欢呼。

标签： excelvba

解决方案

  1. 您的代码中有一些错误的括号。

    如果函数应该返回结果，则参数的括号是必要的。如果过程/函数不返回结果，则不允许使用括号！

    请参阅此示例：

    SomeProcedure(Parameter)         'wrong
SomeProcedure Parameter          'correct

result = SomeFunction(Parameter) 'correct
result = SomeFunction Parameter  'wrong

  2. 您必须指定您在哪个工作簿Sheets("ANSWERS")中：wbtarget.Sheets("ANSWERS")

所以它应该是这样的：

Option Explicit

Sub Examnew()    
    Dim rCell As Range, rRng As Range             'define loop names    
    Dim wbmaster As Workbook                      'name for master workbook    
    Dim wbtarget As Workbook                      'name for student workbook    
    Set wbmaster = ActiveWorkbook                 'set the name for the master

    'Student numbers in cells B3:B64 WARNING SET TO 2 STUDENTS ONLY FOR TEST
    'NOTE that st Nums are in col B with a duplicate in col A to collect results.

    Set rRng = wbmaster.Sheets("studentlist").Range("B3:B4")

    For Each rCell In rRng 
        Set wbtarget = Workbooks.Open("/Users/tester/Final_V1/" & rCell.Value & ".xlsx") 
        '^ set the open workbook directly to the variable

        wbtarget.Sheets("ANSWERS").Unprotect Password:="Coursework2019"
        '^ you must specify the workbook here!!!

        'Other stuff normally here…   
        wbtarget.Close SaveChanges:=True 'submit parameters without parenthesis!
    Next rCell
End Sub

推荐阅读