VBS操作Excel常见方法
作者 佚名
来源 ASP编程
浏览
发布时间 2013-07-09
cel.ActiveSheet.PrintPreview 19) 打印输出工作表: oExcel.ActiveSheet.PrintOut 20) 工作表保存: if not oExcel.ActiveWorkBook.Saved then oExcel.ActiveSheet.PrintPreview 21) 工作表另存为: oExcel.SaveAs( "C:\Excel\Demo1.xls" ) 22) 放弃存盘: oExcel.ActiveWorkBook.Saved = True 23) 关闭工作簿: oExcel.WorkBooks.Close 24) 退出 Excel: oExcel.Quit (二) 使用VBS 控制Excle二维图 1)选择当第一个工作薄第一个工作表 set oSheet=oExcel.Workbooks(1).Worksheets(1) 2)增加一个二维图 achart=oSheet.chartobjects.add(100,100,200,200) 3)选择二维图的形态 achart.chart.charttype=4 4)给二维图赋值 set series=achart.chart.seriescollection range="sheet1!r2c3:r3c9" series.add range,true 5)加上二维图的标题 achart.Chart.HasTitle=True achart.Chart.ChartTitle.Characters.Text=" Excle二维图" 6)改变二维图的标题字体大小 achart.Chart.ChartTitle.Font.size=18 7)给二维图加下标说明 achart.Chart.Axes(xlCategory, xlPrimary).HasTitle = True achart.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "下标说明" 8)给二维图加左标说明 achart.Chart.Axes(xlValue, xlPrimary).HasTitle = True achart.Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "左标说明" 9)给二维图加右标说明 achart.Chart.Axes(xlValue, xlSecondary).HasTitle = True achart.Chart.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "右标说明" 10)改变二维图的显示区大小 achart.Chart.PlotArea.Left = 5 achart.Chart.PlotArea.Width = 223 achart.Chart.PlotArea.Height = 108 如何用vbs把excel的单元格数据写到txt 复制代码 代码如下: If WScript.Arguments.Count > 0 Then Filename = WScript.Arguments(0) Set a = CreateObject("Excel.Application") If Filename = "" Then Filename = a.GetOpenFilename("Excel Files (*.xls), *.xls") If VarType(Filename) = vbBoolean Then MsgBox "Excel2Txt用于将Excel文件的每个Sheet保存为一个文本文件。" & vbCr & vbLf & vbCr & vbLf & "用法: Excel2Txt filename.xls 或在对话框中打开Excel文件。" WScript.Quit End If End If Set w = a.Workbooks.Open(Filename) n = Replace(Replace(w.Name, ".xls", ""), ".XLS", "") a.DisplayAlerts = False For Each s In w.Sheets s.SaveAs w.Path & "\" & n & "_" & s.Name & ".txt", 20 Next a.Quit 把以上代码存为Excel2Txt.vbs双击执行就行了 VBS操作Excel 复制代码 代码如下: Set objExcel = CreateObject("Excel.Application") ''建一个exel对象 Set objWorkbook = objExcel.Workbooks.Open _ ("E:\DOC\Hewl\领域模型.xls") ''打开文件 strToBeWrited = "-----------------------------------" & vbcrlf & _ "-- Generated by ScriptGenerator ---" & vbcrlf & _ "-----------------------------------" & vbcrlf & vbcrlf Count = objWorkbook.WorkSheets.Count ''取sheet数量 Set my = CreateObject("Excel.Sheet") ''新建sheet对象 For Each my In objWorkbook.WorkSheets ''遍历sheet If my.Name = "目录" or my.Name = "SecondHandHouse" Then ''do nothing Else ''Wscript.Echo my.Name ''获得sheet名字 ''Wscript.Echo my.Rows.Count ''strToBeWrited = strToBeWrited & "create table " & my.Name & vbcrlf strToBeWrited = strToBeWrited & "/*==============================================================*/" & vbcrlf strToBeWrited = strToBeWrited & "/* Table: " & my.Name & " */" & vbcrlf strToBeWrited = strToBeWrited & "/*==============================================================*/" & vbcrlf strToBeWrited = strToBeWrited & "create table " & my.Name & " (" & vbcrlf rowNum = 3 Do Until my.Cells(rowNum,1).Value = "" ''Wscript.Echo "sAMAccountName: " & my.Cells(rowNum, 2).Value strToBeWrited = strToBeWrited & " " & my.Cells(rowNum,2).Value & " " & my.Cells(rowNum,3).Value & " not null" If not my.Cells(rowNum,9).Value = "" Then strToBeWrited = strToBeWrited & " default " & my.Cells(rowNum,9).Value End If strToBeWrited = strToBeWrited & "," & vbcrlf rowNum = rowNum + 1 Loop strToBeWrited = strToBeWrited & " constraint PK_" & my.Name & " primary key (id)" & vbcrlf strToBeWrited = strToBeWrited & ")" & vbcrlf End If strToBeWrited = strToBeWrited & vbcrlf Next For Each my In objWorkbook.WorkSheets ''遍历sheet If my.Name = "目录" or my.Name = "SecondHandHouse" Then ''do nothing Else strToBeWrited = strToBeWrited & " constraint PK_" & my.Name & " primary key (id)" & vbcrlf strToBeWrited = strToBeWrited & ")" & vbcrlf End If strToBeWrited = strToBeWrited & vbcrlf Next ''写文件 set fs =createobject("scripting.filesystemobject") set f = fs.opentextfile("E:\DOC\Hewl\dbscript.sql",2, true) ''Wscript.Echo strToBeWrited f.write strToBeWrited f.close Set f = nothing Set fs = nothing objExcel.Quit ''结束退出 |
凌众科技专业提供服务器租用、服务器托管、企业邮局、虚拟主机等服务,公司网站:http://www.lingzhong.cn 为了给广大客户了解更多的技术信息,本技术文章收集来源于网络,凌众科技尊重文章作者的版权,如果有涉及你的版权有必要删除你的文章,请和我们联系。以上信息与文章正文是不可分割的一部分,如果您要转载本文章,请保留以上信息,谢谢! |
你可能对下面的文章感兴趣
关于VBS操作Excel常见方法的所有评论