/////////////////////////////////////////////////////////////////////////////////////////////
1、定義Excel操作變量
Dim objExcelFile As Excel.Application
Dim objWorkBook As Excel.Workbook
Dim objImportSheet As Excel.Worksheet
2、打開Excel進(jìn)程，并打開目標(biāo)Excel文件
Set objExcelFile = New Excel.Application
objExcelFile.DisplayAlerts = False
Set objWorkBook = objExcelFile.Workbooks.Open(strFileName)
Set objImportSheet = objWorkBook.Sheets(1)
3、獲取Excel有效區(qū)域的行和列數(shù)
intLastColNum = objImportSheet.UsedRange.Columns.Count
intLastRowNum = objImportSheet.UsedRange.Rows.Count
4、逐行讀取Excel中數(shù)據(jù)
由于前兩行為Header部分，所以需要從第三行讀取
如果第1到第10個(gè)單元格的值均為空或空格，則視為空行
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
若不是空行，則進(jìn)行讀取動(dòng)作，否則繼續(xù)向后遍歷Excel中的行
If blnNullRow = False Then
獲取單元格中的數(shù)據(jù)，做有效性Check，并將合法數(shù)據(jù)創(chuàng)建為實(shí)體存入對(duì)象數(shù)組中
objImportSheet.Cells(intCountI, 1).Value
……
End If
Next intCountI
5、退出Excel進(jìn)程，并關(guān)閉Excel相關(guān)操作對(duì)象
objExcelFile.Quit
Set objWorkBook = Nothing
Set objImportSheet = Nothing
Set objExcelFile = Nothing
另一個(gè)例子
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的話，給你一個(gè)例子參考：
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") '臨時(shí)創(chuàng)建Excel時(shí)使用：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中怎樣實(shí)現(xiàn)將數(shù)據(jù)讀出并在窗體上顯示出來？
Public ExcelTable As Workbook 'excel 工作表對(duì)象
Private TextBoxA() As Control '以編輯框?yàn)榛A(chǔ)動(dòng)態(tài)構(gòu)造數(shù)據(jù)網(wǎng)格
Private FormWidth As Integer '窗體寬度
'以下子程序是用來建立數(shù)據(jù)網(wǎng)格
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) '重新定義動(dòng)態(tài)數(shù)組！
Set TextBoxA(i, j) = a
With TextBoxA(i, j) '設(shè)置文本框?qū)傩?
.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") '建立對(duì)象實(shí)例
ExcelTable.Application.Workbooks.Open (App.Path + "\address.xls") '打開數(shù)據(jù)文檔address.xls
For i = 1 To ExcelTable.Application.Worksheets.Count '獲得工作表數(shù)目
Data = ExcelTable.Application.Worksheets(i).UsedRange.Value '獲取每個(gè)工作表的數(shù)據(jù)
DataType = VarType(Data) '跳過沒有數(shù)據(jù)的空表
Select Case DataType
Case vbArray + vbVariant
Call CreateGrid(i, Data) '傳遞數(shù)據(jù),建立以文本框?yàn)榛A(chǔ)的數(shù)據(jù)網(wǎng)格
Case vbEmpty
End Select
Next
End Sub
----------------------------------------------------------------------------
