vba 在excel vba中将范围从一个工作簿复制到另一个
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10057806/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
copy range from one workbook to another in excel vba
提问by jus4ujith
I have workbook in which i have to copy a range in one workbook to a range variable in the active workbook. i am getting an error when i execute the following..Is there any other ways of doing this.
我有工作簿，我必须将一个工作簿中的范围复制到活动工作簿中的范围变量。执行以下操作时出现错误..有没有其他方法可以做到这一点。
Dim NTwbk As Workbook
Dim AppExcel As Excel.Application
Dim NewRng As Range
Dim res As Range
Sub OpenWBK()
Set AppExcel = New Excel.Application
Set NTwbk = AppExcel.Workbooks.Open(Sheet1.Range("SecondWorkbookPath"))
NewRng= NTwbk.Sheets("Sheet1").Range("B3")
AppExcel.Quit
End Sub
I tried this also :
我也试过这个：
NewRng.Value = NTwbk.Sheets("Sheet1").Range("B3").Value
Also didnt work
也没有用
回答by Daniel
The main problem with your script is that once you close the 2nd workbook (via AppExcel.Quit or NTwbk.close as the other answer lists) the range object you referenced is no longer in memory and your variable is no longer usable. Also when assigning an object to a variable you must use set:
您的脚本的主要问题是，一旦您关闭第二个工作簿（通过 AppExcel.Quit 或 NTwbk.close 作为其他答案列表），您引用的范围对象不再在内存中，并且您的变量不再可用。同样，在将对象分配给变量时，您必须使用 set：
Set NewRng = Ntwbk.Sheets("Sheet1").Range("B3")
This would not fail, but will also not be useful because you then close Ntwbk.
这不会失败，但也不会有用，因为您随后关闭了 Ntwbk。
Any action that needs to be performed with a range in the a workbook must be performed before that workbook is closed.
需要在工作簿中的某个范围内执行的任何操作都必须在关闭该工作簿之前执行。
If all you want is the value of the range, this is a possible change:
如果你想要的只是范围的值，这是一个可能的变化：
Dim NTwbk As Workbook
Dim AppExcel As Excel.Application
Dim NewRng As Range
Dim res As String 'Note the change from Range to String
Sub OpenWBK()
Set AppExcel = New Excel.Application
Set NTwbk = AppExcel.Workbooks.Open(Sheet1.Range("SecondWorkbookPath"))
NewRng = NTwbk.Sheets("Sheet1").Range("B3").Value
AppExcel.Quit
End Sub
You can then freely use NewRng as the value of the particular range in the other workbook.
然后，您可以自由地使用 NewRng 作为其他工作簿中特定范围的值。
回答by Tony Dallimore
You already have Excel open so you do not need to open a second copy so discard:
你已经打开了 Excel，所以你不需要打开第二个副本，所以丢弃：
Dim AppExcel As Excel.Application
Set AppExcel = New Excel.Application
AppExcel.Quit
Change the open to:
将打开更改为：
Set NTwbk = Workbooks.Open(Sheet1.Range("SecondWorkbookPath"))
However "SecondWorkbookPath" is not a valid range. If cell A1 of worksheet "Sheet1" contains a valid path and file name, the following would be OK:
但是“SecondWorkbookPath”不是有效范围。如果工作表“Sheet1”的单元格 A1 包含有效的路径和文件名，则可以执行以下操作：
Set NTwbk = Workbooks.Open(Worksheets("Sheet1").Range("A1").Value)
Set NewRng = NTwbk.Sheets("Sheet1").Range("B3")
NTwbk.Close SaveChanges := False
Make the above changes and try again.
进行上述更改并重试。
