将宏移动到工作簿打开 [英] Moving a Macro to Workbook Open

问题描述

我为客户编写了一个宏。他希望它在一个可以在打开时自动运行它的工作簿中。

I have written a macro for a client. He wants it in a workbook that will run it automatically upon opening.

在开始时，宏打开第二个工作簿进行处理，并开始在第二个工作簿的工作表中循环。

At the beginning the macro open up a second workbook for processing and starts cycling through the worksheets of the second workbook.

以下是我遇到问题的代码部分：        

Here's the section of the code where I have the problem:        

    Workbooks.Open文件名：= wb_name_path



    Dim ws As Worksheet

    Dim iAns As Integer，sIndex As Integer

    Dim ws_name As String



       每个工作表中的ws



        ws.Select

当我到达这一点时，我得到一个"方法选择对象'_Worksheet'失败"错误。

When I get to this point I get a "Method Select of Object '_Worksheet' failed" error.

当我将宏作为附加到工作表的代码运行时，这很正常。

This worked fine when I had the macro running as code attached to a worksheet.

为什么它现在失败了？

推荐答案

您的代码可能正在尝试引用包含代码的工作簿中的工作表。以下代码在测试下工作。如果正在打开的工作簿是一个大型工作簿，那么请查看我对DoEvents的评论。 （无论如何都可以离开DoEvents。）

Your code probably is trying to reference the worksheets in the workbook containing the code. Following code worked under test. If the workbook being opened is a large workbook then see my comment against DoEvents. (can leave the DoEvents anyway.)

Private Sub Workbook_Open（）

 

    Dim wbToOpen As Workbook

    Dim wb_name_path As String

    Dim ws As Worksheet

   

    wb_name_path = ThisWorkbook.Path& " \" &安培; "WorkbookToBeOpened.xlsx"&
   

   设置wbToOpen = Workbooks.Open（文件名：= wb_name_path）

   的DoEvents    '如果要打开大型工作簿，那么这可能是必要的。
   

    '此示例中不需要以下行，因为已打开的工作簿变为活动工作簿

    '但是，如果其他代码更改了活动工作簿，则需要。

    wbToOpen.Activate

   

   对于每个ws在wbToOpen.Works表格中，
        ws.Select

        MsgBox ws.Name& "已选择"

   下一张ws

   

End Sub

Private Sub Workbook_Open()
 
    Dim wbToOpen As Workbook
    Dim wb_name_path As String
    Dim ws As Worksheet
   
    wb_name_path = ThisWorkbook.Path & "\" & "WorkbookToBeOpened.xlsx"
   
    Set wbToOpen = Workbooks.Open(Filename:=wb_name_path)
    DoEvents    'If large workbook to open then this might be necessary
   
    'Following line not be necessary in this example because opened workbook becomes Active Workbook
    'However, if other code changes active workbook then is necessary.
    wbToOpen.Activate
   
    For Each ws In wbToOpen.Worksheets
        ws.Select
        MsgBox ws.Name & " selected"
    Next ws
   
End Sub

这篇关于将宏移动到工作簿打开的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！