下标超出范围错误-VBA错误 [英] Subscript out of range error - VBA error

问题描述

对于以下代码，我的下标一直超出范围，我是VBA的新手，所以非常感谢您的帮助.

I keep getting subscript out of range for the following code, I'm new to VBA so would greatly appreciate your help.

我试图引用包含各种源工作簿的表，并将数据从此处复制到sTable范围中也包含的目标"工作簿.

I'm trying to reference a table that contains various source workbooks and copy the data from here to "target" workbooks also contained in the sTable range.

谢谢， 罗南

Sub Import()
    Dim sTable As String                              ' Source table
    Dim sTarget As String                             ' Target range for output
    Dim sHeader As String                             ' Header row from the input data
    Dim sFileName As String                           ' File name to read from
    Dim tFileName As String
    Dim sInputSheet As String                         ' Worksheet to read from
    Dim sRange As String                              ' Range to read from/copy
    Dim tSheet As String
    Dim tRange As String                              ' Range to paste into/Target Range
    Dim sRow As Integer
    Dim cRow As Integer

    Application.Calculation = xlManual
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.AskToUpdateLinks = False

    'Define source(s) and target(t) sheets
    sTable = "rng_SourceData"
    'loop through source table to copy and paste requred data
    sRow = Range(sTable).Rows.Count

    For cRow = 1 To sRow
    'loop through source table to copy and paste requred data
    sRow = Range(sTable).Rows.Count
    For cRow = 1 To sRow


        sFileName = Worksheets("I.Import").Range(sTable).Cells(cRow, 1)
        sInputSheet = Worksheets("I.Import").Range(sTable).Cells(cRow, 2)
        sRange = Worksheets("I.Import").Range(sTable).Cells(cRow, 3)
        tFileName = Worksheets("I.Import").Range(sTable).Cells(cRow, 4)
        tRange = Worksheets("I.Import").Range(sTable).Cells(cRow, 5)
        tSheet = Worksheets("I.Import").Range(sTable).Cells(cRow, 6)

        'Include all ranges in the input table
        Call ImportDataSpreadsheet(sFileName, sInputSheet, sRange, tSheet, tRange)


    Next cRow

End Sub

Sub ImportDataSpreadsheet(sFileName, sInputSheet, sRange, tSheet, tRange)
    Dim SourceWorkbook As Excel.Workbook
    Dim TargetWorkbook As Excel.Workbook
    Dim TargetSheet As Excel.Worksheet

    'Define Source workbook
    Set SourceWorkbook = Workbooks.Open(Filename:=sFileName, Password:=False)
    'Select.Workbook.Sheets.Open (sInputSheet)
    Application.ScreenUpdating = False
    Application.AskToUpdateLinks = False
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False

    'Copy
    SourceWorkbook.Sheets(sInputSheet).Activate
    SourceWorkbook.Sheets(sInputSheet).EnableSelection = xlNoRestrictions

    SourceWorkbook.Sheets(sInputSheet).Range(sRange).Copy

    'Define Target workbook
    Set TargetWorkbook = ThisWorkbook.Worksheets("I.Import").Range(sTable).Cells(cRow, 4)
    Set TargetSheet = TargetWorkbook.Sheets(tSheet)

    'Paste
    TargetWorkbook.Sheets(tSheet).Range(tRange).PasteSpecial Paste:=xlPasteValues

    'Close and finish.
    SourceWorkbook.Close savechanges:=False

End Sub

推荐答案

之所以引起此问题，是因为当您Open一个新工作簿时，您正在更改ActiveWorkbook，并且默认情况下使用，因为您没有使您的Worksheets集合有资格说出它们真正指的是哪个工作簿.

The problem is caused because, when you Open a new workbook, you are changing what is the ActiveWorkbook and your code is by default using ActiveWorkbook because you aren't qualifying your Worksheets collections to say which workbook they really refer to.

解决此问题的最简单方法是在启动代码时仅创建对哪个工作簿处于活动状态的引用:

The easiest way to fix this is to just create a reference to which workbook was active when you started the code:

'Define source(s) and target(t) sheets
sTable = "rng_SourceData"

Dim wbTable As Workbook
Set wbTable = ActiveWorkbook

'Shorten some code by using a With block
With wbTable.Worksheets("I.Import").Range(sTable)
    'loop through source table to copy and paste requred data
    sRow = .Rows.Count
    For cRow = 1 To sRow

        sFileName = .Cells(cRow, 1)
        sInputSheet = .Cells(cRow, 2)
        sRange = .Cells(cRow, 3)
        tFileName = .Cells(cRow, 4)
        tRange = .Cells(cRow, 5)
        tSheet = .Cells(cRow, 6)

        'Include all ranges in the input table
        ImportDataSpreadsheet sFileName, sInputSheet, sRange, tSheet, tRange

    Next cRow
End With

由于该代码现在始终引用wbTable(已在打开任何其他工作簿之前进行设置)，因此该代码将引用正确的工作表.

Because the code is now always referring to wbTable, which has been set prior to any other workbooks being opened, the code will refer to the correct sheet.

注意:从理论上讲，我们实际上并不需要wbTable，我们可以只使用一个

Note: Theoretically, we don't really need wbTable, we could just use a

With ActiveWorkbook.Worksheets("I.Import").Range(sTable)

阻止，但我个人倾向于将其设置为该临时对象.

block, but my personal preference it to set that temporary object instead.

这篇关于下标超出范围错误-VBA错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！