最新ExcelVBA实例教程#051不打开工作簿取得其他工作簿数据.docx

最新ExcelVBA实例教程#051不打开工作簿取得其他工作簿数据.docx

《最新ExcelVBA实例教程#051不打开工作簿取得其他工作簿数据.docx》由会员分享，可在线阅读，更多相关《最新ExcelVBA实例教程#051不打开工作簿取得其他工作簿数据.docx（7页珍藏版）》请在冰豆网上搜索。

最新ExcelVBA实例教程#051不打开工作簿取得其他工作簿数据

Excel-VBA实例教程-#051：

不翻开工作簿取得其他工作簿数据

在Excel的使用过程中，经常需要引用其他工作簿的数据，而用户往往希望能在不翻开工作簿或看似不翻开工作簿的情况下取得其他工作簿中的数据，有以下几种方法可以实现。

1、使用公式

如果需要引用的数据不是太多，可以使用公式取得引用工作簿中的工作表数据，如下面的代码所示。

1.SubCopyData_1（）

2.  DimTempAsString

3.  Temp="'"&ThisWorkbook.Path&"\[数据表.xls]Sheet1'!

"

4.  WithSheet1.Range（"A1:

F22"）

5.      .FormulaR1C1="="&Temp&"RC"

6.      .Value=.Value

7.  EndWith

8.EndSub

代码解析：

CopyData_1过程在工作表中写入公式引用“数据表〞中同一位置单元格中的数据。

第3行代码将引用工作簿的路径赋给变量Temp。

第5行代码在作表中写入公式引用数据。

第6行代码将公式转换为数值。

2、使用GetObject函数

使用GetObject函数来获取对指定的Excel工作表的引用，如下面的代码所示。

1.SubCopyData_2（）

2.  DimWbAsWorkbook

3.  DimTempAsString

4.  Application.ScreenUpdating=False

5.  Temp=ThisWorkbook.Path&"\数据表.xls"

6.  SetWb=GetObject（Temp）

7.      WithWb.Sheets

（1）.Range（"A1"）.CurrentRegion

8.        Range（"A1"）.Resize（.Rows.Count,.Columns.Count）=.Value

9.        Wb.CloseFalse

10.      EndWith

11.  SetWb=Nothing

12.  Application.ScreenUpdating=True

13.EndSub

代码解析：

CopyData_2过程使用GetObject函数来获取“数据表〞工作簿中的数据。

第4行代码关闭屏幕更新加快运行速度。

域的数据赋给本工作表的单元格，第9行代码关闭“数据表〞工作簿，使用GetObject函数返回对象的引用时，虽然在窗口中看不到对象的实例，但实际上是翻开的，所以需用Close语句将其关闭。

第12行代码开启屏幕更新。

3、隐藏Application对象

通过隐藏Application对象来模拟不翻开工作簿取数，如下面的代码所示。

1.SubCopyData_3（）

2.  DimmyAppAsNewApplication

3.  DimShAsWorksheet

4.  DimTempAsString

5.  Temp=ThisWorkbook.Path&"\数据表.xls"

6.  myApp.Visible=False

7.  SetSh=myApp.Workbooks.Open（Temp）.Sheets

（1）

8.  WithSh.Range（"A1"）.CurrentRegion

9.      Range（"A1"）.Resize（.Rows.Count,.Columns.Count）=.Value

10.  EndWith

11.  myApp.Quit

12.  SetSh=Nothing

13.  SetmyApp=Nothing

14.EndSub

代码解析：

CopyData_3过程隐藏Application对象来模拟不翻开工作簿取数。

第2行代码使用New关键字隐式地创立一个Application对象。

第6行代码将新创立的Application对象的Visible属性设置为False，使之隐藏。

第7行代码使用Open方法翻开“数据表〞工作簿〔关于Open方法请参阅技巧42，因为工作簿是使用新创立的、隐藏的Application对象翻开的，所以在窗口中是不可视的。

第8行到第10行代码将“数据表〞工作簿中的第1张工作表已使用区域的数据赋给本工作表的单元格。

第11行代码使用Quit方法退出新翻开的Excel程序。

4、使用ExecuteExcel4Macro方法

使用ExecuteExcel4Macro方法可以做到不翻开工作簿的情况下获取其他工作薄中指定工作表的数据，如下面的代码所示。

1.SubCopyData_4（）

2.  DimRCountAsLong

3.  DimCCountAsLong

4.  DimTempAsString

5.  DimTemp1AsString

6.  DimTemp2AsString

7.  DimTemp3AsString

8.  DimRAsLong

9.  DimCAsLong

10.  Dimarr（）AsVariant

11.  Temp="'"&ThisWorkbook.Path&"\[数据表.xls]Sheet1'!

"

12.  Temp1=Temp&Rows

（1）.Address（,,xlR1C1）

13.  Temp1="Counta（"&Temp1&"）"

14.  CCount=Application.ExecuteExcel4Macro（Temp1）

15.  Temp2=Temp&Columns（"A"）.Address（,,xlR1C1）

16.  Temp2="Counta（"&Temp2&"）"

17.  RCount=Application.ExecuteExcel4Macro（Temp2）

18.  ReDimarr（1ToRCount,1ToCCount）

19.  ForR=1ToRCount

20.      ForC=1ToCCount

21.        Temp3=Temp&Cells（R,C）.Address（,,xlR1C1）

22.        arr（R,C）=Application.ExecuteExcel4Macro（Temp3）

23.      Next

24.  Next

25.  Range（"A1"）.Resize（RCount,CCount）.Value=arr

26.EndSub

代码解析：

CopyData_4过程使用ExecuteExcel4Macro方法获取“数据表〞工作薄中指定工作表的数据。

第14、16行代码使用ExecuteExcel4Macro方法执行Counta函数取得“数据表〞工作薄中指定工作表的行数和列数合计。

ExecuteExcel4Macro方法执行一个MicrosoftExcel4.0宏函数，然后返回此函数的结果，语法如下：

expression.ExecuteExcel4Macro（String）

参数expression是可选的，返回一个Application对象。

参数String是必需的，一个不带等号的MicrosoftExcel4.0宏语言函数，所有引用必须是像R1C1这样的字符串。

因为MicrosoftExcel4.0宏不在当前工作簿或工作表的环境中求值，所有的引用都是外部引用，所以无需翻开引用工作簿但是需要明确指定工作簿名称。

第18行代码使用ReDim语句为动态数组arr重新分配存储空间。

第19行到第24行代码循环取值，将“数据表〞工作薄中指定工作表的数据赋给动态数组arr。

第25行代码将动态数组arr的值赋给工作表的单元格。

5、使用SQL连接

使用SQL建立与工作簿的连接，查询数据记录后复制到当前工作表中，如下面的代码所示。

1.SubCopyData_5（）

2.  DimSqlAsString

3.  DimjAsInteger

4.  DimRAsInteger

5.  DimCnnAsADODB.Connection

6.  DimrsAsADODB.Recordset

7.  WithSheet5

8.      .Cells.Clear

9.      SetCnn=NewADODB.Connection

10.      WithCnn

11.        .Provider="microsoft.jet.oledb.4.0"

12.        .ConnectionString="ExtendedProperties=Excel8.0;"_

13.          &"DataSource="&ThisWorkbook.Path&"\数据表"

14.        .Open

15.      EndWith

16.      Setrs=NewADODB.Recordset

17.      Sql="select*from[Sheet1$]"

18.      rs.OpenSql,Cnn,adOpenKeyset,adLockOptimistic

19.        Forj=0Tors.Fields.Count-1

20.          .Cells（1,j+1）=rs.Fields（j）.Name

21.        Next

22.      R=.Range（"A65536"）.End（xlUp）.Row

23.      .Range（"A"&R+1）.CopyFromRecordsetrs

24.  EndWith

25.  rs.Close

26.  Cnn.Close

27.  Setrs=Nothing

28.  SetCnn=Nothing

29.EndSub

代码解析：

CopyData_5过程使建立与“数据表〞工作簿的连接，查询数据记录后复制到当前工作表中。

第8行代码删除当前工作表的所有数据。

第9行到第15行代码建立与“数据表〞工作簿的连接。

第16行到第24行代码查询“数据表〞工作簿的全部数据，并复制到工作表中。

其中第20行代码将字段名称〔标题行〕复制到工作表中，第23行代码将查询到的数据记录复制到工作表。