打开已关闭的工作簿并运行宏 [英] Open Closed Workbook and Run Macro

问题描述

我正在尝试打开不同的Excel工作簿，运行各自的宏，保存并关闭。到目前为止，我有以下代码：

  Sub AllFiles（）
 
 Application.Run'L ：\RESEARCH\Alternative Assets\Private Equity\PE Preqin Data\Preqin_Fundraising_Data_Macro.xlsm'！Get_File
 
 End Sub 
  

此代码的问题是尽管打开了工作簿，但我仍然会遇到运行时错误9的下标超出范围。我的猜测是，宏无法在活动工作簿中找到某些工作表或变量。这里是我得到错误的代码（宏在另一个工作簿中）

  Public Sub Get_File（）
 
 Dim sFiletype As String'基金类型参考
 Dim sFilename As String'文件名（基金类型+下载日期），如果然后默认
 Dim sFolder As String'文件夹名称（基金类型）如果然后默认
 Dim bReplace As Boolean'要替换现有文件
 Dim sURL As String'提取信息的位置的URL 
 Dim pURL As String 
 Dim Cell，Rng As Range 
 Dim Sheet As Worksheet 
 
 Dim oBrowser As InternetExplorer 
 Set oBrowser = New InternetExplorer 
 
'初始化变量
设置Rng =范围（I2：I15）
 Set Sheet = ActiveWorkbook.Sheets（Macro_Button）'POINT OF ERROR 
 
 Rng 
中的每个单元格如果单元格< ;> 然后
 sFiletype = Cell.Value 
 sFilename = sFiletype& _&格式（日期，mmddyyyy）
 sFolder = Application.WorksheetFunction.VLookup（Cell.Value，Sheet.Range（I2：Z15），2，False）
 bReplace = True 
 sURL =www.preqin.com
 pURL = Application.WorksheetFunction.VLookup（Cell.Value，Sheet.Range（I2：Z15），16，False）
 
'下载使用所需的方法，XMLHTTP / IE 
如果Application.WorksheetFunction.VLookup（Cell.Value，Sheet.Range（I2：Z15），15，False）= 1然后
调用Download_Use_IE（oBrowser， sURL，pURL，sFilename，sFolder，bReplace）
 Else 
调用Download_NoLogin_Use_IE（oBrowser，pURL，sFilename，sFolder，bReplace）
 End If 
 
其他：GoTo Exit_Sub 
结束如果
下一个
 
 Exit_Sub：
 
'关闭IE 
 oBrowser.Quit 
 
 End Sub 
  

任何人对此错误有任何解决方案/建议？谢谢！

解决方案

使用ThisWorkbook代替ActiveWorkbook。那么你应该可以运行远程宏。

  Set Sheet = ThisWorkbook.Sheets（Macro_Button）
  

注意：我总是建议使用 ThisWorkbook 而不是 ActiveWorkbook ，因为它更明确。你永远不能确定哪个是活动的工作簿。但是 ThisWorkbook 总是指持有宏的工作簿。

另一个注释（基于你的下面的评论）：上面的代码不是设计为与多个工作簿一起使用。这不够明确例如：

 设置Rng =范围（I2：I15）
  pre> 
 
 
在这一行中，你不是说你指的是哪个工作表，甚至是工作簿。所以，VBA为你做出决定。我猜VBA决定你不想要的东西。所以，你必须告诉VBA你想要什么，并明确说明。以下是有关如何更改上述行的一些可能示例。
 设置Rng = ThisWorkbook.Sheets（1）.Range（ I2：I15）'这将是远程Excel文件中的表单
设置Rng = ActiveWorkbook.Sheets（1）.Range（I2：I15）'这将是您从中最初调用外部宏
设置Rng =工作簿（someFile.xls）。表（someSheetInThatFile）。范围（I2：I15）'这将是第三个Excel文件：不是一个调用宏或Excel文件托管宏
  
然而，这只是上面代码中的一行必须更改，使其在多个Excel文件中工作。
 
I am trying to open different Excel workbooks, run their respective macros, save, and close. So far I have this code below:
Sub AllFiles()

    Application.Run "'L:\RESEARCH\Alternative Assets\Private Equity\PE Preqin Data\Preqin_Fundraising_Data_Macro.xlsm'!Get_File"

End Sub
The problem with this code is that although it opens the workbook, I keep getting a run-time error '9' subscript out of range. My guess is that the macro is unable to find the certain sheets or variables within the "active" workbook. Here's the code where I get the error (macro in another workbook)
Public Sub Get_File()

    Dim sFiletype As String     'Fund type reference
    Dim sFilename As String     'File name (fund type + date of download), if "" then default
    Dim sFolder As String       'Folder name (fund type), if "" then default
    Dim bReplace As Boolean     'To replace the existing file or not
    Dim sURL As String          'The URL to the location to extract information
    Dim pURL As String
    Dim Cell, Rng As Range
    Dim Sheet As Worksheet

    Dim oBrowser As InternetExplorer
    Set oBrowser = New InternetExplorer

    'Initialize variables
    Set Rng = Range("I2:I15")
    Set Sheet = ActiveWorkbook.Sheets("Macro_Button") 'POINT OF ERROR

    For Each Cell In Rng
        If Cell <> "" Then
        sFiletype = Cell.Value
        sFilename = sFiletype & "_" & Format(Date, "mmddyyyy")
        sFolder = Application.WorksheetFunction.VLookup(Cell.Value, Sheet.Range("I2:Z15"), 2, False)
        bReplace = True
        sURL = "www.preqin.com"
        pURL = Application.WorksheetFunction.VLookup(Cell.Value, Sheet.Range("I2:Z15"), 16, False)

        'Download using the desired approach, XMLHTTP / IE
            If Application.WorksheetFunction.VLookup(Cell.Value, Sheet.Range("I2:Z15"), 15, False) = 1 Then
            Call Download_Use_IE(oBrowser, sURL, pURL, sFilename, sFolder, bReplace)
            Else
            Call Download_NoLogin_Use_IE(oBrowser, pURL, sFilename, sFolder, bReplace)
            End If

        Else: GoTo Exit_Sub
        End If
    Next

Exit_Sub:

    'Close IE
    oBrowser.Quit

End Sub
Anyone have any solution/suggestions to this error? Thanks!
解决方案
Use ThisWorkbook instead of ActiveWorkbook. Then you should be fine to run a remote macro.
Set Sheet = ThisWorkbook.Sheets("Macro_Button")
Note: I would always recommend to use ThisWorkbook instead of ActiveWorkbook as it is more explicit. You can never be sure which is the active workbook. But ThisWorkbook always refers to the workbook holding the macro.

Another note (based on your below comments): The above code is not designed to be used with several Workbooks. It is NOT sufficiently explicit. Example:
Set Rng = Range("I2:I15")
In this line you are NOT saying which Sheet or even Workbook you are referring to. So, VBA makes the decision for you. I guess VBA "decides" for something you don't want. So, you'll have to tell VBA what you want and explicitly state that. Here are some possible examples on how to change the above line.
Set Rng = ThisWorkbook.Sheets(1).Range("I2:I15") 'This will be a sheet in the remote Excel file
Set Rng = ActiveWorkbook.Sheets(1).Range("I2:I15") 'This will be a sheet in the file from which you were originally calling the external macro
Set Rng = Workbooks("someFile.xls").Sheets("someSheetInThatFile").Range("I2:I15") 'This would be a third Excel file: not the one calling the macro nor the Excel file hosting the macro
Yet, this is just one line in the above code you'll have to change to make it work across several Excel files.

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