当前位置:首页 > 计算机 > 计算机应用 >

VBA读取Excel工作表数据方法探索

分享到:
作者:秩名。 TAGS:方法,探索,数据,工作,读取,引用,
   导读::可以方便的利用VBA读取Excel工作薄中的数据。经常需要引用其它工作薄中的数据。 关键词:VBA,工作薄,数据读取 引言 工作中,使用Excel进行数据处理时,经常需要引用其它工作薄中的数据工作薄,用VBA编程能帮助用户在不打开工作薄或看似不打开工作薄的情况下取得其它工作薄中的数据,下面
有几种方法可以实现,假设当前工作表:“F:\test.xls”,第一个工作表名称为“sheet1”,其它工作薄如:“F:\源数据.xls”,第一个工作表名称为“销售数据”。读取引用工作薄的工作表数据范围A1:E20。在当前工作表中利用“控件工具箱”,制作命令按钮名称和下面过程名对应。
  1 使用公式引用工作簿中的工作表数据
  Private Sub btn1 ()
  Dim wshpath As String
  wshpath="'" &ThisWorkbook.Path &"\[源数据.xls]销售数据'!"
  With Sheet1.Range("A1:E20")'激活当前工作表A1:E20区域
  .FormulaR1C1="=" &wshpath &"RC"'在A1:E20区域写入对其它工作表单元格引用
  .Value=.Value'将该区域的公式转换为数值
  End With
  End Sub
  此方法适合应用于需要引用的数据不是太多的情况中心。
  2 使用Excel对象的方法引用工作簿中的工作表数据
  Private Sub btn2()
  Dim FilePath As String
  Dim i,j As Long
  Dim sBrow,sErow,sBCol,sEcol As Long'源表的行数,列数
  Dim tBrow,tBcol As Long '设置当前工作表开始行、列数
  sBrow=1
  sErow=20
  sBCol=1
  sEcol=5
  tBrow=1
  tBcol=1
  FilePath=ThisWorkbook.Path &"\源数据.xls"
  Set xlapp=CreateObject("Excel.Application")'创建EXCEL对象
  Set xlbook=xlapp.Workbooks.Open(FilePath)'创建工件簿对象
  Set xlsheet=xlbook.Worksheets(1)'创建工作表对象
  xlapp.Visible=False
  '以下将数据复制到当前工作表中
  For i=sBrow To sErow
  For j=sBCol To sEcol
  ThisWorkbook.Sheets(1).Cells(tBrow+i-1,tBcol+j-1)=xlsheet.Cells(i,j)
  Next
  Next
  '关闭对象代码省略
  End Sub
  该方法编程应用灵活,较为简单工作薄,可以修改添加代码对其它多个工作薄进行读取数据。
  3 使用ExecuteExcel4Macro方法引用工作簿中的工作表数据
  Private Sub btn3()
  Dim FilePath As String
  Dim i,j,sBrow,sErow,sBCol,sEcol,tBrow,tBcol As Long
[论文网 lunwen.nangxue.com]   sBrow=1
  sErow=20
  sBCol=1
  sEcol=5
  tBrow=1
  tBcol=1
  FilePath=ThisWorkbook.Path &"\[源数据.xls]"
  For i=sBrow To sErow
  For j=sBCol To sEcol
  Sheets(1).Cells(tBrow+i-1,tBcol+j-1)=ExecuteExcel4Macro("'"&FilePath &"报名表" &"'!r" & i& "c" &j)
  Next
  Next
  End Sub
  4 使用ADO的方式引用工作簿中的工作表数据。
  在编写代码之前打开Visual Basic编辑器,单击“工具”菜单->“引用”命令,在打开的引用对话框中,选中“MicrosoftActiveX Data Objects 2.0 Library”,点击确定,就可利用ADO来读取Excel工作薄中数据。
  Private Sub btn4()
  Dim Sql As String
  Dim i,nR As Integer
  Dim Cnn As ADODB.Connection
  Dim rs As ADODB.Recordset
  With Sheet1
  .Cells.Clear
  Set Cnn=New ADODB.Connection
  '以下用ADO连接并打开被应用工作薄
  With Cnn
  .Provider="microsoft.jet.oledb.4.0"
  .ConnectionString="Extended Properties=Excel 8.0;" &"Data Source=" &ThisWorkbook.Path &"\源数据"
  .Open
  End With
  '以下筛选表中数据生成记录集,第一行默认为字段名
  Set rs=New ADODB.Recordset
  Sql="select * from [销售数据$]"
  rs.Open Sql,Cnn,adOpenKeyset,adLockOptimistic
  '以下将字段名放入首行,将数据写入工作表
  For i=0 To rs.Fields.Count-1
  .Cells(1,i+1)=rs.Fields(i).Name
  Next
  nR=.Range("A65536").End(xlUp).Row
  .Range("A" &nR+1).CopyFromRecordset rs
  End With
  rs.Close
  Cnn.Close
  Set rs=Nothing
  Set Cnn=Nothing
  End Sub
  结束语:
  通过以上几种方法工作薄,可以方便的利用VBA读取Excel工作薄中的数据,其中一、二种方法在打开Excel工作薄时使其隐藏而使用户看不见,三、四种方法真正意义上没有打开工作薄而读取其数据。在实际VBA应用中,需要读取更多的工作薄,用户则可添加对文件夹及文件的操作代码完成,从而提高工作效率。

参考文献:
[1]John Walkenbach著.盖江南,王勇等译.Excel 2003高级VBA编程宝典[M].电子工业出版社,2005.
[2]Excel Home.Excel应用大全[M].人民邮电出版社,2008.