VB中有如下控件：Text1;Command1;Label1。在桌面上有一个为1.xlsx的工作表，工作表的A列为编号，B列为内容。要求当单机Command1时如果Text1与A列的编号相同则在Label1上显示编号对应的内容。
要使用VB操作EXCEL，必须引用EXCEL对象，方法与步骤如下：
'首先通过VB的菜单 [工程] -- [引用]，勾选：Microsoft Excel XX.X Object Library
'其中XX.X，要看你计算机安装的OFFICE版本而定
Private Sub Command1_Click()
Dim xlsApp As Excel.Application '声明对象变量
Set xlsApp = Excel.Application '实例化对象
xlsApp.Visible = False '使Excel隐藏不可见
xlsApp.Workbooks.Open ("C:\1.xls") '打开EXCEL文件，假设文件在c盘根目录
Dim i As Long
i = 1
Do While xlsApp.ActiveWorkbook.Sheets("sheet1").Cells(i, 1) <> ""
If xlsApp.ActiveWorkbook.Sheets("sheet1").Cells(i, 1).Value = Text1.Text Then
Label1.Caption = xlsApp.ActiveWorkbook.Sheets("sheet1").Cells(i, 2).Value
Exit Do
End If
i = i + 1
Loop
xlsApp.ActiveWorkbook.Close savechanges:=False '关闭活动工作簿同时不保存对它的更改。
xlsApp.Quit '关闭EXCELL
Set xlsApp = Nothing '释放资源
End Sub
'首先通过VB的菜单 [工程] -- [引用]，勾选：Microsoft Excel XX.X Object Library
'其中XX.X，要看你计算机安装的OFFICE版本而定
Private Sub Command1_Click()
Dim xlsApp As Excel.Application '声明对象变量
Set xlsApp = Excel.Application '实例化对象
xlsApp.Visible = False '使Excel隐藏不可见
xlsApp.Workbooks.Open ("C:\1.xls") '打开EXCEL文件，假设文件在c盘根目录
Dim i As Long
i = 1
Do While xlsApp.ActiveWorkbook.Sheets("sheet1").Cells(i, 1) <> ""
If xlsApp.ActiveWorkbook.Sheets("sheet1").Cells(i, 1).Value = Text1.Text Then
Label1.Caption = xlsApp.ActiveWorkbook.Sheets("sheet1").Cells(i, 2).Value
Exit Do
End If
i = i + 1
Loop
xlsApp.ActiveWorkbook.Close savechanges:=False '关闭活动工作簿同时不保存对它的更改。
xlsApp.Quit '关闭EXCELL
Set xlsApp = Nothing '释放资源
End Sub
温馨提示：答案为网友推荐，仅供参考
第1个回答 2012-08-16
我把1.xls放d盘了，文件名根据你的自己改，另外工程要引入Microsoft Excel xx.0 Object Library
以下是代码：
Dim xlsApp As Excel.Application
Dim xlsWorkbook As Excel.Workbook
Dim xlssheet As Excel.Worksheet
Private Sub Command1_Click()
Dim temp As String
Dim rowNum As Integer
rowNum = 1
temp = xlssheet.Cells(rowNum, 1).Value
While temp <> ""
If temp = Text1.Text Then
Label1.Caption = xlssheet.Cells(rowNum, 2).Value
Exit Sub
End If
rowNum = rowNum + 1
temp = xlssheet.Cells(rowNum, 1).Value
Wend
Label1.Caption = "没有找到"
End Sub
Private Sub Form_Load()
Set xlsApp = CreateObject("Excel.Application")
Set xlsWorkbook = xlsApp.Workbooks.Open("D:\1.xls")
xlsApp.DisplayAlerts = False
Set xlssheet = xlsWorkbook.Sheets(1)
End Sub
Private Sub Form_Unload(Cancel As Integer)
xlsWorkbook.Close
Set xlssheet = Nothing
Set xlsWorkbook = Nothing
Set xlsApp = Nothing
End Sub
以下是代码：
Dim xlsApp As Excel.Application
Dim xlsWorkbook As Excel.Workbook
Dim xlssheet As Excel.Worksheet
Private Sub Command1_Click()
Dim temp As String
Dim rowNum As Integer
rowNum = 1
temp = xlssheet.Cells(rowNum, 1).Value
While temp <> ""
If temp = Text1.Text Then
Label1.Caption = xlssheet.Cells(rowNum, 2).Value
Exit Sub
End If
rowNum = rowNum + 1
temp = xlssheet.Cells(rowNum, 1).Value
Wend
Label1.Caption = "没有找到"
End Sub
Private Sub Form_Load()
Set xlsApp = CreateObject("Excel.Application")
Set xlsWorkbook = xlsApp.Workbooks.Open("D:\1.xls")
xlsApp.DisplayAlerts = False
Set xlssheet = xlsWorkbook.Sheets(1)
End Sub
Private Sub Form_Unload(Cancel As Integer)
xlsWorkbook.Close
Set xlssheet = Nothing
Set xlsWorkbook = Nothing
Set xlsApp = Nothing
End Sub
第2个回答 2012-08-16
Dim Excelapp As Excel.Application
Set Excelapp = New Excel.Application
i=1
do while Excelapp.ActiveSheet.Cells(1, i) <>""
if Excelapp.ActiveSheet.Cells(1, i) =text1.text then label1.caption= Excelapp.ActiveSheet.Cells(2, i)
i=i+1
loop本回答被网友采纳
Set Excelapp = New Excel.Application
i=1
do while Excelapp.ActiveSheet.Cells(1, i) <>""
if Excelapp.ActiveSheet.Cells(1, i) =text1.text then label1.caption= Excelapp.ActiveSheet.Cells(2, i)
i=i+1
loop本回答被网友采纳
第3个回答 2012-08-16
哇。这个创意不错！
