VBA - 当工作簿（）时，下标超出范围。关闭

I would like to loop through all excel workbooks in a folder and write the string "Test" in Cell A1 of every sheet of every workbook. The following code results in 'Subscript out of Range(Error 9)'. When I ran the code line by line it turned out the Error is caused by the line:
Workbooks(FName).Close Savechanges:=True
Dir("C\...") stored in FName returns just the file name so the error can't be because of giving the full path name to Workboooks(...).Close which seems often to be the reason for the error.
On top of that this code really opens the workbook instead of just writing into it. I don't want it to open visually.

我想遍历文件夹中的所有excel工作簿,并在每个工作簿的每个工作表的单元格A1中写入字符串“Test”。以下代码导致'下标超出范围(错误9)'。当我逐行运行代码时,结果显示错误是由以下行引起的:工作簿(FName).Close Savechanges:=存储在FName中的True Dir(“C \ ...”)只返回文件名,因此错误不能因为给Workboooks(...)提供完整的路径名。关闭这通常是错误的原因。最重要的是,这段代码真正打开了工作簿,而不仅仅是写入它。我不希望它在视觉上打开。

Sub multWB()

Dim FName As String
Dim wb As Workbook
Dim sht As Worksheet
Dim directory As String

directory = "C:\Users\...\Desktop\multipleWorkbooks\"
FName = Dir("C:\Users\...\Desktop\multipleWorkbooks\*.xls*")

Do While FName <> ""


            Set wb = Workbooks.Open(directory & FName)
                   For Each sht In wb.Worksheets
                    sht.Cells(1, 1) = "Test"
                   Next
            FName = Dir

            Workbooks(FName).Close Savechanges:=True 'causes error


Loop
     Set wb = Nothing

End Sub

3 个解决方案

#1

You are retrieving the name of the next workbook before closing the current open one. Switch the order those two lines of code:

您在关闭当前打开的工作簿之前检索下一个工作簿的名称。切换这两行代码的顺序:

        Workbooks(FName).Close Savechanges:=True 
        FName = Dir()

#2

You already have a reference to the workbook with wb. Just use that reference!

您已经使用wb引用了工作簿。只需使用该参考!

wb.Close SaveChanges:=True

Anything else is dereferencing objects for no reason.

其他任何东西都无缘无故地取消引用对象。

#3

This: FName = Dir is missing the folder name. Change it to this:

这:FName = Dir缺少文件夹名称。把它改成这个:

FName = directory & Dir()

#1