excel - 从关闭的工作簿中复制变量范围

Question

正如标题所说，我正在尝试从已关闭的工作簿中复制变量范围。我收到“对象不支持此属性或方法”错误。这是我的代码：

Sub PadslocReadData()

    Application.ScreenUpdating = False
    
    Dim src As Workbook
    Dim LastSrcRow As Integer
    Dim curWorkbook As Workbook
    
    Set curWorkbook = ThisWorkbook
    Set src = Workbooks.Open("\\filename.xls", False, False)
    LastSrcRow = src.Cells(Rows.count, "A").End(xlUp).row
    curWorkbook.Worksheets("sls").Range("A1:G" & LastSrcRow).Formula = 
    src.Worksheets("Sheet1").Range("A1:G" & LastSrcRow).Formula

    src1.Close False

End Sub

知道我哪里出错了吗？谢谢

编辑：根据评论我更改了一行代码，但现在我收到一个新错误“需要对象”（424）错误。

子 PadslocReadData()

Application.ScreenUpdating = False

Dim source As Workbook
Dim LastSrcRow As Long
Dim curWorkbook As Workbook
    
Set curWorkbook = ThisWorkbook
Set source = Workbooks.Open("\\filename.xls", False, False)
LastSrcRow = source.Sheets("Sheet1").Cells(Rows.count, 
"A").End(xlUp).row
curWorkbook.Worksheets("sls").Range("A1:G" & LastSrcRow).Formula = 
source.Worksheets("Sheet1").Range("A1:G" & LastSrcRow).Formula

src1.Close False

结束子

标签： excelvba

Answer 1

你能试试这个代码。根据您的要求更改文件路径和工作表名称

Sub PadslocReadData()

Dim wbtarget, wbsource As Workbook
Dim wstarget, wssource   As Worksheet
Dim lastrow As Long



Set wbtarget = ThisWorkbook
Set wstarget = wbtarget.Worksheets("Sheet1")

wstarget.Cells.Clear

'Filepath
Filepath = "C:\test.xlsx"

Set wbsource = Workbooks.Open(Filepath, UpdateLinks:=0)
Set wssource = wbsource.Worksheets("sheet1")

lastrow = wssource.Cells(wssource.Rows.Count, "A").End(xlUp).Row

wstarget.Range("A1:G" & lastrow).Formula = wssource.Range("A1:G" & lastrow).Formula

wbsource.Close savechanges:=False

End Sub