Excel_VBA從關(guān)閉的工作簿中取值多種實現(xiàn)方法(代碼)

1、從關(guān)閉的工作簿中取值有多種方法，現(xiàn)將網(wǎng)上收集的整理向大家共享。方法1、使用公式方法2、使用GetObject函數(shù)方法3、隱藏Application對象方法4、使用ExecuteExcel4Macro方法方法5、使用SQL連接其它收集的相關(guān)內(nèi)容1、使用公式如果需要引用的數(shù)據(jù)不是太多，可以使用公式取得引用工作簿中的工作表數(shù)據(jù)，如下面的代碼所示。1. Sub CopyData_1()2.     Dim Temp As String3.     Temp = "'" & ThisWorkbook.Path & &qu

2、ot;數(shù)據(jù)表.xlsSheet1'!"4.     With Sheet1.Range("A1:F22")5.         .FormulaR1C1 = "=" & Temp & "RC"6.         .Value = .Value7.     End With8. End Sub代碼解析：CopyData_1過程在工作表中寫入公式

3、引用“數(shù)據(jù)表”中同一位置單元格中的數(shù)據(jù)。第3行代碼將引用工作簿的路徑賦給變量Temp。第5行代碼在作表中寫入公式引用數(shù)據(jù)。第6行代碼將公式轉(zhuǎn)換為數(shù)值。2、使用GetObject函數(shù) （返回目錄）使用GetObject函數(shù)來獲取對指定的Excel工作表的引用，如下面的代碼所示。1. Sub CopyData_2()2.     Dim Wb As Workbook3.     Dim Temp As String4.     Application.ScreenUpdating = False5.     Temp

4、= ThisWorkbook.Path & "數(shù)據(jù)表.xls"6.     Set Wb = GetObject(Temp)7.         With Wb.Sheets(1).Range("A1").CurrentRegion8.             Range("A1").Resize(.Rows.Count, .Columns.Count) = .Valu

5、e9.             Wb.Close False10.         End With11.     Set Wb = Nothing12.     Application.ScreenUpdating = True13. End Sub代碼解析：CopyData_2過程使用GetObject函數(shù)來獲取“數(shù)據(jù)表”工作簿中的數(shù)據(jù)。第4行代碼關(guān)閉屏幕更新加快運(yùn)行速度。第5行代碼將引用工作簿的路徑賦給變量Te

6、mp。第6行代碼使用Set語句將GetObject函數(shù)返回的對象賦給對象變量Wb。GetObject函數(shù)返回文件中的ActiveX對象的引用，語法如下：GetObject(pathname , class)參數(shù)pathname是可選的，包含待檢索對象的文件的全路徑和名稱。如果省略，則class參數(shù)是必需的。參數(shù)class是可選的，代表該對象的類的字符串。Class參數(shù)的格式為appname.objecttype，語法的各個部分如表格 1所示。部分描述appname必需的，提供該對象的應(yīng)用程序名稱。objecttype必需的，待創(chuàng)建對象的類型或類。表格 1 Class參數(shù)語法的各個部分第7行到第

7、10行代碼，當(dāng)GetObject函數(shù)指定的對象被激活之后，就可以在代碼中使用對象變量Wb來訪問這個對象的屬性和方法。其中第7、8行代碼將“數(shù)據(jù)表”工作簿中的第1張工作表已使用區(qū)域的數(shù)據(jù)賦給本工作表的單元格，第9行代碼關(guān)閉“數(shù)據(jù)表”工作簿，使用GetObject函數(shù)返回對象的引用時，雖然在窗口中看不到對象的實例，但實際上是打開的，所以需用Close語句將其關(guān)閉。第12行代碼開啟屏幕更新。3、隱藏Application對象（返回目錄）通過隱藏Application對象來模擬不打開工作簿取數(shù)，如下面的代碼所示。1. Sub CopyData_3()2.     Dim myApp

8、 As New Application3.     Dim Sh As Worksheet4.     Dim Temp As String5.     Temp = ThisWorkbook.Path & "數(shù)據(jù)表.xls"6.     myApp.Visible = False7.     Set Sh = myApp.Workbooks.Open(Temp).Sheets(1)8.     With Sh.Range("A1&quo

9、t;).CurrentRegion9.         Range("A1").Resize(.Rows.Count, .Columns.Count) = .Value10.     End With11.     myApp.Quit12.     Set Sh = Nothing13.     Set myApp = Nothing14. End Sub代碼解析：CopyData_3過程隱藏Application對象來模擬不打開工作

10、簿取數(shù)。第2行代碼使用New關(guān)鍵字隱式地創(chuàng)建一個Application對象。第6行代碼將新創(chuàng)建的Application對象的Visible屬性設(shè)置為False，使之隱藏。第7行代碼使用Open方法打開“數(shù)據(jù)表”工作簿（關(guān)于Open方法請參閱技巧42 ，因為工作簿是使用新創(chuàng)建的、隱藏的Application對象打開的，所以在窗口中是不可視的。第8行到第10行代碼將“數(shù)據(jù)表”工作簿中的第1張工作表已使用區(qū)域的數(shù)據(jù)賦給本工作表的單元格。第11行代碼使用Quit方法退出新打開的Excel程序。4、使用ExecuteExcel4Macro方法（返回目錄）使用ExecuteExcel4Macro方法可以做

11、到不打開工作簿的情況下獲取其他工作薄中指定工作表的數(shù)據(jù)，如下面的代碼所示。1. Sub CopyData_4()2.     Dim RCount As Long3.     Dim CCount As Long4.     Dim Temp As String5.     Dim Temp1 As String6.     Dim Temp2 As String7.     Dim Temp3 As String8.     Dim R As Long9

12、.     Dim C As Long10.     Dim arr() As Variant11.     Temp = "'" & ThisWorkbook.Path & "數(shù)據(jù)表.xlsSheet1'!"12.     Temp1 = Temp & Rows(1).Address(, , xlR1C1)13.     Temp1 = "Counta(" & Temp1 & &

13、quot;)"14.     CCount = Application.ExecuteExcel4Macro(Temp1)15.     Temp2 = Temp & Columns("A").Address(, , xlR1C1)16.     Temp2 = "Counta(" & Temp2 & ")"17.     RCount = Application.ExecuteExcel4Macro(Temp2)18.

14、    ReDim arr(1 To RCount, 1 To CCount)19.     For R = 1 To RCount20.         For C = 1 To CCount21.             Temp3 = Temp & Cells(R, C).Address(, , xlR1C1)22.          

15、0;  arr(R, C) = Application.ExecuteExcel4Macro(Temp3)23.         Next24.     Next25.     Range("A1").Resize(RCount, CCount).Value = arr26. End Sub代碼解析：CopyData_4過程使用ExecuteExcel4Macro方法獲取“數(shù)據(jù)表”工作薄中指定工作表的數(shù)據(jù)。第14、16行代碼使用ExecuteExcel4Macro方

16、法執(zhí)行Counta函數(shù)取得“數(shù)據(jù)表”工作薄中指定工作表的行數(shù)和列數(shù)合計。ExecuteExcel4Macro方法執(zhí)行一個Microsoft Excel 4.0宏函數(shù)，然后返回此函數(shù)的結(jié)果，語法如下：expression.ExecuteExcel4Macro(String)參數(shù)expression是可選的，返回一個Application對象。參數(shù)String是必需的，一個不帶等號的Microsoft Excel 4.0宏語言函數(shù)，所有引用必須是像R1C1這樣的字符串。因為Microsoft Excel 4.0 宏不在當(dāng)前工作簿或工作表的環(huán)境中求值，所有的引用都是外部引用，所以無需打開引用工作簿但

17、是需要明確指定工作簿名稱。第18行代碼使用ReDim語句為動態(tài)數(shù)組arr重新分配存儲空間。第19行到第24行代碼循環(huán)取值，將“數(shù)據(jù)表”工作薄中指定工作表的數(shù)據(jù)賦給動態(tài)數(shù)組arr。第25行代碼將動態(tài)數(shù)組arr的值賦給工作表的單元格。5、使用SQL連接（返回目錄）使用SQL建立與工作簿的連接，查詢數(shù)據(jù)記錄后復(fù)制到當(dāng)前工作表中，如下面的代碼所示。1. Sub CopyData_5()2.     Dim Sql As String3.     Dim j As Integer4.     Dim R As Integer5.  

18、  Dim Cnn As ADODB.Connection6.     Dim rs As ADODB.Recordset7.     With Sheet58.         .Cells.Clear9.         Set Cnn = New ADODB.Connection10.         With Cnn11.     

19、;        .Provider = "microsoft.jet.oledb.4.0"12.             .ConnectionString = "Extended Properties=Excel 8.0;" _13.                 & "Data Source="

20、; & ThisWorkbook.Path & "數(shù)據(jù)表"14.             .Open15.         End With16.         Set rs = New ADODB.Recordset17.         Sql = "select * from Shee

21、t1$"18.         rs.Open Sql, Cnn, adOpenKeyset, adLockOptimistic19.             For j = 0 To rs.Fields.Count - 120.                 .Cells(1, j + 1) = rs.Fields(j).Name21.

22、60;           Next22.         R = .Range("A65536").End(xlUp).Row23.         .Range("A" & R + 1).CopyFromRecordset rs24.     End With25.     rs.Close26.   &#

23、160; Cnn.Close27.     Set rs = Nothing28.     Set Cnn = Nothing29. End Sub代碼解析：CopyData_5過程使建立與“數(shù)據(jù)表”工作簿的連接，查詢數(shù)據(jù)記錄后復(fù)制到當(dāng)前工作表中。第8行代碼刪除當(dāng)前工作表的所有數(shù)據(jù)。第9行到第15行代碼建立與“數(shù)據(jù)表”工作簿的連接。第16行到第24行代碼查詢“數(shù)據(jù)表”工作簿的全部數(shù)據(jù)，并復(fù)制到工作表中。其中第20行代碼將字段名稱（標(biāo)題行）復(fù)制到工作表中，第23行代碼將查詢到的數(shù)據(jù)記錄復(fù)制到工作表。其它收集的相關(guān)內(nèi)容：（返回目錄）示例代碼1：Sub tes

24、tGetValuesFromClosedWorkbook() GetValuesFromAClosedWorkbook "C:", "Book1.xls", "Sheet1", "A1:G20"End Sub Sub GetValuesFromAClosedWorkbook(fPath As String, _ fName As String, sName, cellRange As String) With ActiveSheet.Range(cellRange) .FormulaArray = &qu

25、ot;='" & fPath & "" & fName & "" _ & sName & "'!" & cellRange .Value = .Value End WithEnd Sub本示例包含一個子過程GetValuesFromAClosedWorkbook，用來從已關(guān)閉的工作簿中獲取數(shù)據(jù)，主過程testGetValuesFromClosedWorkbook用來傳遞參數(shù)。本示例表示從C盤根目錄下的Book1.xls工作簿的工作表Sheet1中的A1:

26、G20單元格區(qū)域內(nèi)獲取數(shù)據(jù)，并將其復(fù)制到當(dāng)前工作表相應(yīng)單元格區(qū)域中。示例代碼2：已前面的代碼相似，下面的VBA代碼從關(guān)閉的工作簿中獲取值。Sub ExtractDataFromClosedWorkBook()  Application.ScreenUpdating = False  '創(chuàng)建鏈接來從關(guān)閉的工作簿中獲取數(shù)據(jù) '可以將相關(guān)代碼修改為相應(yīng)的路徑和單元格 With Sheet1!A1:B4 .Value = "='" & ActiveWorkbook.Path & "testDataWorkbook

27、.xlsSheet1'!A1:B4" '刪除鏈接 .Value = .Value End With  Application.ScreenUpdating = True End Sub其中，可以將代碼中的路徑修改為需要從中獲取值的工作簿的路徑，單元格也作相應(yīng)的修改。示例代碼3：Sub GetDataFromClosedWorkbook() Dim wb As Workbook Application.ScreenUpdating = False '以只讀方式打開工作簿 Set wb = Workbooks.Open("C:文件夾名

28、文件.xls", True, True) With ThisWorkbook.Worksheets("工作表名") '從工作簿中讀取數(shù)據(jù) .Range("A10").Formula = wb.Worksheets("源工作表名").Range("A10").Formula .Range("A11").Formula = wb.Worksheets("源工作表名").Range("A20").Formula .Range("A12

29、").Formula = wb.Worksheets("源工作表名").Range("A30").Formula .Range("A13").Formula = wb.Worksheets("源工作表名").Range("A40").Formula End With wb.Close False '關(guān)閉打開的源數(shù)據(jù)工作簿且不保存任何變化 Set wb = Nothing '釋放內(nèi)存 Application.ScreenUpdating = TrueEnd Sub在運(yùn)行程

30、序時，打開所要獲取數(shù)據(jù)的工作簿，當(dāng)取得數(shù)據(jù)后再關(guān)閉該工作簿。將屏幕更新屬性值設(shè)置為False，將看不出源數(shù)據(jù)工作簿是否被打開過。本程序代碼中，“C:文件夾名文件.xls”、”源工作表名”代表工作簿所在的文件夾和工作簿文件名。示例代碼4：下面是JOHN WALKENBACH先生使用VBA編寫的一個實用函數(shù)，其作用是從關(guān)閉的工作簿中取值。VBA沒有包含從關(guān)閉的文件中獲取值的方法，但是利用Excel處理連接文件的功能，可以實現(xiàn)。該函數(shù)要調(diào)用XLM宏，但不能在工作表公式中使用該函數(shù)。GetValue函數(shù)具有四個參數(shù)，分別如下：· path: 關(guān)閉的文件的驅(qū)動器和路徑(例如”d:files”)

31、· file: 工作簿名稱(例如”99budget.xls”)· sheet: 工作表名稱(例如”Sheet1)· ref: 單元格引用(例如”C4)Private Function GetValue(path, file, sheet, ref)' 從一個關(guān)閉的工作簿中獲取值 Dim arg As String ' 確保該文件存在 If Right(path, 1) <> "" Then path = path & "" If Dir(path & file) = "&

32、quot; Then GetValue = "File Not Found" Exit Function End If' 創(chuàng)建參數(shù) arg = "'" & path & "" & file & "" & sheet & "'!" & _ Range(ref).Range("A1").Address(, , xlR1C1)' 執(zhí)行XLM宏 GetValue = ExecuteExcel4Ma

33、cro(arg)End Function使用GetValue函數(shù)要使用該函數(shù)，將其復(fù)制到VBA模塊中，然后使用合適的參數(shù)調(diào)用該函數(shù)。子過程演示如下，簡單地顯示在名為99Budget.xls工作簿Sheet1的單元格A1中的值，該文件在驅(qū)動器C:中的XLFilesBudget目錄下。Sub TestGetValue() p = "c:XLFilesBudget" f = "99Budget.xls" s = "Sheet1" a = "A1" MsgBox GetValue(p, f, s, a)End Sub另一個

34、示例如下，該過程從一個關(guān)閉的文件中讀取1,200個值(100行和12列)，并將這些值放置到活動工作表中。Sub TestGetValue2() p = "c:XLFilesBudget" f = "99Budget.xls" s = "Sheet1" Application.ScreenUpdating = False For r = 1 To 100 For c = 1 To 12 a = Cells(r, c).Address Cells(r, c) = GetValue(p, f, s, a) Next c Next r App

35、lication.ScreenUpdating = TrueEnd Sub注意：為了使該函數(shù)正常運(yùn)行，在Excel中必須有一個活動工作表。如果所有窗口都是隱藏的，或者活動工作表為圖表工作表，那么將產(chǎn)生錯誤。示例代碼5：Sub ReadDataFromAllWorkbooksInFolder() Dim FolderName As String, wbName As String, r As Long, cValue As Variant Dim wbList() As String, wbCount As Integer, i As Integer FolderName = "C:文件夾名" '創(chuàng)