vba 如何激活特定工作簿和特定工作表？

How do i activate a specific workbook and a specific sheet?

提问by niko

How do I activate my Other workbook from the Current workbook? I have a current workbook with dumb.xls and The other workbook name as Tire.xls.I have opened the Tire.xls from the dumb.xls using worksbooks.open filename:= "name of the file".Its getting open but The problem is Im unable to make it work.

如何从当前工作簿激活我的其他工作簿？我有一个带有dumb.xls 的当前工作簿，另一个工作簿名称为Tire.xls。我已经使用.It 打开了dumb.xls 中的Tire.xls worksbooks.open filename:= "name of the file"，但问题是我无法使其工作。

If I say cells(2,24).value=24puts these value in the cell of dumb.xls but I want it to be done one Tire.xls.

如果我说cells(2,24).value=24将这些值放在dumb.xls 的单元格中，但我希望它在Tire.xls 中完成。

activesheet.cells(2,24).value=24puts these on Tire.xls. But how do i activate the Workbook with the name ? I need to open 3 to 4 excel workbooks And perform the operation? How do I activate the specific workbook

activesheet.cells(2,24).value=24将这些放在 Tire.xls 上。但是我如何激活带有名称的工作簿？我需要打开 3 到 4 个 excel 工作簿并执行操作？如何激活特定的工作簿

I have found this code on google

我在谷歌上找到了这个代码

     activeworkbook.worksheet("sheetname").activate  ' but not working
     windows("sheetname").activate ' people on google suggested not to use

Its not getting activated. I dont know how to make it work. Can anyone tell me How do i activate a specific workbook and a specific sheet of the other workbook ?

它没有被激活。我不知道如何使它工作。谁能告诉我如何激活特定工作簿和其他工作簿的特定工作表？

Example: I have niko.xls and niko_2.xls opened as workbooks from the dumb.xls workbook so totally 3 workbooks and I have to activate the 2nd sheet of niko_2.xls workbook.How do I make it? Can anyone explain me the syntax with these example? Thank you in advance

示例：我将 niko.xls 和 niko_2.xls 作为工作簿从 dubt.xls 工作簿中打开，所以总共有 3 个工作簿，我必须激活 niko_2.xls 工作簿的第二张纸。我该如何制作？谁能用这些例子解释我的语法？先感谢您

回答by aevanko

You do not need to activate the sheet (you'll take a huge performance hit for doing so, actually). Since you are declaring an object for the sheet, when you call the method starting with "wb." you are selecting that object. For example, you can jump in between workbooks without activating anything like here:

您不需要激活该工作表（实际上，这样做会对性能造成巨大影响）。由于您正在为工作表声明一个对象，因此当您调用以“wb”开头的方法时。您正在选择该对象。例如，您可以在不激活任何内容的情况下在工作簿之间跳转，如下所示：

Sub Test()

Dim wb1 As Excel.Workbook
Set wb1 = Workbooks.Open("C:\Documents and Settings\xxxx\Desktop\test1.xls")
Dim wb2 As Excel.Workbook
Set wb2 = Workbooks.Open("C:\Documents and Settings\xxxx\Desktop\test2.xls")

wb1.Sheets("Sheet1").Cells(1, 1).Value = 24
wb2.Sheets("Sheet1").Cells(1, 1).Value = 24
wb1.Sheets("Sheet1").Cells(2, 1).Value = 54

End Sub

回答by Jean-Fran?ois Corbett

You have to set a reference to the workbook you're opening. Then you can do anything you want with that workbook by using its reference.

您必须设置对正在打开的工作簿的引用。然后，您可以通过使用该工作簿的参考对该工作簿执行任何您想要的操作。

Dim wkb As Workbook
Set wkb = Workbooks.Open("Tire.xls") ' open workbook and set reference!

wkb.Sheets("Sheet1").Activate
wkb.Sheets("Sheet1").Cells(2, 1).Value = 123

Could even set a reference to the sheet, which will make life easier later:

甚至可以设置对工作表的引用，这将使以后的生活更轻松：

Dim wkb As Workbook
Dim sht As Worksheet

Set wkb = Workbooks.Open("Tire.xls")
Set sht = wkb.Sheets("Sheet2")

sht.Activate
sht.Cells(2, 1) = 123

Others have pointed out that .Activatemay be superfluous in your case. You don't strictly need to activate a sheet before editing its cells. But, if that's what you want to do, it does no harm to activate -- except for a small hit to performance which should not be noticeable as long as you do it only once or a few times. However, if you activate many times e.g. in a loop, it will slow things down significantly, so activate should be avoided.

其他人指出，.Activate在您的情况下可能是多余的。在编辑其单元格之前，您并不严格需要激活工作表。但是，如果这就是您想要做的，那么激活它并没有什么坏处——除了对性能的小影响，只要您只执行一次或几次就不会引起注意。但是，如果您多次激活，例如在循环中，它会显着减慢速度，因此应避免激活。

回答by Jonia

The code that worked for me is:

对我有用的代码是：

ThisWorkbook.Sheets("sheetName").Activate

回答by MNS

try this

尝试这个

Windows("name.xls").Activate

回答by Kai

You can try this.

你可以试试这个。

Workbooks("Tire.xls").Activate

ThisWorkbook.Sheets("Sheet1").Select
Cells(2,24).value=24

回答by Tim Williams

Dim Wb As Excel.Workbook
Set Wb = Workbooks.Open(file_path)
Wb.Sheets("Sheet1").Cells(2,24).Value = 24
Wb.Close

To know the sheets name to refer in Wb.Sheets("sheetname")you can use the following :

要知道要引用的工作表名称，Wb.Sheets("sheetname")您可以使用以下命令：

Dim sht as Worksheet    
For Each sht In tempWB.Sheets
    Debug.Print sht.Name
Next sht