正如標題所說，我正在嘗試從封閉的作業簿中復制變數范圍。我收到“物件不支持此屬性或方法”錯誤。這是我的代碼：
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
結束子
uj5u.com熱心網友回復：
你可以試試這個代碼。根據您的要求更改檔案路徑和作業表名稱
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
uj5u.com熱心網友回復：
或者，在 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
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/313370.html
上一篇：如何將表格展平為一行？