excel - 在同一代码中使用不同的工作簿

Question

当在代码中引用不同的工作簿时，我一直在使用“Set Workbooks.Open”。

例子:

`Dim wbname1 as Workbook
Dim wbname2 as Workbook


'code which refers to wbname1
Set wbname1 = Workbooks.Open("path\filename")

'code which refers to wbname2
Set wbname2 = Workbooks.Open("path\filename")`

这有时很不方便，因为为了再次引用以前的工作簿，我必须关闭工作簿并重新打开它。

例子:

` 'code which refers to wbname1
Set wbname1 = Workbooks.Open("path\filename")
wbname1.Close True

'code which refers to wbname2
Set wbname2 = Workbooks.Open("path\filename")

'code which refers again to wbname1
Set wbname1 = Workbooks.Open("path\filename")`

我不得不解决这个问题，尤其是在循环中使用不同的工作簿时。

有没有比使用更简单的方法来引用不同的工作簿
Set Workbooks.Open()

Answer 1

Dim book1 as Workbook

你在这里声明一个变量，类型为 Workbook .当您使用 Set关键字，您正在为变量分配一个引用。

Set book1 = Workbooks.Open("path\filename1")`

这里引用作为调用 Excel.Application.Workbooks.Open 的结果返回。 ，Excel 对象模型中的一个函数，如果操作成功，它会打开一个工作簿并返回对它打开的工作簿的引用。

This is sometimes inconvenient because in order to refer again to a previous workbook, I would have to close the workbook and reopen it.

一点也不。 您已经拥有对两个工作簿的引用 ，你需要做的就是，好吧，使用它们!

Set book1 = Workbooks.Open("path\filename1")
Set book2 = Workbooks.Open("path\filename2")

'work with book1
book1.Sheets(1).Range("A1") = "Hello"

'work with book2
book2.Sheets(1).Range("A1") = "World!"

'all done, clean up now:
book1.Close
book2.Close