excel导入VB

引自：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

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

----------------------------------------------------------------------------