代码之家 › 专栏 › 技术社区 › Solar Mike

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

vba excel

Solar Mike · 技术社区 · 6 年前

我试图让一个宏在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

为任何帮助干杯。

1 回复 | 直到 6 年前

Pá´Ê 6 年前

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

如果函数应返回结果,则参数的括号是必需的。如果过程/函数不返回结果,则括号为 不允许 !

请参阅以下示例:
```
SomeProcedure(Parameter)         'wrong
SomeProcedure Parameter          'correct

result = SomeFunction(Parameter) 'correct
result = SomeFunction Parameter  'wrong
```
必须在哪个工作簿中指定 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

推荐文章