wdmexcel / Excel Vba / VBA常用代码解析(第十一讲)

分享

   

VBA常用代码解析(第十一讲)

2015-08-21  wdmexcel



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

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

051-1 使用公式

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

Sub CopyData_1()

DimTemp As String

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

WithSheet1.Range('A1:F22')

.FormulaR1C1 = '=' & Temp & 'RC'

.Value = .Value

EndWith

End Sub

代码解析:

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

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

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

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

051-2 使用GetObject函数

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

Sub CopyData_2()

DimWb As Workbook

DimTemp As String

Application.ScreenUpdating= False

Temp= ThisWorkbook.Path & '\数据表.xls'

SetWb = GetObject(Temp)

With Wb.Sheets(1).Range('A1').CurrentRegion

Range('A1').Resize(.Rows.Count.Columns.Count) = .Value

Wb.Close False

End With

SetWb = Nothing

Application.ScreenUpdating= True

End Sub

代码解析:

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

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

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

6行代码使用Set语句将GetObject函数返回的对象赋给对象变量Wb

GetObject函数返回文件中的ActiveX对象的引用,语法如下:

GetObject([pathname][class])

参数pathname是可选的,包含待检索对象的文件的全路径和名称。如果省略,则class参数是必需的。

参数class是可选的,代表该对象的类的字符串。

Class参数的格式为appname.objecttype,语法的各个部分如表格所示。

7行到第10行代码,当GetObject函数指定的对象被激活之后,就可以在代码中使用对象变量Wb来访问这个对象的属性和方法。

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

12行代码开启屏幕更新。

051-3 隐藏Application对象

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

Sub CopyData_3()

DimmyApp As New Application

DimSh As Worksheet

DimTemp As String

Temp= ThisWorkbook.Path & '\数据表.xls'

myApp.Visible= False

SetSh = myApp.Workbooks.Open(Temp).Sheets(1)

WithSh.Range('A1').CurrentRegion

Range('A1').Resize(.Rows.Count.Columns.Count) = .Value

EndWith

myApp.Quit

SetSh = Nothing

SetmyApp = Nothing

End Sub

代码解析:

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

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

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

7行代码使用Open方法打开“数据表”工作簿(关于Open方法请参阅▲42) ,因为工作簿是使用新创建的、隐藏的Application对象打开的,所以在窗口中是不可视的。

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

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

051-4 使用ExecuteExcel4Macro方法

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

Sub CopyData_4()

DimRCount As Long

DimCCount As Long

DimTemp As String

DimTemp1 As String

DimTemp2 As String

DimTemp3 As String

DimR As Long

DimC As Long

Dimarr() As Variant

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

Temp1= Temp & Rows(1).Address(,,xlR1C1)

Temp1= 'Counta(' & Temp1 & ')'

CCount= Application.ExecuteExcel4Macro(Temp1)

Temp2= Temp & Columns('A').Address(,,xlR1C1)

Temp2= 'Counta(' & Temp2 & ')'

RCount= Application.ExecuteExcel4Macro(Temp2)

ReDimarr(1 To RCount1 To CCount)

ForR = 1 To RCount

For C = 1 To CCount

Temp3 = Temp & Cells(RC).Address(,,xlR1C1)

arr(RC) = Application.ExecuteExcel4Macro(Temp3)

Next

Next

Range('A1').Resize(RCountCCount).Value = arr

End Sub

代码解析:

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

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

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

expression.ExecuteExcel4Macro(String)

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

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

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

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

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

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

051-5 使用SQL连接

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

Sub CopyData_5()

DimSql As String

Dimj As Integer

DimR As Integer

DimCnn As ADODB.Connection

Dimrs As ADODB.Recordset

WithSheet5

.Cells.Clear

Set Cnn = New ADODB.Connection

With Cnn

.Provider = 'microsoft.jet.oledb.4.0'

.ConnectionString = 'Extended Properties=Excel8.0;' _

& 'Data Source=' &ThisWorkbook.Path & '\数据表'

.Open

End With

Set rs = New ADODB.Recordset

Sql = 'select * from [Sheet1$]'

rs.Open SqlCnnadOpenKeysetadLockOptimistic

For j = 0 To rs.Fields.Count - 1

.Cells(1j + 1) = rs.Fields(j).Name

Next

R = .Range('A65536').End(xlUp).Row

.Range('A' & R + 1).CopyFromRecordsetrs

EndWith

rs.Close

Cnn.Close

Setrs = Nothing

SetCnn = Nothing

End Sub

代码解析:

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

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

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

16行到第24行代码查询“数据表”工作簿的全部数据,并复制到工作表中。其中第20行代码将字段名称(标题行)复制到工作表中,第23行代码将查询到的数据记录复制到工作表。

052 返回窗口的可视区域地址

VBA中使用VisibleRange属性返回当前窗口的可视区域,如下面的代码所示。

Sub VbRange()

Dims As String

s =ActiveWindow.VisibleRange.Address(00)

MsgBox'窗口的可视区域为:' & s

End Sub

代码解析:

VbRange过程使用消息框显示当前窗口的可视区域的地址。

应用于当前Window对象的VisibleRange属性返回一个Range对象,代表当前窗口的可视区域。窗口的可视区域就是用户可以在窗口或窗格中看到的单元格区域,如果行或列部分可见,该行或列也包括在可视区域中。

因为VisibleRange属性返回的是一个Range对象,因此可以直接使用该对象的属性和方法。当窗口的大小发生变化时,返回的可视区域的地址也会不同。


    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多
    喜欢该文的人也喜欢 更多

    ×
    ×

    ¥.00

    微信或支付宝扫码支付:

    开通即同意《个图VIP服务协议》

    全部>>