excel.frm

这是一个通过VisualBasic编写的操作Excel数据库的小程序！ 其中包含有源代码和可执行文件！

VERSION 5.00
Begin VB.Form Form1 
   Caption         =   "Excel操作"
   ClientHeight    =   1080
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   6900
   LinkTopic       =   "Form1"
   ScaleHeight     =   1080
   ScaleWidth      =   6900
   StartUpPosition =   3  '窗口缺省
   Begin VB.CommandButton Command4 
      Caption         =   "打开文件"
      Height          =   495
      Left            =   5280
      TabIndex        =   3
      Top             =   240
      Width           =   1215
   End
   Begin VB.CommandButton Command3 
      Caption         =   "读取Excel"
      Height          =   495
      Left            =   3600
      TabIndex        =   2
      Top             =   240
      Width           =   1215
   End
   Begin VB.CommandButton Command2 
      Caption         =   "写入Excel"
      Height          =   495
      Left            =   1920
      TabIndex        =   1
      Top             =   240
      Width           =   1215
   End
   Begin VB.CommandButton Command1 
      Caption         =   "创建Excel"
      Height          =   495
      Left            =   240
      TabIndex        =   0
      Top             =   240
      Width           =   1215
   End
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Private Sub Command1_Click()
Dim VBExcel As Excel.Application
Set VBExcel = CreateObject("Excel.Application")
    With VBExcel
         .Workbooks.Add
            With ActiveWorkbook
              .SaveAs "D:\Documents and Settings\Administrator\桌面\Temp\1234.XLS"
              '.SaveAs "temp\123.xls"
              .Close
            End With
         .Quit
     End With
Set VBExcel = Nothing
MsgBox "创建成功！"
End Sub

Private Sub Command2_Click()
Dim xlApp As New Excel.Application '定义并创建EXCEL对象
Dim xlBook As Excel.Workbook '创建工作簿

'xlApp.Visible = True '让Excel可见

Set xlBook = xlApp.Workbooks.Open("D:\Documents and Settings\Administrator\桌面\Temp\1234.XLS") '打开Excel模板
With xlBook.ActiveSheet
.Cells(1, 1) = 1231 '填充第一行第一列的值
.Cells(5, 6) = "我爱你！"
.Cells(3, 2) = "哈哈"
.Cells(3, 3) = "哈哈"
.Cells(2, 2) = "这是什么"
'...... 填充其他行列的值，具体的填充位置得根据模板来做
End With
' xlBook.PrintPreview '打印预览

'给工作表设置保护，让它只能看，不能编辑
'ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=1234

'撤消保护：
'ActiveSheet.Unprotect Password:=1234
'xlBook.SaveAs "c:\rlt.xls" '将模板另存，以防模板被破坏
xlBook.Save
xlBook.Close False '关闭工作簿
xlApp.DisplayAlerts = False
xlApp.Quit '关闭Excel
Set xlBook = Nothing
Set xlApp = Nothing
MsgBox "写入成功！"
End Sub


Private Sub Command3_Click()
Dim adoCnn As New ADODB.Connection
  Dim adoRst As New ADODB.Recordset
  Dim j As Integer
  adoCnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=D:\Documents and Settings\Administrator\桌面\Temp\1234.XLS;Extended Properties='Excel 8.0;HDR=no'"
 adoRst.Open "Select * from [Sheet1$]", adoCnn, adOpenStatic
 adoRst.MoveFirst
 For j = 0 To adoRst.RecordCount - 1 'j=1 是从excel的第二行读起
          MsgBox "现在是第" & adoRst.AbsolutePosition & "行开始！"
          MsgBox adoRst.Fields(0).Value & "Null"              '当j=1时，是读取第一列的数据
          MsgBox adoRst.Fields(1).Value & "Null"
'           adoRst.Fields(1).Value = "Hello"
          MsgBox adoRst.Fields(2).Value & "Null"
          MsgBox adoRst.Fields(3).Value & "Null"
          MsgBox adoRst.Fields(4).Value & "Null"
         MsgBox adoRst.Fields(5).Value & "Null"
'         MsgBox adoRst.Fields(6).Value & "Null"
'          MsgBox Rs_db.Fields(7).Value & "Null"
       MsgBox "现在是第" & adoRst.AbsolutePosition & "行结束！"
          adoRst.MoveNext
      Next j
 MsgBox "共有" & adoRst.RecordCount & "条记录！"
End Sub

Private Sub Command4_Click()
'shellexecute 0
ShellExecute 0, "open", "D:\Documents and Settings\Administrator\桌面\Temp\1234.XLS", vbNullString, vbNullString, 3
'ShellExecute 0, "open", "help/index.htm", vbNullString, vbNullString, 3
End Sub