vb读取excel内容

2009-09-22 08:58
引自：http://zhidao.baidu.com/question/91945073.html
未测试
/////////////////////////////////////////////////////////////////////////////////////////////
VB定义Excel操作变量-未测试
引自：http://blog.163.com/taihang2005@126/blog/static/76387215200872994154837/
1、定义Excel操作变量
Dim objExcelFile As Excel.Application
Dim objWorkBook As Excel.Workbook
Dim objImportSheet As Excel.Worksheet
2、打开Excel进程，并打开目标Excel文件
Set objExcelFile = New Excel.Application
objExcelFile.DisplayAlerts = False
Set objWorkBook = objExcelFile.Workbooks.Open(strFileName)
Set objImportSheet = objWorkBook.Sheets(1)
3、获取Excel有效区域的行和列数
intLastColNum = objImportSheet.UsedRange.Columns.Count
intLastRowNum = objImportSheet.UsedRange.Rows.Count
4、逐行读取Excel中数据
由于前两行为Header部分，所以需要从第三行读取
如果第1到第10个单元格的值均为空或空格，则视为空行
For intCountI = 3 To intLastRowNum
''Check if Empty Data Row
blnNullRow = True
For intI = 1 To 10
If Trim$(objImportSheet.Cells(intCountI, intI).Value) <> "" Then
blnNullRow = False
End If
Next intI
若不是空行，则进行读取动作，否则继续向后遍历Excel中的行
If blnNullRow = False Then
获取单元格中的数据，做有效性Check，并将合法数据创建为实体存入对象数组中
objImportSheet.Cells(intCountI, 1).Value
……
End If
Next intCountI
5、退出Excel进程，并关闭Excel相关操作对象
objExcelFile.Quit
Set objWorkBook = Nothing
Set objImportSheet = Nothing
Set objExcelFile = Nothing
另一个例子
Option Explicit
Dim xlExcel As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim er As Excel.Range
Dim AppExcel As Object
Private Sub Command1_Click()
On Error GoTo Errhandler
CommonDialog1.Filter = "Excel(*.xls) |*.xls |AllFile(*.*) |*.*"
CommonDialog1.FilterIndex = 1
CommonDialog1.ShowOpen
Set xlExcel = CreateObject("Excel.Application")
xlExcel.Workbooks.Open
CommonDialog1.FileName
Set xlBook = xlExcel.Workbooks(CommonDialog1.FileTitle)
For Each xlSheet In xlBook.Worksheets List1.AddItem xlSheet.Name Next
Text2.Text = xlBook.Worksheets.Count Errhandler:
Exit Sub
End Sub
Private Sub List1_Click()
xlBook.Sheets(List1.List(List1.ListIndex)).Select
' xlBook.Worksheets(List1.ListIndex ) Text1.Text = xlBook.Worksheets(List1.List(List1.ListIndex)).Cells(1, 1)    'xlBook.xlSheet.Cells(1, 1).Value
xlBook.Save
xlBook.Close
xlExcel.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlExcel = Nothing
End Sub
----------------------------------------------------------------
如果你指的是VB，而非VBA的话，给你一个例子参考：
Private Sub Command1_Click()
Set xlsApp = Excel.Application
With xlsApp
.Visible = True
.Workbooks.Open ("c:\temp.xls")
x = .Workbooks("temp").Sheets("Sheet1").Range("A1").Value
MsgBox x
End With
xlsApp.Workbooks("temp").Close
xlsApp.Quit
Set xlsApp = Nothing
End Sub
-----------------------------------------------------------------
Private Sub Command1_Click()
Dim objExcel As Excel.Application
Dim objWorkBook As Excel.Workbook
Set objExcel = CreateObject("Excel.Application")
Set objWorkBook = objExcel.Workbooks.Open("d:\aa.xls") '临时创建Excel时使用：Set objWorkBook = objExcel.Workbooks.Add()
objExcel.Visible = False
Dim objSheet As Excel.Worksheet
Set objSheet = objExcel.Worksheets("sheet1")
Dim uu(2 To 9) As Integer
For i = 3 To 10
uu(i - 1) = Worksheets("sheet1").Cells(i, 1).Value
Next i
'objWorkBook.Save
objWorkBook.Close
objExcel.Quit
Set objSheet = Nothing
Set objWorkBook = Nothing
Set objExcel = Nothing
End Sub
------------------------------------------------------------
有一张EXCEL表格，在VB中怎样实现将数据读出并在窗体上显示出来？
Public ExcelTable As Workbook 'excel 工作表对象
Private TextBoxA() As Control '以编辑框为基础动态构造数据网格
Private FormWidth As Integer '窗体宽度
'以下子程序是用来建立数据网格
Sub CreateGrid(No, Data)
For i = 1 To CInt(UBound(Data, 1))
For j = 1 To CInt(UBound(Data, 2))
Set a = Form1.Controls.Add("VB.TextBox", "textbox" & CStr(i) & CStr(j) & CStr(No))
ReDim TextBoxA(1 To i, 1 To j) '重新定义动态数组！
Set TextBoxA(i, j) = a
With TextBoxA(i, j) '设置文本框属性
.Text = Data(i, j)
.Visible = True
.Height = 200
.Width = 500
.Top = .Height * (i - 1)
.Left = .Width * (j - 1) + FormWidth
End With
Next
Next
End Sub
Private Sub Command1_Click()
Set ExcelTable = CreateObject("Excel.sheet") '建立对象实例
ExcelTable.Application.Workbooks.Open (App.Path + "\address.xls") '打开数据文档address.xls
For i = 1 To ExcelTable.Application.Worksheets.Count '获得工作表数目
Data = ExcelTable.Application.Worksheets(i).UsedRange.Value '获取每个工作表的数据
DataType = VarType(Data) '跳过没有数据的空表
Select Case DataType
Case vbArray + vbVariant
Call CreateGrid(i, Data) '传递数据,建立以文本框为基础的数据网格
Case vbEmpty
End Select
Next
End Sub
----------------------------------------------------------------------------