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

admin

51-1        使用公式

1. Sub CopyData_1()
   8 }$ e. j5 o0 o! H+ J7 _
2.       Dim Temp As String
   
3.       Temp = "'" & ThisWorkbook.Path & "\[数据表.xls]Sheet1'!"
   
4.       With Sheet1.Range("A1:F22")
   ' ]* L: E0 Z; y: X7 U  t1 o" ~/ L1 N
5.           .FormulaR1C1 = "=" & Temp & "RC"
   
6.           .Value = .Value
   : H+ F. M, Q+ I6 D: y5 }& P7 {. l9 X
7.       End With
   
8.   End Sub

复制代码

3 D0 k4 O, O7 d( u5 a9 K) z& [代码解析：
. V& v2 Q* A7 qCopyData_1过程在工作表中写入公式引用“数据表”中同一位置单元格中的数据。
第3行代码将引用工作簿的路径赋给变量Temp。
第5行代码在作表中写入公式引用数据。
5 I; O% O5 X  I第6行代码将公式转换为数值。

admin

51-2        使用GetObject函数
, r$ E4 z2 d- ^& E' ?! }5 U
1 V1 \+ E2 r, s8 A$ U0 P

1. Sub CopyData_2()
   
2.       Dim Wb As Workbook
   
3.       Dim Temp As String
   
4.       Application.ScreenUpdating = False
   - b! E) Z% `6 Q! E; B- h! R+ u6 {
5.       Temp = ThisWorkbook.Path & "\数据表.xls"
   + j5 d- B; |0 R% x( M
6.       Set Wb = GetObject(Temp)
   
7.           With Wb.Sheets(1).Range("A1").CurrentRegion
   
8.               Range("A1").Resize(.Rows.Count, .Columns.Count) = .Value
   
9.               Wb.Close False
   3 K5 ~7 C9 N: X+ X1 `& I
10.           End With
    0 s1 @! ]8 S$ y; J# z
11.       Set Wb = Nothing
    
12.       Application.ScreenUpdating = True
    
13.   End Sub

复制代码

- F& c# f4 V, l# F) ^9 _, [代码解析：
7 F! E& n* W/ j  ]3 @) A: o4 TCopyData_2过程使用GetObject函数来获取“数据表”工作簿中的数据。
第4行代码关闭屏幕更新加快运行速度。
7 {" S' ]& H* k9 {1 e第5行代码将引用工作簿的路径赋给变量Temp。
第6行代码使用Set语句将GetObject函数返回的对象赋给对象变量Wb。
GetObject函数返回文件中的ActiveX对象的引用
/ r( j2 B- _: a$ O3 z参数pathname是可选的，包含待检索对象的文件的全路径和名称。如果省略，则class参数是必需的。
( @; o% L, ]( m参数class是可选的，代表该对象的类的字符串。
8 y( H$ J* p: ?' e: T. ~第7行到第10行代码，当GetObject函数指定的对象被激活之后，就可以在代码中使用对象变量Wb来访问这个对象的属性和方法。
8 u  i) c8 a8 n5 D3 Q" k6 S其中第7、8行代码将“数据表”工作簿中的第1张工作表已使用区域的数据赋给本工作表的单元格，第9行代码关闭“数据表”工作簿，使用GetObject函数返回对象的引用时，虽然在窗口中看不到对象的实例，但实际上是打开的，所以需用Close语句将其关闭。
- |( ]: i+ H) C: i3 _! L第12行代码开启屏幕更新。
. `) g3 d: b) c$ y, Z9 A: H

admin

51-3        隐藏Application对象
& h/ X' f4 O+ q  E# e2 I# S通过隐藏Application对象来模拟不打开工作簿取数，如下面的代码所示。

1. Sub CopyData_3()
   
2.       Dim myApp As New Application
   & |9 a: d/ N- F0 T2 y! D
3.       Dim Sh As Worksheet
   
4.       Dim Temp As String
   ; p' S( W5 R, Q6 L- Z
5.       Temp = ThisWorkbook.Path & "\数据表.xls"
   $ K8 q# f; j" F9 H* Y* l1 J
6.       myApp.Visible = False
   5 B2 N$ J; ?. K# ~
7.       Set Sh = myApp.Workbooks.Open(Temp).Sheets(1)
   
8.       With Sh.Range("A1").CurrentRegion
   
9.           Range("A1").Resize(.Rows.Count, .Columns.Count) = .Value
   
10.       End With
    
11.       myApp.Quit
    
12.       Set Sh = Nothing
    
13.       Set myApp = Nothing
    
14. End Sub

复制代码

代码解析：
3 g, n0 N+ @" q2 [CopyData_3过程隐藏Application对象来模拟不打开工作簿取数。
$ h6 C! ]- Z/ h5 T9 _$ W1 V6 j+ j第2行代码使用New关键字隐式地创建一个Application对象。
9 n1 v: e/ F0 y4 w第6行代码将新创建的Application对象的Visible属性设置为False，使之隐藏。
" D4 D! L* G& E: X8 G第7行代码使用Open方法打开“数据表”工作簿（关于Open方法请参阅技巧42 ，因为工作簿是使用新创建的、隐藏的Application对象打开的，所以在窗口中是不可视的。
) o8 }, R( i0 I- t第8行到第10行代码将“数据表”工作簿中的第1张工作表已使用区域的数据赋给本工作表的单元格。
第11行代码使用Quit方法退出新打开的Excel程序。

admin

51-4  使用ExecuteExcel4Macro方法

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

1. Sub CopyData_4()
   
2.       Dim RCount As Long
   
3.       Dim CCount As Long
   
4.       Dim Temp As String
   
5.       Dim Temp1 As String
   ( ^; Z# E0 E+ C4 O) k; s: f+ R
6.       Dim Temp2 As String
   
7.       Dim Temp3 As String
   
8.       Dim R As Long
   1 ^  w4 j: Z$ n7 b! K! l" @5 ^
9.       Dim C As Long
   
10.       Dim arr() As Variant
    ! H7 R2 d7 b. g+ s1 g
11.       Temp = "'" &ThisWorkbook.Path & "\[数据表.xls]Sheet1'!"
    ( _' F. m/ y, |9 Z- u
12.       Temp1 = Temp & Rows(1).Address(, ,xlR1C1)
    
13.       Temp1 = "Counta(" & Temp1& ")"
    
14.       CCount =Application.ExecuteExcel4Macro(Temp1)
    , ?( V+ f* S0 y, f8 ~% J' e
15.       Temp2 = Temp &Columns("A").Address(, , xlR1C1)
    
16.       Temp2 = "Counta(" & Temp2& ")"
    % j# `, L$ b6 G0 t# v" j% c1 j- `& j2 j
17.       RCount = Application.ExecuteExcel4Macro(Temp2)
    : }6 j: e9 ]8 a& o
18.       ReDim arr(1 To RCount, 1 To CCount)
    $ I4 i4 d; z, p& q8 R
19.       For R = 1 To RCount
    ( V9 f( C+ k4 _' K
20.           For C = 1 To CCount
      ?$ Q  T* R8 v# P4 a* [8 _* `
21.               Temp3 = Temp & Cells(R,C).Address(, , xlR1C1)
    
22.               arr(R, C) = Application.ExecuteExcel4Macro(Temp3)
    + t0 K9 k: [8 E
23.           Next
    1 m" V6 v+ h0 z# y1 H
24.       Next
    , t. v3 E; k7 U: G" h4 ^
25.       Range("A1").Resize(RCount,CCount).Value = arr
    # d) }5 t. h% h# h
26. End Sub

复制代码

代码解析：

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

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

ExecuteExcel4Macro方法执行一个Microsoft Excel 4.0宏函数，然后返回此函数的结果，语法如下：

expression.ExecuteExcel4Macro(String)

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

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

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

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

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

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

2 s! V- e* P+ l6 c- d
( e* c4 n8 V7 _! t, t& g  M

admin

51-5        使用SQL连接


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

1. Sub CopyData_5()
   2 L$ Z6 T3 ?5 f& r. i9 ^# v
2.       Dim Sql As String
   
3.       Dim j As Integer
   * }( N$ _' H' l0 f- F9 x% @7 R
4.       Dim R As Integer
   . f3 \( F+ \3 C, d
5.       Dim Cnn As ADODB.Connection
   6 ^" [! D* q6 I  t) `5 e# @/ n
6.       Dim rs As ADODB.Recordset
   
7.       With Sheet5
   , `! K! ]. P# b, W& a
8.           .Cells.Clear
   
9.           Set Cnn = New ADODB.Connection
   
10.           With Cnn
    1 e4 H: ?; _& z0 t- O
11.               .Provider = "microsoft.jet.oledb.4.0"
    - F. O; P' i6 ^+ R( t4 e4 ^
12.               .ConnectionString = "Extended Properties=Excel 8.0;" _
    
13.                   & "Data Source=" & ThisWorkbook.Path & "\数据表"
    " l4 a' Y3 ]; j6 U& Z
14.               .Open
    : |" Q8 P+ A1 u; P( D
15.           End With
    
16.           Set rs = New ADODB.Recordset
    1 ?% b5 R/ A( y: B# w
17.           Sql = "select * from [Sheet1$]"
    # u& B' C. f' P( V
18.           rs.Open Sql, Cnn, adOpenKeyset, adLockOptimistic
    
19.               For j = 0 To rs.Fields.Count - 1
    
20.                   .Cells(1, j + 1) = rs.Fields(j).Name
    
21.               Next
    . E6 p: Y9 E; x& k  Z8 _  v4 j
22.           R = .Range("A65536").End(xlUp).Row
    4 {* g4 l* ^9 v( v( P3 R7 r
23.           .Range("A" & R + 1).CopyFromRecordset rs
    9 t) w# P1 Q; r1 q" I& J% i; S
24.       End With
    
25.       rs.Close
    
26.       Cnn.Close
    6 I0 i4 T3 B$ S* E6 N2 z- M' U
27.       Set rs = Nothing
    
28.       Set Cnn = Nothing
    
29. End Sub

复制代码

. j4 i8 O8 k- ]+ y# w8 J代码解析：
& N. C: m9 ?( Q: u+ }& aCopyData_5过程使建立与“数据表”工作簿的连接，查询数据记录后复制到当前工作表中。
第8行代码删除当前工作表的所有数据。
第9行到第15行代码建立与“数据表”工作簿的连接。
# r6 J6 v( W6 g; g! V" p' a第16行到第24行代码查询“数据表”工作簿的全部数据，并复制到工作表中。
/ ~1 J/ B2 v7 n( X% ]第20行代码将字段名称（标题行）复制到工作表中，
第23行代码将查询到的数据记录复制到工作表。

9 n! U9 t, w; [& ?  C3 w# _* m. k
' f7 t# F& G- d% U' j0 f) ^( [
3 w2 |  i! H9 T& w) b