从已关闭的工作簿复制变量范围

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

你知道我哪里做错了吗?由于

编辑:根据注释，我更改了一行代码，但现在我得到了一个新的错误"Object Required"(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

结束子

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

Answer 2

在vba中学习数组是另一种非常有价值的方法。这样做的目的是尽量减少与表单的交互次数。所以基本上,加载在内存中(数组),做任何你想做的事,愚蠢的最后数据回表:

Option Explicit 'always add this, it will triger an error if you forgot to dim a var
Sub PadslocReadData2()
Dim srcWb As Workbook, FileName As String 'declare your vars
FileName = "12.xlsx" 'make path dynamic, just update filename
Set srcWb = Workbooks.Open(FileName:=Application.ActiveWorkbook.Path & FileName)

Dim arr, LastSrcRow As Long
With srcWb.Sheets("Sheet1") 'use with to avoid retyping
LastSrcRow = .Cells(Rows.Count, "A").End(xlUp).Row 'if your cells are adjacent you can use .currentregion to avoid this step
arr = .Range(.Cells(1, 1), .Cells(LastSrcRow, 7)).Formula 'Add source to array
End With
With ThisWorkbook.Sheets("sls")
.Range(.Cells(1, 1), .Cells(UBound(arr), UBound(arr, 2))).Formula = arr 'dump to target sheet
End With
End Sub