用VBA建立AutoCAD与Excel之间的数据通信-涂晓斌

:2001-10-31:(1976-),,,.:1005-0523(2002)02-0009-03VBAAutoCADExcel涂晓斌1,蒋先刚1,张　馨2(1.,330013;2.,412001):介绍了AutoCAD与Excel之间的通信机制.基于OLE自动化技术,利用VBA在AutoCAD中创建应用程序.该程序可遍历当前图形中的所有实体要素,并从中选取有用的信息进行分类汇总,并利用Excel制作设备材料报表.　　:AutoCAD;VBA;Excel;数据通信:TP391.72　　　　:A0　　AutoCAD,,.,,、、,,,.,,.,..AutoCADExcel,AutoCADExcel,.1　VBAExcelExcel,Excel、.Excel.,ApplicationExcel,.Excel,Ap-plication-WorkBook()-WorkSheet()-Cell().WorkBookExcel,Excel,WorkBooks,,:′1Workbooks(1).Active′MyBook.xls.Workbooks(”MyBook.xls”).Active,,ExcelSheet1、Sheet2Sheet3,Sheet1.ExcelAppExcel,Sheet2,:ExcelApp.WorkSheets(”Sheet2”).ActiveRange,Sort.“Sheet1”“A1”,:WorkSheets(“sheet1”).Range(“A1”).sortKey1:=Work-Sheets(“Sheet1”).Columns(“A”)Cells,Range,RowCol19220026　　　　　　　　　　　　　JournalofEastChinaJiaotongUniversity　　　　　　　　　　Vol.19　No.2Jun.2002,A1A5,:WorkSheets(“Sheet1”).Cells(5,1).Value=WorkSheets(“Sheet1”).Cells(1,1).Value2　AutoCADExcelAutoCADExcel,:′ExcelDimExcelAsExcel.Application′Excel,OnErrorResumeNextSetExcel=GetObject(,“Excel.Application”)IfErr0Then　　　　′Excel′ExcelEndIfSetExcel=CreateObject(“Excel.Application”),,.,AutoCADExcel,AutoCAD,,Excel,.“Excel”①,Edit1,,Excel(),,.“Excel”CommandButton1,:PrivateSubCommandButton1-Click()DimExcelAsExcel.ApplicationDimExcelSheetAsObjectDimExcelWorkbookAsObject′ExcelOnErrorResumeNextSetExcel=GetObject(,“Excel.Application”)IfErr0ThenSetExcel=CreateObject(“Excel.Application”)EndIf′SetExcelWorkbook=Excel.Workbooks.Add′(sheet1)SetExcelSheet=Excel.ActiveSheet′ExcelExcelWorkbook.SaveAsEdit1.textDimobjCountAsIntegerDimIAsIntegerDimRowNumAsIntegerRowNum=1objCount=ThisDrawing.ModelSpace.CountDimmspaceObjAsAcadObject′.ForI=0ToobjCount′SetmspaceObj=ThisDrawing.ModelSpace.Item(I)IfStrComp(mspaceObj.EntityName,”AcDbBlockReference”,1)=0ThenExcelSheet.Cells(RowNum,1).Value=mspaceObj.NameRowNum=RowNum+1EndIfNext′1Excel.Worksheets(”Sheet1”).Range(”A1”).Sort-key1:=Excel.Worksheets(”Sheet1”).Columns(”A”)Dimblockname(50)AsStringDimblocknum(50)AsIntegerDimjAsIntegerDimflagAsIntegerj=1:flag=1′ForI=1ToRowNumIfStrComp(ExcelSheet.Cells(I+1,1).Value,-ExcelSheet.Cells(I,1).Value,1)=0Thenj=j+1Elseblockname(flag)=ExcelSheet.Cells(I,1)blocknum(flag)=jflag=flag+1:j=1EndIfNext′ForI=1ToflagExcelSheet.Cells(I,1).Value=blockname(I)ExcelSheet.Cells(I,2).Value=Str＄(blocknum(I))NextForI=flagToRowNumExcelSheet.Cells(I,1).Value=″″ExcelSheet.Cells(I,2).Value=″″10　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　2002　　Next′ExcelExcel.Visible=True′,ExcelMsgBox″`'Excel!″ExcelWorkbook.Save　′Excel.Application.Quit　′ExcelSetExcel=Nothing　′ExcelEndSub3　AutoCAD,,Excel,,ExcelAu-toCAD.AutoCADExcel,Excel,Excel,,.Excel,、.:[1],.AutoCAD[M].:,2000.[2]　.VBAAutoCAD2000[M].:,1999.BuildingUpDataCommunicationbetweenAutoCADandExcelbyVBATUXiao-bin1,JIANGXiang-gang1,ZHANGXing2(1.SchoolofNaturalScience,EastChinaJiaotongUni.,Nanchang330013;2.ZhuzhouElectricMotorFactory,Zhuzhou412001,China)Abstract:ItintroducesthecommunicationmechanismbetweenAutoCADandExcel.BaseonOLEautomationtechnolo-gy,itbuildsupaprograminAutoCADbyVBA.Theprogramcanransackallentitiesintheactivedrawing,selectandsortthoseusefulinformationinit,anduseExceltoformadevicesandmaterialsreports.KeyWords:AutoCAD;VBA;Excel;Datacommunication112　　　　　　　　　　　:VBAAutoCADExcel