Using VBA Programming to Realize Conversion from EXCEL Table to AUTOCAD Table I. Introduction ---- Microsoft Excel software has very powerful tabulation, table calculation and other functions, is a common tabulation tool for ordinary people. The entire operation process of Microsoft Excel can be controlled through its embedded VBA language. ---- AutoCAD is an engineering drawing software by AutoDesk, a mainstream product in the CAD market, with very powerful functions, and one of the commonly used software for engineering draftsmen. AutoDesk has provided a VBA language interface since R14. ---- In engineering drawing, it is often necessary to insert a drawing table in the drawing, there are generally two methods. One is to use the clipboard to copy the Microsoft Excel table to the clipboard, then open the AutoCAD file, and then paste the file from the clipboard to the desired location. This method is very simple, but has its inherent shortcomings. ①When saving the file, the .xls and .dwg files must be saved together. Once the excel environment is missing, the table will be modified. ② Opening multiple tables at the same time requires a large memory space. ③ The file size becomes very large, and the table is sometimes displayed as an icon in the .dwg file, which is not easy to observe. ---- The second method is to use the VBA function provided by Microsoft Excel and AutoCAD to compile the program to convert, convert the Microsoft Excel table as it was, that is, read all the text and line information in the Microsoft Excel table , Write it out in the AutoCAD file in a one-to-one correspondence to ensure that the converted form is consistent with the original form. This completely avoids the shortcomings of the previous method and facilitates the editing of table content. This article focuses on this method. ---- 2. Analysis of the working mechanism of table conversion and specific implementation methods ---- 1. Analysis of working mechanism of table conversion ---- In the process of watchmaking, two concepts are often encountered, table and grid. ---- In Microsoft Excel, the object corresponding to the table is a worksheet (Sheet or Worksheet), and the object corresponding to each table grid is a range of cells (range), which can include only one cell (cell ), Can also be formed by combining multiple cells. ---- In AutoCAD, there is no object corresponding to the table, but the table can be understood as a combination of several lines and text objects. ---- According to the above analysis, the following conversion methods can be found: ---- Read the smallest object in the Microsoft Excel file-the main information of the cell range (range)-lines and text, and then draw lines at the specified layer and position in the AutoCAD file to write text. Through the loop, traverse all the cell range (range), read while writing, and finally complete the conversion of the table. During the conversion process, keep lines, text and related attributes unchanged. ---- The following discusses the two main object table lines and table text of the conversion work. ---- 2. Conversion of table lines ---- The VBA embedded in Microsoft Excel provides us with great convenience to obtain the information of Excel file. Generally, by accessing the range object, a lot of information can be obtained. The attributes of the access analysis table should start from the analysis range. Each range includes many objects and attributes, for example, the font object can return the font information of the range. Through traversal, you can get the entire table information. The purpose of obtaining table information is to accurately draw the table line according to the position while determining the position of the text. ---- There is a problem with the best algorithm when obtaining table information. Take the line drawing problem as an example to clarify the problem and the solution. ---- Suppose the table is composed of a (a> = 1) row b (b> = 1) column, x and y are cyclic variables, the table is completely composed of cells, because each cell has 4 sides , Let x start looping from 1 to a, and then y loop from 1 to b, read the 4 edges of each cell, it will read a * b * 4 times, repeat reading a * b * 2 times. When x = 1, read the upper side; when y = 1, read the left side, and in other cases read the right side and the lower side. Read a + b + a * b * 2 times. Taking 3 rows and 4 columns as an example, a total of 3 + 4 + 3 * 4 * 2 = 31 times are read, which is the same as the number of edges in the actual table, without repeated reading. ---- It is difficult to read the merged cell information. Because if it is read sequentially according to the position of the cell, the cell range (range) formed by merging a row and b column of cells has only 4 sides. Using the above calculation method, you need to read a + b + a * b * 2 times, repeat reading a + b + a * b * 2-4 times. Taking 3 rows and 4 columns as an example, a total of 3 + 4 + 3 * 4 * 2 = 31 times are read, and 31-4 = 27 times are read repeatedly. The algorithm is duplicated. If you read by row number and column number, there is only one row number and column number of the merged cell, and the value is the row number and column number of the cell on the left and top. For example, after merging cells A2: E5, the row number is 2 and the column number is A. In this way, the table row and column numbers combined by multiple merged cells are discontinuous and discontinuous, making it impossible to read information cyclically. The author found through research that the function address () and the mergearea attribute of the cell can obtain accurate information of the merged cell. The specific method is: when reading cells (x, y) cells, use address () to determine the absolute address of the merged cell area c.mergearea containing cells (x, y), if the first 4 characters and cells ( x, y) The addresses of the cells are the same. The cells (x, y) are the top and left merged cells of the merged cell range. Read the 4 side information, otherwise they will not be read. In this way, repeated reading is completely avoided, and at the same time the entire reading and drawing speed is improved. ---- In AutoCAD, there are many kinds of lines. Considering that it is convenient to control the line attributes, polyline is selected. The specific command is as follows: RetVal = object.AddLightWeightPolyline (VerTIcesList) ---- The following program demonstrates the specific process of reading table lines and drawing table lines. 3. Table text conversion ---- Table text conversion includes two parts: the conversion of the table text itself and the conversion of the table text in the table. ---- In AutoCAD, there are many forms of text annotation, and Microsoft Excel cell area The content of the multi-line text in the field corresponds to the multi-line text command. The command line for adding multi-line text in VBA provided by AutoCAD is: RetVal = object.AddMText (InserTIonPoint, Width, Text) ---- You can control the position of the table text in the table by modifying the properties of RetVal. ---- (1). Conversion of table text itself ---- Analysis of the AddMText command can be drawn: the location of the table text, the width of the text content, Text content can be added by this command. However, the table text font, size, underline, Up and down feet, tilting, bolding, etc. can't. The general method is to adopt the method of modifying the font-shaped file, which is cumbersome and not easy to implement. And it is only valid for the font of modified shape files. Moreover, when the font, size, underline, upper and lower footing, and slanting of different texts in the same text block, When bold is different, the method of modifying the font-shaped file cannot be realized. This article introduces a method that directly uses the method provided by the Mtext command to convert. ---- In the AddMText command, the parameter Text that affects the text content and text attributes. In specific text Add a certain control symbol before the word to control the text attribute of the text. For the specific control symbol, please refer to the AutoCAD help file. For example, {\ F 宋体; \ Q18; \ W1.2; ABCDEFG} set "ABCDEFG" to Song type, Inclined to the right by 18 degrees, the width of each word is 1.2 times the normal width. ---- The specific method used by this program is: read a range of cells in a Microsoft Excel file A certain j-th character attribute (font, size, underline, upper and lower footer, slant, bold), Read the attribute of the j + 1th character in a cell range of the Microsoft Excel file, If it is the same as the jth character, the two use the same control symbol; if they are different, start from the j + 1th character, Repeat the previous work. Sub wz () ---- (2). Conversion of table text position in the table ---- Direct control of the properties of the text object is achieved, by with .... end with structure can be easily Control text height, layer, color, writing direction. As the arrangement position supported by Mtext text is divided into 9 types, it must be arranged according to Microsoft Excel table text Make appropriate judgments in the way, and then convert. The specific implementation method is detailed in the following program. Sub kz () ---- 3. Introduction of functions and features ---- This program can convert all the cells in the Excel table to the original size, The style is converted into an AutoCAD file. In the conversion process, the conversion of table lines and text conversion is the key. Text conversion uses the attributes provided directly by the AddMtext command for conversion, It avoids the method of modifying text files for text annotation in the past, Directly control the font, size, underline, upper and lower footing, tilt, bold, etc. of the table text, The style of each text can be well controlled, which greatly improves the flexibility of text labeling. ---- This program is compiled with Visual BASIC and requires Microsoft Excel 2000 and AutoCAD R14 operating environment. Passed after compilation.
Cordless Floor Cleaner is a new and smart vacuum mopping cleaner. recharge floor cleaner very small,free,simple use. Latest floor wipe cleaner can vacuum ,mopping,wipe home,hotel,office clean area.recharge mopping cleaner very good design,use simple and freely,
Wireless hand floor cleaner advantage:
Cordless hand floor cleaner with special design and best quality
Vacuum ,mopping function
Fast and silent and smart floor hand cleaner
Cordless floor cleaner facotry in shenzhen,china,welcome to visit facotry,we are 15 years manufacture experience.
Cordless Floor Cleaner Cordless Floor Cleaner,Robot Vacuum Cleaner,Cordless Hard Floor Cleaner,Cordless Floor Cleaning Machine Zhengzhou Bangmi Smart Technology Co., Ltd. , https://www.globalcleanrobot.com
Char = RTrim (Left (c.Characters.Caption, 256))
If Char <> Empty Then
textStr = ""
For j = 1 To Len (Char)
If c.Characters (j, 1) .Font.Underline =
xlUnderlineStyleNone Then
cpt = c.Characters (j, 1) .Caption
sonstr = ForeFontStr (c, j)
tempstr = ""
Do While j + 1 <= Len (Char)
sonstr1 = ForeFontStr (c, j + 1)
If sonstr1 = sonstr Then
j = j + 1
tempstr = tempstr + c.Characters (j,
1) .Caption
Else
Exit Do
End If
Loop
textStr = textStr + "{" + sonstr + cpt
+ tempstr + "}"
Else
cpt = c.Characters (j, 1) .Caption
sonstr = ForeFontStr (c, j)
tempstr = ""
Do While j + 1 <= Len (Char)
sonstr1 = ForeFontStr (c, j + 1)
If sonstr1 = sonstr Then
j = j + 1
tempstr = tempstr + c.Characters (j,
1) .Caption
Else
Exit Do
End If
Loop
textStr = textStr + "{\ L" +
sonstr + cpt + tempstr + "\ l}"
End If
Next j
End If
End Sub
'The following function controls the attributes of the font itself
Function ForeFontStr (m As Range, u As Integer) As String
a1 = "\ F" + m.Characters (u, 1) .Font.Name + ";" 'font
a2 = IIf (m.Characters (u, 1) .Font.Superscript =
True, "\ H0.33x; \ A2;", "") 'Footmark
a3 = IIf (m.Characters (u, 1) .Font.Subscript =
True, "\ H0.33x; \ A0;", "") 'Subscript
a4 = IIf (m.Characters (u, 1) .Font.FontStyle =
"Tilt", "\ Q18;", "") 'Tilt
a5 = IIf (m.Characters (u, 1) .Font.FontStyle =
"Bold", "\ W1.2;", "") 'Bold
a6 = IIf (m.Characters (u, 1) .Font.FontStyle =
"Bold Tilt", "\ W1.2; \ Q18;", "") 'Bold Tilt
ForeFontStr = a1 + a2 + a3 + a4 + a5 + a6
End Function
With textObj 'Text object .Height = textHgt
.Layer = newlayer.Name 'Set layer .Color = acRed' Set color .DrawingDirection = 1 'Set writing direction If (ma.VerticalAlignment = xlTop _
Or ma.VerticalAlignment = xlGeneral) _
And (ma.HorizontalAlignment = xlLeft _
Or ma.HorizontalAlignment = xlGeneral) _
Then .AttachmentPoint = 1 'acAttachmentPointTopLeft
If (ma.VerticalAlignment = xlTop _
Or ma.VerticalAlignment = xlGeneral) _
And (ma.HorizontalAlignment = xlCenter _
Or ma.HorizontalAlignment = xlJustify _
Or ma.HorizontalAlignment = xlDistributed) _
Then .AttachmentPoint = 2 'acAttachmentPointTopCenter
If (ma.VerticalAlignment = xlTop _
Or ma.VerticalAlignment = xlGeneral) _
And ma.HorizontalAlignment = xlRight _
Then .AttachmentPoint = 3 'acAttachmentPointTopRight
If (ma.VerticalAlignment = xlCenter _
Or ma.VerticalAlignment = xlJustify _
Or ma.VerticalAlignment = xlDistributed) _
And (ma.HorizontalAlignment = xlLeft _
Or ma.HorizontalAlignment = xlGeneral) _
Then .AttachmentPoint = 4 'acAttachmentPointMiddleLeft
If (ma.VerticalAlignment = xlCenter _
Or ma.VerticalAlignment = xlJustify _
Or ma.VerticalAlignment = xlDistributed) _
And (ma.HorizontalAlignment = xlCenter _
Or ma.HorizontalAlignment = xlJustify _
Or ma.HorizontalAlignment = xlDistributed) _
Then .AttachmentPoint = 5 'acAttachmentPointMiddleCenter
If (ma.VerticalAlignment = xlCenter _
Or ma.VerticalAlignment = xlJustify _
Or ma.VerticalAlignment = xlDistributed) _
And ma.HorizontalAlignment = xlRight _
Then .AttachmentPoint = 6 'acAttachmentPointMiddleRight
If ma.VerticalAlignment = xlBottom _
And (ma.HorizontalAlignment = xlLeft _
Or ma.HorizontalAlignment = xlGeneral) _
Then .AttachmentPoint = 7 'acAttachmentPointBottomLeft
If ma.VerticalAlignment = xlBottom _
And (ma.HorizontalAlignment = xlCenter _
Or ma.HorizontalAlignment = xlJustify _
Or ma.HorizontalAlignment = xlDistributed) _
Then .AttachmentPoint = 8 'acAttachmentPointBottomCenter
If ma.VerticalAlignment = xlBottom _
And ma.HorizontalAlignment = xlRight _
Then .AttachmentPoint = 9 'acAttachmentPointBottomRight
End With
textObj.Update
End Sub