Excel数据操作

**********************************************************************
1.打开已存在的excel文件，并使其可见
***************************************************
Set excelApp = CreateObject("excel.application")
excelApp.Visible = True  '使excel程序可见
excelApp.Workbooks.Open"d:\test.xls"  '打开d:\test.xls文件
Set excelApp = Nothing
***************************************************
2.指定某个sheet为活动工作表
****************************************************
Set excelApp = CreateObject("excel.application")
excelApp.Visible = True
Set WorkBook = excelApp.Workbooks.Open("d:\test.xls")
oWorkBook.Worksheets("name").activate '设置name表为活动工作表
Set excelApp = Nothing
****************************************************
3．添加、删除工作表
****************************************************
Set excelApp = CreateObject("excel.application")
excelApp.Visible = True
'不显示特定的警告和消息，当出现警告和消息时选择默认应答
excelApp.DisplayAlerts = False
Set WorkBook = excelApp.Workbooks.Open("d:\test.xls")
Set Sheet = oWorkBook.Worksheets.Add '添加新的工作表
oSheet.name = "newcname" 将刚添加的工作表改名为newname
oWorkBook.Worksheets(“Sheet3”).delete '删除表Sheet3
oWorkBook.Save '保存
oWorkBook.SaveAs "d:\test1.xls" '另存为
oWorkBook.Close '关闭工作表
excelApp.Quit '退出excel
Set Sheet = nothing
Set WorkBook = nothing
Set excelApp = Nothing
***************************************************
4．获取某个指定表中的某个单元格的值
***************************************************
Set excelApp = CreateObject("excel.application")
excelApp.Visible = True
Set WorkBook = excelApp.Workbooks.Open("d:\test.xls")
oWorkBook.Sheets("login").activate '设置login表为活动工作表
cellValue = excelApp.Cells(1,1).value '显示第一行第一列的单元格中数据
oWorkBook.Close
excelApp.Quit
Set WorkBook = Nothing
Set excelApp = Nothing
***************************************************
5．修改某个单元格的值或赋值
***************************************************
Set excelApp = CreateObject("excel.application")
excelApp.Visible = True
excelApp.DisplayAlerts = False
Set WorkBook = excelApp.Workbooks.Open("d:\test.xls")
oWorkBook.Worksheets("a").cells(5,5).value="中国" '给a表中的5行5列单元格赋值
cellvalue = oWorkBook.Worksheets("a").cells(5,5).value '获取a表中5行5列单元格的值
oWorkBook.Worksheets("Sheet1").cells(2,1).value= cellvalue '将值赋给sheet1表单元格
oWorkBook.Save
oWorkBook.Close
excelApp.Quit
Set WorkBook = Nothing
Set excelApp = Nothing
***************************************************
6．清除单元格数据
***************************************************
Set excelApp = CreateObject("excel.application")
excelApp.Visible = True
Set WorkBook = excelApp.Workbooks.Open("d:\test.xls")
oWorkBook.Worksheets("a").cells(1,1).clearContents '清除数据
oWorkBook.Save
oWorkBook.Close
excelApp.Quit
Set WorkBook = Nothing
Set excelApp = Nothing
***************************************************
**********************************************************************

转载于:https://www.cnblogs.com/hgfg331/p/3216680.html