时间戳

首页 > 解决方案 > 从另一个工作簿调用活动 VBA 脚本

excel - 从另一个工作簿调用活动 VBA 脚本

问题描述

我可以从当前工作簿运行此 VBA 脚本，但我想从另一个新工作簿运行并获得结果。

Sub ListAllSheets()

Dim ws As Worksheet
Dim Counter As Integer

Counter = 0

For Each ws In ActiveWorkbook.Worksheets
    ActiveCell.Offset(Counter, 0).Value = ws.Name
    Counter = Counter + 1
Next ws

End Sub

标签： excelvba

解决方案

这取决于工作簿是否已经打开，并且ActiveCell仅在工作表打开时才有效，Active如果您必须使用ActiveCell

Sub ListAllSheets()
Dim wb As Workbook
Set wb = Workbooks("Transactions")
Dim ws As Worksheet
Dim Counter As Integer

Counter = 0

For Each ws In wb.Worksheets
    ws.Activate
    Application.ActiveCell.Offset(Counter, 0).Value = ws.Name
    Counter = Counter + 1
Next ws

End Sub

这可能不完全符合您的要求，因为您的问题和代码示例都不是很清楚（对我来说）。尝试找到另一种根本不使用ActiveCell的方法以获得更好的结果。也许是这样的：

Sub ListAllSheets()
Dim wb1 As Workbook
Set wb1 = Workbooks("Transactions")
Dim ws1 As Worksheet

Dim wb2 As Workbook
Set wb2 = Workbooks("OtherWorkbook")
Dim ws2 as Worksheet
Set ws2 = wb2.Worksheets("Sheet1")

Dim Counter As Integer

Counter = 1

For Each ws1 In wb1.Worksheets
    ws2.Cells(Counter, 1).Value = ws1.Name
    Counter = Counter + 1
Next

End Sub

一种更可靠的设置 Workbook 对象的方法是在打开时抓取：

Set wb1 = Workbooks.Open("C:\Transactions.xlsx")

推荐阅读