VBS操作EXCEL方法總結(jié)

1、Vbs操作EXCEL方法總結(jié)：一打開Excel電子表格I一說明I演示腳本打開名為C:ScriptsNew_users.xls的現(xiàn)有Excel電子表格。SetobjExcel=CreateObject(Excel.Application)SetobjWorkbook=objExcel.Workbooks.Open(C:ScriptsNew_users.xlsM)復(fù)制代碼讀取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復(fù)制代碼自動調(diào)整MicrosoftExcel工作表中的列I一說明復(fù)制代碼T演示腳本檢索計算機上安裝的服務(wù)的相關(guān)信息，將該數(shù)據(jù)寫入到Excel電子表格中，然后使用Autofit0方法根據(jù)該數(shù)據(jù)來自動調(diào)整列。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()父制代碼一將數(shù)據(jù)添加到電子表格單元格中SetobjExcel=CreateObject(MExcel.Application)objExce1Visible=True3objExcel.Workbooks.AddobjExcel.Cells(l,1).Value=Testvalue1向單元格2J中寫入Testvalue復(fù)制代碼復(fù)制代碼一將帶格式的數(shù)據(jù)添加到電子表格中T一說明7演示腳本將文字“testvalue”添加到新的電子表格中，然后設(shè)置包含該值的單元格的格式。1.SetobjExcel=Crea

6、teObject(Exce1Application)2.objExce1Visible=True復(fù)制代碼復(fù)制代碼3.objExce1WorkbooksAdd復(fù)制代碼復(fù)制代碼5.6.7.objExce1CellsobjExce1CellsobjExce1CellsobjExce1Cells1).Value=Testvalue1).Font.Bold=TRUE1).Font.Size=241).Font.Colorindex=寫入內(nèi)容加粗為真單元格大小為24顏色號為3復(fù)制代碼復(fù)制代碼復(fù)制代碼一列出Excel顏色值I一說明，|一演示腳本顯示在以編程方式控制MicrosoftExcel時可用的各種顏色

7、（及其相關(guān)顏色索引）。1.SetobjExcel=CreateObject(Excel.Application)2objExce1Visible=True3objExce1Workbooks.Add4.Fori=1to56一共有56種顏色可供選擇5objExce1Cells(i1).Value=i6objExce1Cells(i1)Interior.Colorlndex=i7.Next復(fù)制代碼列出電子表格中的服務(wù)數(shù)據(jù)I一說明，|一演示腳本檢索計算機上運行的每種服務(wù)的相關(guān)信息，然后在Excel電子表格中顯示該數(shù)據(jù)。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復(fù)制

9、代碼一對Excel電子表格應(yīng)用自動套用格式T一說明I演示腳本將數(shù)據(jù)添加到Excel電子表格中，然后對該數(shù)據(jù)應(yīng)用AutoFormat(xpRangeAutoFormatList2)o1.ConstxpRangeAutoFonmatList?=112SetobjExcel=CreateObject(*Exce1Application)3objExce1Visible=True4.SetobjWorkbook=objExcel.Workbooks.Add()5SetobjWorksheet=objWorkbookWorksheets(l.)復(fù)制代碼復(fù)制代碼k=17Fori=1to108Forj=1t

10、o10objWorksheet.Cells(ij)=kk=k+1NextNextSetobjRange=objWorksheet.UsedRange14objRangeAutoFormatCxpRangeAutoFormatList?)復(fù)制代碼一基于電子表格中的信息來創(chuàng)建用戶帳戶I一說明I演示腳本基于Excel電子表格中存儲的信息來創(chuàng)建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一演示腳本將數(shù)據(jù)添加到MicrosoftExcel電子表格中，然后在列A中對該數(shù)據(jù)進行排序。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)復(fù)制代碼一設(shè)置一組單元格的格式I一說明I一演示腳本將數(shù)據(jù)添加到電子表格的四個不同的單元格中，然后使用Range對象同時設(shè)置多個單元格的格式。 .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()復(fù)制代碼一在三個不同的列上對Excel電子表格進行排序I一說明I一演示腳本打開Excel電子表格，然后對列A（升序）、列B（降序）和列C（升序）中的數(shù)據(jù)進行排序。此腳本假定工作表包含一個標(biāo)題行（由xlYes來指示，

19、它是Sort方法中的最后一個參數(shù)）。腳本需要一個名為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復(fù)制代碼一列出電子表格中的ActiveDirectory數(shù)據(jù)I一說明1I演示腳本從ActiveDirectory檢索數(shù)據(jù)，然后在Excel電子表格中顯示該數(shù)據(jù)。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