将数据从一个电子表格复制到另一个电子表格中

I am trying to copy data from one spreadsheet to another (which will already have data in). I need to copy (from source spreadsheet) all populated cells to column B in the destination spreadsheet but at the last row (not overwriting existing data)

我正在尝试将数据从一个电子表格复制到另一个电子表格(其中已经包含了数据)。我需要将(从源电子表格中)所有填充的单元格复制到目标电子表格中的B列，但在最后一行(不覆盖现有数据)

here is what I have so far but it is not exactly what I need and is throwing an error saying:

这是我到目前为止所拥有的，但并不是我所需要的，我说错了:

object required

对象要求

at this line: Last_Row = Range("A" & Rows.Count).End(xlUp).Row)

在这一行:Last_Row = Range(“A”& row . count).End(xlUp).Row)

And I'm trying to do this in a .VBS file

我试着在。vbs文件中做这个

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open("D:\Test2.xlsx")
Set objWorksheet = objWorkbook.Worksheets("Sheet 2")

Set objRange = objWorksheet.Range("A5:D5")
objRange.Copy

'destination spreadsheet stuff:
Set objExcel2 = CreateObject("Excel.Application")
objExcel2.Visible = True
Set objWorkbook2 = objExcel.Workbooks.Open("D:\Test1.xlsx")
Set objWorksheet2 = objWorkbook2.Worksheets("Sheet1")   

Last_Row = Range("A" & Rows.Count).End(xlUp).Row
objWorksheet2.Range("B" & Last_Row).Activate
objWorksheet2.Paste

1 个解决方案

#1

I changed Last_Row to Next_Row because, you'll want to paste on the next line after the data (hence + 1).

我将Last_Row更改为Next_Row，因为您将希望在数据之后粘贴下一行(因此是+ 1)。

And got rid of the second Excel instance, because pasting between two instances is very limited and programmatically very inefficient!

并删除第二个Excel实例，因为在两个实例之间粘贴非常有限，而且编程效率非常低!

So this should work just fine :

所以这个应该没问题:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open("D:\Test2.xlsx")
Set objWorksheet = objWorkbook.Worksheets("Sheet 2")

Set objRange = objWorksheet.Range("A5:D5")
objRange.Copy

'destination spreadsheet stuff:
Set objWorkbook2 = objExcel.Workbooks.Open("D:\Test1.xlsx")
Set objWorksheet2 = objWorkbook2.Worksheets("Sheet1")

Next_Row = objWorksheet2.Range("B" & objWorksheet2.Rows.Count).End(xlUp).Row + 1
objWorksheet2.Range("B" & Next_Row).Paste

#1