vba - Excel 工作表 : Copy data from one workbook to another workbook

Question

我无法将数据从一个工作簿复制到另一个工作簿。但在同一工作簿中它的工作原理。运行宏程序后，目标工作表为空。我有2个代码。两者都不工作。我的源文件是.xlsx格式，目标文件是.xlsm格式。有没有错误？

代码1:

Sub mycode()

Workbooks.Open Filename:="source_file"
Worksheets("Sheet1").Cells.Select
Selection.Copy


Workbooks.Open Filename:="destination_file"
Worksheets("Sheet1").Cells.Select
Selection.PasteSpecial
ActiveWorkbook.Save


End Sub

代码2

Sub foo2()
Dim x As Workbook
Dim y As Workbook

Set x = Workbooks.Open("source file")
Set y = Workbooks.Open("destination file")

y.Sheets("Sheet1").Range("A1").Value = x.Sheets("Sheet1").Range("A1")

x.Close

End Sub

Answer 1

我假设您在单独的文件中编写 Code1 和 Code2 下面的 Excel 宏，例如 copy_paste.xlsm:

代码 1 在您向 Workbooks.open 提供文件的完整路径时起作用。 :

Sub mycode()

Workbooks.Open Filename:="C:\Users\xyz\Documents\Excel-Problem\source_file.xlsx"
Worksheets("Sheet1").Cells.Select
Selection.Copy

Workbooks.Open Filename:="C:\Users\xyz\Documents\Excel-Problem\destination_file.xlsm"
Worksheets("Sheet1").Cells.Select
Selection.PasteSpecial xlPasteValues               'xlPasteAll to paste everything
ActiveWorkbook.Save

ActiveWorkbook.Close SaveChanges:=True             'to close the file
Workbooks("source_file").Close SaveChanges:=False  'to close the file

End Sub

要粘贴所有内容(公式+值+格式)，请使用粘贴类型xlPasteAll .

代码 2 也有效，您所需要做的就是提供完整路径，并且文件名中缺少 _:

Sub foo2()
Dim x As Workbook
Dim y As Workbook

Set x = Workbooks.Open("C:\Users\xyz\Documents\Excel-Problem\source_file.xlsx")
Set y = Workbooks.Open("C:\Users\xyz\Documents\Excel-Problem\destination_file.xlsm")

'it copies only Range("A1") i.e. single cell
y.Sheets("Sheet1").Range("A1").Value = x.Sheets("Sheet1").Range("A1")

x.Close SaveChanges:=False
y.Close SaveChanges:=True

End Sub