Excel VBA 从外部工作簿取数的5种方法

'======================================================= 
'1、循环单元格取数,效率最低，不可取，初学者易犯 
'2、区域相等取数 
'3、复制粘贴取数 
'4、借助数组取数  
'————以上4种都需要打开外部工作簿 
'5、宏表函数取数（不打开工作簿）  
'======================================================= 
Private Sub GetValueFromOpenedWorkbook() 
'打开工作簿取数
	MyWorkbook As Workbook
	Dim MyArry As Variant
	Set MyWorkbook = Application.Workbooks.Open("D:\外部工作表.xlsx")

	'方法1： 
	'    Dim i As Integer, j As Integer
	'    n2 = MyWorkbook.Sheets.Count
	'    For i = 7 To 56
	'		For j = 4 To 10
	'			Sheets("外部工作表").Cells(i, j) = MyWorkbook.Sheets("外部工作表").Cells(i, j)
	'		Next j
	'    Next i
	'方法2：
	'    ThisWorkbook.Sheets("外部工作表").Range("d5:j56").Value = MyWorkbook.Sheets("外部工作表").Range("d5:j56").Value
	'方法3：
	'    ThisWorkbook.Sheets("外部工作表").Range("d5:j56").Copy
	'    MyWorkbook.Sheets("外部工作表").Range("d5").PasteSpecial Paste:=xlPasteValues
	'方法4：
	MyArry = MyWorkbook.Sheets("外部工作表").Range("d5:j56").Value
	ThisWorkbook.Sheets("外部工作表").Range("d5:j56") = MyArry
	MyWorkbook.Close SaveChanges:=False
	Set MyWorkbook = Nothing 
End Sub 

'方法5：  
Sub GetValueFromClosedWorkbook() 
	'不用打开工作簿取数
	p = "D:\"
	f = "外部工作表.xlsx"
	s = "外部工作表"
	Application.ScreenUpdating = False
	For r = 7 To 56
		For c = 4 To 10
			a = Cells(r, c).Address
			Cells(r, c) = GetValue(p, f, s, a)
		Next c
	Next r
	Application.ScreenUpdating = True 
End Sub

Private Function GetValue(path, file, sheet, ref) 
	'   从未打开的Excel文件中检索数据
	Dim arg As String
	'   确保该文件存在
	If Right(path, 1) <> "" Then path = path & ""
	If Dir(path & file) = "" Then
		GetValue = "File Not Found"
		Exit Function
	End If
	'   创建变量
	arg = "'" & path & "[" & file & "]" & sheet & "'!" & Range(ref).Range("A1").Address(, , xlR1C1)
	'   执行XLM 宏
	GetValue = ExecuteExcel4Macro(arg) 
End Function