在VB中操作Excel的问题

初学者请问：vb操作excel的有关问题~

Private Sub Command3_Click() Dim x As Excel.Application Dim xbook As Excel.Workbook Dim xsheet As Excel.Worksheet Set x = CreateObject("excel.application") s_path = App.Path & "est1.xls" Set xbook = x.Workbooks.Open(s_path) S.

下面是我刚刚在计算机里测试的代码，你自己对比一下，看看步骤是否一致！

操作系统WINDOWS XP，VB6，Excel2003，似乎你的问题与版本无关。

Option Explicit

Private Sub Command1_Click()
Dim xlsApp As Excel.Application '声明Excel对象变量
Dim xlsBook As Excel.Workbook '声明工作簿对象变量
Dim xlsSheet As Excel.Worksheet '声明工作表对象变量

'******************** 创建EXCEL对象实例、打开EXCEL文件，并创建工作簿实例的 方法一 ********************
Set xlsApp = CreateObject("Excel.Application") '创建EXCEL对象实例
Set xlsBook = xlsApp.Workbooks.Open(App.Path & "\实验1.xls") '设置工作簿对象并打开已经存在的工作簿EXCEL文件
'**********************************************************************************************************

xlsApp.Visible = False '设置EXCEL对象不可见（或True为可见）
Set xlsSheet = xlsBook.Worksheets("Sheet1") '设置工作表对象为打开的工作簿中的表“Sheet1”

'引用单元格方法1
xlsSheet.Cells(1, 1) = 999 '给“Sheet1”表的第1行第1格即“A1”单元格赋值999
xlsSheet.Cells(2, 1) = "你好！" '给“Sheet1”表的第1行第1格即“A2”单元格赋值"你好！"
xlsSheet.Cells(3, 2) = "EXCEL" '给“Sheet1”表的第1行第1格即“B3”单元格赋值"EXCEL"

'引用单元格方法2
Dim Str As String
Dim d As Date
Str = "欢迎使用EXCEL VBA"
d = #8/28/2012#
xlsSheet.Range("C1").Value = Str '用变量给“Sheet1”表的“C1”单元格赋值
xlsSheet.Range("C2").Value = d '用变量给“Sheet1”表的“C2”单元格赋值 日期类型

'读取单元格数据
Dim x As Integer
Dim s As String
Dim d1 As Date
x = xlsSheet.Cells(1, 1).Value
s = xlsSheet.Range("C1").Value
d1 = xlsSheet.Range("C2").Value
MsgBox x, , "VBA 实例"
MsgBox s, , "VBA 实例"
MsgBox d1, , "VBA 实例"

'单元格之间直接完成赋值
xlsSheet.Range("A5").Value = xlsSheet.Range("A1").Value
xlsSheet.Range("A10").Value = xlsSheet.Range("A1").Value + 200000

xlsBook.Close (True) '关闭工作簿并保存所做的修改 (False)则放弃修改，xlsBook.Close将会提问
xlsApp.Quit '关闭EXCEL对象
Set xlsApp = Nothing '释放xlApp对象

End Sub

把以下代码放入代码窗口中运行即可：
Option Explicit
Dim mxlApp As Excel.Application
Dim mxlBook As Excel.Workbook
Dim mxlSheet As Object
Dim H As Integer
Private Sub Command1_Click() '下一行
If H < 4 Then
H = H + 1
Text1.Text = mxlSheet.Cells(H, 1)
Text2.Text = mxlSheet.Cells(H, 2)
Text3.Text = mxlSheet.Cells(H, 3)
Text4.Text = mxlSheet.Cells(H, 4)
Else
MsgBox "已经是最后一行！"
End If
End Sub

Private Sub Command2_Click() '上一行
If H > 1 Then
H = H - 1
Text1.Text = mxlSheet.Cells(H, 1)
Text2.Text = mxlSheet.Cells(H, 2)
Text3.Text = mxlSheet.Cells(H, 3)
Text4.Text = mxlSheet.Cells(H, 4)
Else
MsgBox "已经是第一行！"
End If

End Sub

Private Sub Form_Load()
'先在工程中引用Microsoft Excel 11.0 Object Library
Dim R As String, i As Integer
Dim sSource, sDestination As String
Set mxlApp = New Excel.Application
Set mxlApp = CreateObject("Excel.Application")
R = "c:\1.xls"
H = 1
mxlApp.Visible = False
Set mxlBook = mxlApp.Workbooks.Open(R)
Set mxlSheet = mxlBook.Worksheets(1)
Text1.Text = mxlSheet.Cells(H, 1)
Text2.Text = mxlSheet.Cells(H, 2)
Text3.Text = mxlSheet.Cells(H, 3)
Text4.Text = mxlSheet.Cells(H, 4)

End Sub

Private Sub Form_Terminate() '程序退出时，关闭Excel
mxlBook.Application.Quit
End Sub

这个很容易的..当初我把数据库里面资料放入EXCEL里面去打印...

只是不记得代码了...原谅!! ^_^

几天内要是还没有高手帮你解决..我回去帮你把代码拷过来吧...

帮你写了下，建好工程后要先引用Microsoft Excel 11.0 Object Library
,然后输入下面代码，我这里用text5作了个指针的功能。还有就是我这里excel的默认范围是(a1:d4)
Private Sub CommandButton1_Click()'导入excel
text5.caption="0"
dim xl,xlbook,xlsheet as object
set xl = CreateObject("Excel.Application")
set xlbook= objExcel.Workbooks.Open("你存的excel路径名")
xl.Visible = true'这里可以改成false 让其不显示
set xlsheet = objExcel.Worksheets("sheet1")'以上是打开excel文件
end sub

Private Sub CommandButton2_Click()'下一条的作用
n=val(text5)'这里设置一个text5，相当一个指针的作用,开始时把
'text5.caption设置成0
n=n+1
if n>4 then
msgbox "已经为最后行"
exit sub
else
text1.text=xlsheet.cells(n,1)
text2.text=xlsheet.cells(n,2)
text3.text=xlsheet.cells(n,3)
text4.text=xlsheet.cells(n,4)'我默认的你的excel的范围是(A1:D4)的范围
text5.caption=n
end if
end sub

Private Sub CommandButton3_Click()'上一条的作用
n=val(text5)'这里设置一个text5，相当一个指针的作用,开始时把
'text5.caption设置成0
n=n-1
if n<0 then
msgbox "已经为第一行"
exit sub
else
text1.text=xlsheet.cells(n,1)
text2.text=xlsheet.cells(n,2)
text3.text=xlsheet.cells(n,3)
text4.text=xlsheet.cells(n,4)'我默认的你的excel的范围是(A1:D4)的范围
text5.caption=n
end if
end sub

'添加引用：microsoft excel 11.0 object library
Dim xlsApp As Excel.Application
Dim xlsBook1 As Excel.Workbook
Dim xlsSheet1 As Excel.Worksheet
Dim i As Integer
Dim fileName As String

Private Sub Command1_Click()
'上一个
If i >= 2 Then
i = i - 1
If i = 2 Then
Command1.Enabled = False
End If
Text1.Text = xlsSheet1.Cells(i, 1)
Text2.Text = xlsSheet1.Cells(i, 2)
Text3.Text = xlsSheet1.Cells(i, 3)
Text4.Text = xlsSheet1.Cells(i, 4)
End If
Command2.Enabled = True
End Sub

Private Sub Command2_Click()
'下一个
i = i + 1
Text1.Text = xlsSheet1.Cells(i, 1)
Text2.Text = xlsSheet1.Cells(i, 2)
Text3.Text = xlsSheet1.Cells(i, 3)
Text4.Text = xlsSheet1.Cells(i, 4)

If xlsSheet1.Cells(i + 1, 1) = "" Then
Command2.Enabled = False
End If

Command1.Enabled = True
End Sub

Private Sub Form_Load()
fileName = "C:\Documents and Settings\Administrator\桌面\新建文件夹\test.xls"
Set xlsApp = New Excel.Application
Set xlsBook1 = xlsApp.Workbooks.Open(fileName)
Set xlsSheet1 = xlsApp.Worksheets("sheet1")

'默认显示第一条记录
Text1.Text = xlsSheet1.Cells(2, 1)
Text2.Text = xlsSheet1.Cells(2, 2)
Text3.Text = xlsSheet1.Cells(2, 3)
Text4.Text = xlsSheet1.Cells(2, 4)
i = 2
Command1.Enabled = False
End Sub

Private Sub Form_Unload(Cancel As Integer)
'xlsApp.Quit
xlsApp.Application.Quit '这条语句和上边一条语句的区别是什么
Set xlsApp = Nothing
End Sub

Text1.Text = mxlSheet.Cells(i, 1)
......

---

在VB中操作Excel的问题视频