VBA下标超出范围，代码9

I'm conducting my very first VBA macro and I'm having some difficulties with this seemingly easy code to read data from a closed workbook into my currently opened one.

我正在执行我的第一个VBA宏，我对这个看似简单的代码有一些困难，它可以从一个封闭的工作簿读取数据到我当前打开的工作簿。

Sub KAuto()

   Dim path As String
   path = "C:\files\Utfall.xlsx"
   Dim currentWb As Workbook
   Set currentWb = ThisWorkbook
   Dim openWb As Workbook
   Set openWb = Workbooks.Open(path)
   Dim openWs As Worksheet
   Set openWs = openWb.Sheets("March")
   currentWb.Sheets("Indata").Range("A1").Value = openWs.Range("A3").Value
End Sub

The problem I'm having is that I get a code 9, subscript out of range. But I've checked that A1 and A3 is existent for the current workbook and the imported one respectively.

我的问题是我得到一个代码9，下标超出范围。但是我已经检查了A1和A3对于当前工作簿和导入的工作簿是存在的。

What I have tried to do is to omit the ".Value" in all combinations, as that was what the original author did.

我试图做的是省略“。价值“在所有的组合中，就像原作者所做的那样。

Googling this problem I've encountered that people misused functions which I do not use, for instance windows(), or omitted "" for referencing the worksheets, or simply misspelled things. I don't Think I have any of these, and so I need further help.

在google上搜索这个问题时，我发现人们误用了我没有使用的函数，例如windows()，或者在引用工作表时省略了“”，或者仅仅是拼写错误。我想我没有这些，所以我需要进一步的帮助。

How can I correct my subscript out of range? Is there a better way to achieve this copying of cells? In the future I want to import 10 files, will this then be obsolete? (I recall someone posting something in the lines of openWb = [file1,file2,file3] and looping through them, but I cannot find it; does anyone have a link?

我怎样才能纠正超出范围的下标?有没有更好的方法来实现这种细胞复制?将来我想导入10个文件，这会过时吗?(我记得有人在openWb = [file1,file2,file3]行中发布了一些内容，并在其中循环，但我找不到;有人有链接吗?

EDIT: I've copied the path to the file from its properties, so it ought to be correct.

编辑:我已经从文件的属性中复制了路径，所以应该是正确的。

EDIT2:

EDIT2:

  currentWb.Sheets("Indata").Range("A1").Value = openWs.Range("A3").Value 

snippet gives the error

片段给出了错误

EDIT3: VB editor print screen:

VB编辑器打印屏幕:

1 个解决方案

#1

Try using ActiveWorkbook instead of ThisWorkbook.

尝试使用ActiveWorkbook而不是这本书。

Set currentWb = ActiveWorkbook

ThisWorkbook refers to the workbook in which the code resides. ActiveWorkbook refers to the workbook that is currently active i.e. "on top" in the Excel application. It looks like your case, the code resides in a different workbook; so what you want is ActiveWorkbook.

这个工作簿引用代码所在的工作簿。ActiveWorkbook是指当前活动的工作簿。在Excel应用程序中“最上面”。看起来像你的情况，代码驻留在不同的工作簿中;所以你想要的是ActiveWorkbook。

And you can ommit the .value from last line.

你可以从最后一行来ommit。

currentWb.Sheets("Indata").Range("A1") = openWs.Range("A3")

currentWb.Sheets(Indata).Range(" A1 ")= openWs.Range(A3)

Your code worked fine for me, that`s why I cannot be sure if it will help.
There can be an issue, when opening the openWs. The error line can be evaluated before the openWs is actually open. Then maybe add a line :

您的代码对我来说工作得很好，这就是为什么我不能确定它是否有用。当打开openWs时，可能会有一个问题。在openWs真正打开之前，可以对错误行进行评估。然后再加一行:

Application.Wait (Now + TimeValue("00:00:03")) 'this is 3 seconds from now

after the Set openWb = Workbooks.Open(path).

在设置openWb = Workbooks.Open(path)之后。