VBA将Excel范围复制到其他工作簿 [英] VBA Copying Excel Range to Different Workbook
问题描述
我试图找到一种方法将一个工作簿中的范围(在本例中为A6:J21)复制到另一工作簿中。我认为应该是以下内容...
currentWorksheet = xlWorkBook.Sheets.Item( Command Group)
excelRange = currentWorksheet.Range( A6:J21)
excelDestination = newXlSheet.Range( A6:J21)
excelRange.Copy(excelDestination)
但是 excelRange.Copy(excelDestination)
却给我一个错误。
下面的代码按预期运行,所以我不确定我在哪里出错。
Dim xRng作为Excel.Range = CType(currentWorksheet.Cells(7,7),Excel.Range)
Console.WriteLine(xRng.ToString)
Dim val作为对象= xRng.Value()
testString = val.ToString
Console.WriteLine(testString)
newXlSheet.Cells(1,1)= testString
回答您的问题为什么B运行而不是A运行。
在A中:
currentWorksheet = xlWorkBook.Sheets.Item( Command Group)
首先,您缺少对象分配的 SET
。其次,您使用的是 Workbook.Sheets.Item()
,它返回 Sheets对象
。 Sheets对象
可以代表图表或工作表,因此没有 .Range()
方法。
您可以通过以下代码进行验证:
将currentWorksheet设置为表格
设置currentWorksheet = ThisWorkbook.Sheets.Item( Command Group)
excelRange = currentWorksheet.Range( A1:A21)
它将出错,并告诉您未找到该方法。
要修复答:请确保您通过使用强类型输入来获取工作表对象。 .Sheets.Item( Command Group)
要修复将来的代码并简化调试过程,我高度重视建议始终在所有模块的顶部声明 Option Explicit
。
为简便起见,您可以将代码缩短为:
将currentWorksheet设为Workshe等
设置currentWorksheet = ThisWorkbook.Sheets( Command Group)
I am trying to find a way to copy a range in one workbook, in this case A6:J21,to another workbook. I thought it would be something like the following...
currentWorksheet = xlWorkBook.Sheets.Item("Command Group")
excelRange = currentWorksheet.Range("A6:J21")
excelDestination = newXlSheet.Range("A6:J21")
excelRange.Copy(excelDestination)
But it gives me an error on excelRange.Copy(excelDestination)
.
The below code runs as expected, so I'm not sure where i'm going wrong here..
Dim xRng As Excel.Range = CType(currentWorksheet.Cells(7, 7), Excel.Range)
Console.WriteLine(xRng.ToString)
Dim val As Object = xRng.Value()
testString = val.ToString
Console.WriteLine(testString)
newXlSheet.Cells(1, 1) = testString
To answer your question "Why is B running, but not A"..
In A:
currentWorksheet = xlWorkBook.Sheets.Item("Command Group")
First, you are missing SET
for your object assignment. Secondly, you are using Workbook.Sheets.Item()
which returns a Sheets object
. A Sheets object
can represent either a chart sheet, or a work sheet, and therefore does not have a .Range()
method.
You can verify this by stepping over this code:
Dim currentWorksheet As Sheets
Set currentWorksheet = ThisWorkbook.Sheets.Item("Command Group")
excelRange = currentWorksheet.Range("A1:A21")
It will error, and tell you that the method is not found.
To Fix A: Ensure you get back a Worksheet object by using strong typing.
Dim currentWorksheet as Worksheet
Set currentWorksheet = ThisWorkbook.Sheets.Item("Command Group")
To fix future code and ease the debugging process I highly recommend always declaring Option Explicit
at the top of all your modules.
For brevity you can shorten your code to:
Dim currentWorksheet as Worksheet
Set currentWorksheet = ThisWorkbook.Sheets("Command Group")
这篇关于VBA将Excel范围复制到其他工作簿的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!