返回目录：excel表格制作
range能选中多个格子
cell是其中一个格子
使用cell要注意数据类型等问题
Private Sub Command2_Click()
Dim objExcel As Excel.Application
Dim objWorkBook As Excel.Workbook
Set objExcel = CreateObject("Excel.Application")
Set objWorkBook = objExcel.Workbooks.Add()
objExcel.Visible = True
'设置活动工作表
Dim objSheet As Excel.Worksheet
Set objSheet = objExcel.Worksheets("sheet1")
objSheet.Cells(1, 1) = "mather" '对单元格赋值
objWorkBook.SaveAs "D:\aa.xls" '保存
objWorkBook.Close
objExcel.Quit
Set objSheet = Nothing
Set objWorkBook = Nothing
Set objExcel = Nothing
End Sub
补充：e799bee5baa6e58685e5aeb9336 在“工程”-引用 -中要选择 Microsoft Excel 11.0【或其他数值】 Object Library
因为ADO不支持Excel的删除
所以用Excel+access的方法解决
新建access,在access里面添加2个表,一个"在职"表,一个"离职"表,(都将工号设置主键)
Excel按Ctrl+F11打开vbe编辑器,插入窗体
按以下步骤建立对应控件
最重要的一点就是将文本框的名称属性依次改成"工号", "姓名", "部门", "二级小组", "三组小组",因为代码里要用到
建立好之后e5a48de588b67a64336 双击窗体将以下代码复制到代码窗口中
Dim Con As Object
Dim Rst As Object
Dim Sql As String
Dim FieldArr
Const ProvidSr$ = "provider=microsoft.jet.oledb.4.0;data source="
Private Sub CommandButton1_Click()
Dim FieldSr$, ValueSr$, x%
If 工号.Text = "" Then MsgBox "工号必填": Exit Sub
For x = 0 To 4
FieldSr = FieldSr & FieldArr(x) & ", "
ValueSr = ValueSr & Me.Controls(FieldArr(x)).Text & "', '"
Next
FieldSr = Left(FieldSr, Len(FieldSr) - 2)
ValueSr = Left(ValueSr, Len(ValueSr) - 3)
Sql = "Insert into 在职 (" & FieldSr & ") VALUES('" & ValueSr & ")"
Con.Execute Sql
MsgBox "操作完成"
End Sub
Private Sub CommandButton2_Click()
Dim Wsr$, TBox$
For x = 0 To 1
TBox = Me.Controls(FieldArr(x)).Text
If TBox <> "" Then Wsr = Wsr & FieldArr(x) & "='" & TBox & "' or "
Next
If Wsr = "" Then MsgBox "请输入工号或姓名": Exit Sub
Wsr = Left(Wsr, Len(Wsr) - 4)
If MsgBox("确定删除?", vbQuestion + vbYesNo) = vbYes Then
Sql = "insert into 离职 select * from 在职 where " & Wsr
Con.Execute Sql
Sql = "delete from 在职 where " & Wsr
Con.Execute Sql
MsgBox "操作完成"
End If
End Sub
Private Sub UserForm_Initialize()
Dim AccPath$
FieldArr = Array("工号", "姓名", "部门", "二级小组", "三组小组")
Set Con = CreateObject("adodb.connection")
AccPath = "d:/Database/data.MDB" ''''这里设置数据库路径
Con.Open ProvidSr & AccPath
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Set Con = Nothing
End Sub
好了 运行就可以了
说的的excel中vba的方法 如果要做成vb软件的话,在vb中的步骤也是一样的
代码和上面一样的 只是控件名称有些不同
添加引用 Microsoft Excel 12.0 Object Library (视你的office版本不同有所差异copy)
然后就可以用这个来编程啦
Dim xlapp As Excel.Application ‘代百表excel程序
Dim wkBook As Excel.Workbook '代表excelworkbook（也就是度excel工作簿文件 .xls .xlsx)
Dim wkSheet As Excel.Worksheet '代表excel的工作页
xlapp.Application.EnableEvents = False '禁止宏等提示的运行知
Set wkBook = xlapp.Workbooks.Open(ExcelFileName)'wkBook对象,把ExcelFileName替换为你的真实文件 比如 app.path & "\" & "百家姓.xls"
Set wkSheet = wkBook.Worksheets(1) '选中某个sheet用工作页名字道(Sheet1,Sheet2...) or 序号