VBA:复制范围。运行时错误9 - 下标超出范围
我是VBA新手。我正在尝试将一个工作簿中的列复制到另一个工作簿中。下面是我试图使用的子,但得到“运行时错误9 - 下标超出范围”的错误。有什么建议么?VBA:复制范围。运行时错误9 - 下标超出范围
Sub copydata(wbSource As String, wsSource As String, rangeSource As String, wbDest As String, wsDest As String, rangeDest As String)
Workbooks(wbSource).Worksheets(wsSource).Range(rangeSource).copy Destination:=Workbooks(wbDest).Worksheets(wsDest).Range(rangeDest)
End Sub
Sub result()
' I also tried to set wsSource and wsDest to 1 but still doesn't work
Call copydata("es.csv", "es", "A:B", "Workbook1.xlsm", "result", "A:B")
End Sub
感谢
编辑: 他们是在同一个目录。我该模块中创建Workbook1.xlsm
他们必须同时打开,当你复制,它们都在同一个文件夹并不重要。
你应该打开它们,然后你可以复制数据。
检查两片材名称(ES为es.csv,导致对其Workbook1.xslm)
Sub copydata(wbSource As String, wsSource As String, rangeSource As String,
wbDest As String, wsDest As String, rangeDest As String)
Workbooks(wbSource).Worksheets(wsSource).Range(rangeSource).Copy
Destination:=Workbooks(wbDest).Worksheets(wsDest).Range(rangeDest)
End Sub
Sub result()
' I also tried to set wsSource and wsDest to 1 but still doesn't work
If Not IsWorkbookOpen("es.csv") Then OpenWorkbook ("es.csv")
End If
Call copydata("es.csv", "es", "A:B", "Workbook1.xlsm", "result", "A:B")
End Sub
Sub OpenWorkbook(fileName As String)
Dim activePath As String
activePath = Application.ActiveWorkbook.Path
Application.Workbooks.Open (activePath & Application.PathSeparator &
fileName)
End Sub
Private Function IsWorkbookOpen(wbName As String) As Boolean
Dim wb As Workbook
On Error GoTo Handler
Set wb = Workbooks(wbName)
If wb.Name = wbName Then
Set wb = Nothing
IsWorkbookOpen = True
Else
Handler:
IsWorkbookOpen = False
End If
End Function
哦,是的。我不知道我需要先打开所有文件。无论如何,有没有什么功能可以在不打开所有文件的情况下进行复制? –
如果它是一个csv文件,您可以使用ActiveWorkbook.Queries方法https://msdn.microsoft.com/en-us/vba/excel-vba/articles/queries-add-method-excel – exSnake
确定。非常感谢。 –
一个工作簿/工作表不存在。 –
请检查编辑 –
这是不够的,他们是'在同一个文件夹'。它们必须位于同一工作空间中,才能在不使用完全限定的外部引用的情况下引用另一个工作簿(例如.csv)。 – Jeeped