VBS操作EXCEL方法总结

1、Vbs操作EXCEL方法总结：一打开Excel电子表格I一说明I演示脚本打开名为C:ScriptsNew_users.xls的现有Excel电子表格。SetobjExcel=CreateObject(Excel.Application)SetobjWorkbook=objExcel.Workbooks.Open(C:ScriptsNew_users.xlsM)复制代码读取Excel电子表格I一说明T演示脚本读取名为C:ScriptsNew_users.xls的电子表格中存储的值。SetobjExcel=CreateObject(Excel.Application)SetobjWorkbook=

2、objExcel.Workbooks.Open(C:ScriptsNew_users.xlsM)3intRow=2DoUntilobjExcel.Cells(intRowl).Value=5.Wscript.EchoCN:11&objExcel.Cells(intRow,1).Value6.Wscript.EchosAMAccountName:&objExcel.Cells(intRow2).Value7.Wscript.EchoGivenName:M&objExcel.Cells(intRow3).Value8Wscript.EchoLastName:&objExcelCells(intRo

3、w4).VRow=intRow+110LoopobjExcel.Quit复制代码自动调整MicrosoftExcel工作表中的列I一说明复制代码T演示脚本检索计算机上安装的服务的相关信息，将该数据写入到Excel电子表格中，然后使用Autofit0方法根据该数据来自动调整列。SetobjExcel=CreateObject(MExcel.Application)2objExce1Visible=True3SetobjWorkbook=objExcel.Workbooks.Add()SetobjWorksheet=objWorkbookWorksheets(l)x=1strCo

4、mputer=SetobjWMIService=_GetObject(winmgmts:M&strComputer&Mrootcimv2*)Setcolltems=objWMIService.ExecQuery_10(Select*fromWin32_ServiceM)ForEachobjltemincolltems22.objWorksheetCells(x1)=objltem.Nameobj忖orksheetCells(x2)=objItem.DisplayNameobjWorksheetCells(x3)=objltem.State15x=x+1NextSetobjRange=objWo

5、rksheet.UsedRangeobjRange.EntineColumn.Autofit()父制代码一将数据添加到电子表格单元格中SetobjExcel=CreateObject(MExcel.Application)objExce1Visible=True3objExcel.Workbooks.AddobjExcel.Cells(l,1).Value=Testvalue1向单元格2J中写入Testvalue复制代码复制代码一将带格式的数据添加到电子表格中T一说明7演示脚本将文字“testvalue”添加到新的电子表格中，然后设置包含该值的单元格的格式。1.SetobjExcel=Crea

6、teObject(Exce1Application)2.objExce1Visible=True复制代码复制代码3.objExce1WorkbooksAdd复制代码复制代码5.6.7.objExce1CellsobjExce1CellsobjExce1CellsobjExce1Cells1).Value=Testvalue1).Font.Bold=TRUE1).Font.Size=241).Font.Colorindex=写入内容加粗为真单元格大小为24颜色号为3复制代码复制代码复制代码一列出Excel颜色值I一说明，|一演示脚本显示在以编程方式控制MicrosoftExcel时可用的各种颜色

7、（及其相关颜色索引）。1.SetobjExcel=CreateObject(Excel.Application)2objExce1Visible=True3objExce1Workbooks.Add4.Fori=1to56一共有56种颜色可供选择5objExce1Cells(i1).Value=i6objExce1Cells(i1)Interior.Colorlndex=i7.Next复制代码列出电子表格中的服务数据I一说明，|一演示脚本检索计算机上运行的每种服务的相关信息，然后在Excel电子表格中显示该数据。SetobjExcel=CreateObject(*Excel.Applicati

8、on)2objExce1Visible=True3objExce1Workbooks.Add4x=1strComputer=.SetobjWMIService=GetObject_(Mwinmgmts:M&strComputer&Pootcimv2M)8SetcolServices=objWMIService.ExecQuery_(Select*FromWin32_Service)For*EachobjServiceincolServicesobjExcelCells(x1)=objServiceNameobjExcelCells(x2)=objService.Statex=x+1Next复制

9、代码一对Excel电子表格应用自动套用格式T一说明I演示脚本将数据添加到Excel电子表格中，然后对该数据应用AutoFormat(xpRangeAutoFormatList2)o1.ConstxpRangeAutoFonmatList?=112SetobjExcel=CreateObject(*Exce1Application)3objExce1Visible=True4.SetobjWorkbook=objExcel.Workbooks.Add()5SetobjWorksheet=objWorkbookWorksheets(l.)复制代码复制代码k=17Fori=1to108Forj=1t

10、o10objWorksheet.Cells(ij)=kk=k+1NextNextSetobjRange=objWorksheet.UsedRange14objRangeAutoFormatCxpRangeAutoFormatList?)复制代码一基于电子表格中的信息来创建用户帐户I一说明I演示脚本基于Excel电子表格中存储的信息来创建ActiveDirectory用户帐户。SetobjExcel=CreateObject(*Excel.Application)SetobjWorkbook=objExcel.Workbooks.Open(C:ScriptsNew_users.xlsM)3int

11、Row=24.DoUntilobjExcel.Cells(intRowl).Value=“5SetobjOU=GetObject(Mou=Financedc=fabrikamdc=comu)SetobjUser=objOU.Create(User,cn=M&objExcel.Cells(intRow1).Value)objUser.sAMAccountName=objExcel.Cells(intRow2).ValueobjUser.GivenName=objExcelCells(intRow，3)ValueobjUser.SN=objExcel.Cells(intRow,4).Valueob

12、jUsen.AccountDisabled=FALSEobjUsenSetinfointRow=intRow+1LoopobjExcel.Quit #.SetobjExcel=CreateObject(Excel.Application)2objExce1Visible=True .SetobjExcel=CreateObject(Excel.Application)2objExce1Visible=True对MicrosoftExcel工作表进行排序T一说明fI一演示脚本将数据添加到MicrosoftExcel电子表格中，然后在列A中对该数据进行排序。1.SetobjExcel=Create

13、Object(Excel.Application)2.objExcelVisible=True #.SetobjExcel=CreateObject(Excel.Application)2objExce1Visible=True #.SetobjExcel=CreateObject(Excel.Application)2objExce1Visible=True #.SetobjExcel=CreateObject(Excel.Application)2objExce1Visible=True #.SetobjExcel=CreateObject(Excel.Application)2objEx

14、ce1Visible=True3.SetobjWorkbook=objExcel.Workbooks.Add4.SetobjWorksheet=objWorkbookWonksheetsCl)5.objExce1Cells1).Valuem4m6.objExce1Cells(2,1).Value7.objExce1Cells(3,1).Value28objExce1Cells(41).Value39objExce1Cells2).Value10.objExcel.Cells(2,2).Valuea11.objExcel.Cells(3,2).Value=HCM12.objExcel.Cells

15、(4,2).ValueMDU23SetobjRange=objWorksheet.UsedRange14.SetobjRange2=objExcel.Range(MA1M)objRange.Sort(objRange2)复制代码一设置一组单元格的格式I一说明I一演示脚本将数据添加到电子表格的四个不同的单元格中，然后使用Range对象同时设置多个单元格的格式。 .SetobjExcel=CreateObject(Excel.Application)7objExce1Visible=True #.SetobjExcel=CreateObject(Excel.Application)7objExce

16、1Visible=True3objExce1Wonkbooks.Add4.objExce1Cells(l,1).Value=“Name”5.objExce1Cells(l,1).Font.Bold=TRUE6.objExce1Cells(l,2)Interior.Colonlndex=307.objExce1Cells(l,1).Font.Colorindex=28objExce1Cells(2,1).Value=MTestvalue1M9objExce1Cells(3,1).Value=MTestvalue210.objExcel.Cells(4,1).Value=MTetsvalue3M2

17、1.objExcelCell.s(51).Value=MTestvalue4 #.SetobjExcel=CreateObject(Excel.Application)7objExce1Visible=True #.SetobjExcel=CreateObject(Excel.Application)7objExce1Visible=True #.SetobjExcel=CreateObject(Excel.Application)7objExce1Visible=True #.SetobjExcel=CreateObject(Excel.Application)7objExce1Visibl

18、e=TrueSetobjRange=objExcel.Range(MA1M,MA5M)objRange.Font.Size=14SetobjRange=objExcel.Range(MA2u/A5M)15objRangeInterior.Colorlndex=36SetobjRange=objExcelActiveCell.EntireColumnobjRange.AutoFit()复制代码一在三个不同的列上对Excel电子表格进行排序I一说明I一演示脚本打开Excel电子表格，然后对列A（升序）、列B（降序）和列C（升序）中的数据进行排序。此脚本假定工作表包含一个标题行（由xlYes来指示，

19、它是Sort方法中的最后一个参数）。脚本需要一个名为C:ScriptsSort_test.xls的Excel文件。1ConstxlAscending=12ConstxlDescending=23ConstxlYes=14.SetobjExcel=CreateObject(Excel.Application)5objExce1Visible=True16objCommand.CommandTextSetobjWorkbook=_9objExcel.Workbooks.Open(C:ScriptsSort_test.xlsM)21SetSetobjWorksheet=objWorkbook.Wor

20、ksheets(1)objRange=objWorksheet.UsedRange16objCommand.CommandText16objCommand.CommandText22SetobjRange2=objExcel.Range(MAlH)13.SetobjRange3=objExcel.Range(MBlM)14SetobjRange4=objExcel.Range(Cl)16objCommand.CommandText25objRange.Sort5.objExce1Cells1).Value=Lastname6objExce1Cells2).Value=Firstname7.ob

21、jExce1Cells3).Value=Department8objExce1Cells4).Value=Phonenumber9.SetobjConnectionCreateObject(*ADODB.Connection*)objRange2xlAscendingobjRange3xlDescending,16objRange4xlDescending/XlYes复制代码一列出电子表格中的ActiveDirectory数据I一说明1I演示脚本从ActiveDirectory检索数据，然后在Excel电子表格中显示该数据。1.ConstADS_SCOPE_SUBTREE=22SetobjEx

22、cel=CreateObject(KExcelApplication)3objExce1Visible=True4.objExce1WorkbooksAdd16objCommand.CommandText16objCommand.CommandText10SetobjCommand=CreateObject(MADODBCommand)objConnection.Provider=nADsDSOObjectobjConnection0pennActiveDirectoryProviderSetobjCommandActiveConnection=objConnection14.objComma

23、nd.Properties(PageSize)=10025objCommand.Properties(MSearchscopeM)=ADS_SCOPE_SUBTREE17SELECTgivenNameS心department,telephoneNumberFROM&LDAPM/dcXabnikamdcHmicrosoftdcucomWHERE19&MobjectCategory=userSetobjRecordSet=objCommand.ExecuteobiRecondSet.MoueFinst22x=223DoUntilobjRecordSet.EOF24objExcelCells(x1).Value=_25objRecordSet.Fields(MSNM).Value26.objExce1Cells(x2)Value=_27objReco