怎样使用VB查询EXCEL中单元格的内容？

分类: 电脑/网络 >>程序设计 >>其他编程语言

问题描述:

我已通过VB打开了一个excel表格，我想查找表格中单元格内的一些内容，然后还想得到有该内容的地址（行，列），请帮忙解答，不胜感激！

解析:

Option Explicit

Dim xlApp As Excel.Application '定义EXCEL类

Dim xlBook As Excel.Workbook '定义工件簿类

Dim xlsheet As Excel.Worksheet '定义工作表类

Dim Tex As String

Dim ln As String

Private Sub Command3_Click()

End

End Sub

Private Sub Command4_Click()

Dim x As Integer

CD.ShowOpen

List2.MousePointer = 11

Open CD.Filename For Input As 1

Do Until EOF(1)

Line Input #1, ln

Tex = Tex &ln &vbCrLf

Loop

'判断EXCEL是否打开

Set xlApp = CreateObject("Excel.Application") '创建EXCEL应用类

xlApp.Visible = False '设置EXCEL可见

Set xlBook = xlApp.Workbooks.Open(CD.Filename) '打开EXCEL工作簿

Set xlsheet = xlBook.Worksheets(1) '打开EXCEL工作表

xlsheet.Activate '激活工作表

xlBook.RunAutoMacros (xlAutoOpen) '运行EXCEL中的启动宏

For x = 1 To 4

List2.AddItem xlsheet.Cells(1, x) '给单元格1行驶列赋值

Next x

Close 1

List2.MousePointer = 0

Tex = ""

End Sub

代码是实现对excel *** 作的excel.application

还有如

Excel.Application

Set xlapp = CreateObject("Excel.Application") '激活EXCEL

xlapp.Visible = True '让EXCEL可见

Set xlbook = xlapp.Workbooks.Open(App.Path + "个人简历.xlt")

'打开工作簿，strDestination为一个EXCEL报表文件

Set xlsheet = xlbook.Worksheets(1) '设定工作表

这个有点像是vb6的、vb也有相应的方法的.总之是

excel.application 用到这个就可以了.必要的话导入excel的dll文件.

这样你就发现、可以用vb来对excel来 *** 作.你可以查下微软的msdn、有具体的属性方法.

如:

Dim ef As New Excel.Application

Dim es As New Excel.Worksheet

Dim er As Excel.Range

ef.Workbooks.Add()

es = ef.Worksheets("sheet1")

es.Activate()

er = es.Range("a1:C1")

er.Select()

er.Merge()

er.Value = "gordon1117" ef.Visible = True 目前应用了三个对象:application,worksheet和range,三者的关系应该清晰,做到这点就能从基本开始做了:)格式设置都在range对象,只不过在设置对齐属性上是verticalalignment和horizon

range 用来定位、

然后你可以循环把数据输出到指定了格式的excel文件里了吧、其实连格式都是可以程序控制的好好学下吧.

可以参考一下这篇文章

blog.csdn/hawordcheng/archive/2006/10/26/1351931x

n Explicit

Private objSWbemServices As SWbemServices '

Private WithEvents DeleteProcessEvent As SWbemSink '进程结束监控

Dim xlApp As Excel.Application '定义EXCEL类

Dim xlBook As Excel.Workbook '定义工件簿类

Dim xlsheet As Excel.Worksheet '定义工作表类

Private Sub Initial(ss As String)

Dim i, j As Integer

Set xlApp = CreateObject("Excel.Application") '创建EXCEL应用类

'xlApp.Visible = True '设置EXCEL可见

'Set xlBook = xlApp.Workbooks.Open(App.Path + "\" &"106-33-LCDTest-20130202.csv") '打开EXCEL工作簿

Set xlBook = xlApp.Workbooks.Open(App.Path + "\" &ss) '打开EXCEL工作簿

Set xlsheet = xlBook.Worksheets(1) '打开EXCEL工作表

xlsheet.Activate '激活工作表

'-----正式统计数量

Text1.Text = xlsheet.UsedRange.Cells.Rows.Count

sun = xlsheet.UsedRange.Cells.Rows.Count

For i = 2 To xlsheet.UsedRange.Cells.Rows.Count - 1

If Format(xlsheet.Cells(i, 3), "mm/dd") = Format(Now, "mm/dd") Then '统计今天的数据

Select Case Format(xlsheet.Cells(i, 3), "hh")

Case 0, 1 '0-1点钟数据统计

If xlsheet.Cells(i, 2) = "PASS" Then

Select Case xlsheet.Cells(i, 6)

Case 1

u(1, 1) = u(1, 1) + 1

Case 2

u(3, 1) = u(3, 1) + 1

Case 3

u(5, 1) = u(5, 1) + 1

Case 4

u(7, 1) = u(7, 1) + 1

Case Else

MsgBox "Nest异常", vbCritical, "消息提示:"

End Select

Else

。。。。。。。。。。。。。。。。。。。。。。。。

Else

Select Case xlsheet.Cells(i, 6)

Case 1

u(2, 12) = u(2, 12) + 1

Case 2

u(4, 12) = u(4, 12) + 1

Case 3

u(6, 12) = u(6, 12) + 1

Case 4

u(8, 12) = u(8, 12) + 1

Case Else

MsgBox "Nest异常", vbCritical, "消息提示:"

End Select

j = j + 1

End If

Case Else

'MsgBox "时间格式异常", vbCritical, "消息提示:"

End Select

End If

Next i

'-----------------------------------------------------------

'xlBook.Save '保存文件

xlBook.Close (True) '关闭工作簿

xlApp.Quit '结束Excel对象

Set xlApp = Nothing '释放xlApp对象

'MsgBox "初始化完成", vbCritical, "消息提示:OK"

Text2.Text = Format(j / Text1.Text, "0.00%")

End Sub

'首先要在VB中 引用 EXCEL

'在FORM 中 绘制 textbox ×2 commandbutton × 1

'先打开 EXCEL 第一列输入 查询字段 第二列 输入 结果内容

'先点击COMMAND1 连接EXCEL

'在TEXT1中 输入查询字段 点击COMMAND2 TEXT2中显示结果

'在VB6 EXCEL 2003测试通过

'如有疑问 请留言

Option Explicit

Dim I, J, K, L As Long

Dim ROW_COUNT, COL_COUNT As Long

Dim EXCEL_APP As Excel.Application '声明EXCEL对象

Private Sub Command1_Click()

Set EXCEL_APP = GetObject(, "Excel.Application") '连接EXCEL

End Sub

Private Sub Command2_Click()

ROW_COUNT = EXCEL_APP.ActiveSheet.UsedRange.Rows.Count '查询有多少行数据被使用，以获得最小的查询范围

EXCEL_APP.ActiveCell.Offset(1 - EXCEL_APP.ActiveCell.Row, 1 - EXCEL_APP.ActiveCell.Column).Select '定位于 左上角

Text2.Text = ""

For I = 1 To ROW_COUNT

If EXCEL_APP.Cells(I, 1).Value = Text1.Text Then

Text2.Text = EXCEL_APP.Cells(I, 2).Value

I = 100 + ROW_COUNT

End If

Next I

End Sub

---