Autocad Excel Vba Tutorial

Document Sample
Autocad Excel Vba Tutorial Powered By Docstoc
					    .

    VBA: Integrating with Microsoft Excel - Part 1

    By dave-espinosa aguilar

    This article is the first in a series on integrating the programming power of AutoCAD® VBA with the functionality found in other Windows
    applications—specifically, Microsoft Excel. Through Visual Basic programming within AutoCAD, you can launch Excel and bring the full power of a
    spreadsheet application into an AutoCAD drawing session. The information in this series will demonstrate how to generate, organize, query, and
    extract linework visibly or behind the scenes during an AutoCAD session without spending a dime on additional compilers or documentation. If you
    have AutoCAD and Microsoft Excel already loaded on your workstation, this technology is ready and waiting for you to take advantage of it.

    To enter the world of Visual Basic programming and add Excel functionality to AutoCAD software, you have to be willing to spend the time it takes
    to master this object-oriented programming tool. Even if you've programmed in AutoLISP® before, Visual Basic is not as forgiving or as
    accommodating, and the rules that must be obeyed for everything to work right when you run your own VB applications are very strict. This isn't to
    say that an AutoLISP programmer can't conquer the world of Visual Basic; it just takes more dedication and effort.

    So... as with any serious investment of your time, it pays to step back and examine what getting up to speed on Visual Basic (and the integration of
    AutoCAD VBA and Microsoft Excel) brings to your table in the first place. The more you learn about the software you already own, the more
    powerful it gets and the more impact it stands to have on your productivity. If the nature of your drawings and designs lends itself to spreadsheet
    functionality, then consider setting aside notions of cranking out code, and go Zen with me for a bit as we consider some potentially profound
    questions... beginning with "Why should any AutoCAD user care that integration of AutoCAD and Microsoft Excel is even possible?"

    Reason #1: Automated Fully Customizable Part Counts
    Have you ever counted entities in a drawing manually? By that I mean to ask have you ever (with your finger or pencil) tallied every parking lot space
    or every chair or every toilet in a bathroom or window on an exterior building wall or every HVAC fixture throughout a series of roof plans? Have
    you every counted sprinklers or landscaping features by plant type, bolts or screws for an assembly, or datapoints for a given site plan? Imagine
    pressing a menu button and being done with the process!

    Reason #2: Queries
    Maybe you use blocks with attributes. Have you ever wished you could count blocks (or maybe even select them) based on the values in those
    attributes? Maybe you use Extended Entity Data, which attaches gobs of unique information to individual entities. Wouldn't it be slick if there were an
    easy way to take the sum or average of that information within seconds?

    There are some ingenious tricks and techniques out there like using the old SSX.LSP routine, the internal FILTER command and various other
    filtering functions in AutoCAD, layer isolations, the ATTEXT Block Attribute extraction to CDF/SDF command, and even basic select-objects
    techniques to report entity counts in a drawing. But none of them come close to the reporting, querying, and attachment control that a linked
    spreadsheet application can offer.

    Want to specify the name of a block with an attribute value that falls within a certain numeric range and have those entities meeting that criteria
    reported with full text formatting in a real spreadsheet template? You can with VBA. Imagine reporting attribute information or extended entity data
    from entities based on their location in the drawing or even based on the way in which they are being used or were originally generated (for example,
    count all blocks named "x" that were inserted by CAD operator "y" during month "z" of last year). If you're a SQL guru, you could actually query
    blocks with attribute data using a SQL query statement—entirely through VBA.

    Reason #3: Portability and Data Reporting
    Are there people in your office who use Excel with the same impressive proficiency that you use AutoCAD software? Have you ever wanted to share
    your AutoCAD information with them in a format with which they can easily work? Through coding, you can convert any kind of AutoCAD
    information into a format that speaks another Windows application's language. You can translate your vector base into a grid base or a record base.
    You can view, report, and analyze drawings with all the usual analytical bells and whistles in a spreadsheet (flow charts, pie diagrams, trend charts,
    and so on) so that non-AutoCAD users using their native reporting methods and their native graphical tools can easily understand that AutoCAD data

    .



www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    Reason #4: Externalized Data
    The golden rule of CAD drawing performance: small = fast. An AutoCAD session is never faster to work with than when you start it up. From then
    on, everything just goes downhill in terms of speed and performance. It makes sense to bust out any information in a drawing that is not immediately
    relevant to the work being done. For example, attributes eat up memory and they're cumbersome to work with. Why not store them (associated with
    the relevant entities) in spreadsheet cells rather than as nested entities in a drawing?

    Data externalization provides two terrific benefits: external editing and manipulation of data and drawing-size reduction, which increases drawing
    performance. It is possible to open a spreadsheet and change the value of a cell without ever opening AutoCAD. Can you imagine how easy it would
    be to swap values of certain cells using Excel sorting/replacing or reformatting functions and have those changes immediately reflected in AutoCAD
    block attributes? How much smaller would your drawings be (and how much faster would they load, save, regenerate, and edit) if attribute entities
    were completely removed from them? And while we're discussing attributes...

    Reason #5: Hard-Coded Textual Options
    Now this one might really play with your mind. Imagine that you could control (hardwire) those values you allowed to be entered for any particular
    use of text or blocks with block attributes. For example, imagine that when you open a drawing, it reads information read from a spreadsheet and
    automatically sends that data to a title block. Imagine that you could provide your users a combo box of preset textual or numeric values for any
    block's attribute values (based on the block name no less) when those blocks are inserted. And imagine that the hardwired set of values was controlled
    in a password-protected master spreadsheet file so that only certain users could change the allowed values for those block attributes? There are
    actually ways (using AutoCAD VBA and Excel) to do this kind of thing. The stuff of dreams.

    Reason #6: Tabulation
    In an AutoCAD drawing, put a bunch of text entities with numeric values (for example, currency) in a vertical column (see Figure 1). Now add them
    up and report that value at the bottom of the column. Oops! Something changed. Edit one of the text entity values and retotal everything. Find their
    average value or their minimal value or their maximum value and report that, too, at the bottom of the column. Oops! Sales tax just went up! Increase
    all of the text entity values by a new percentage value and change the new total at the bottom of the column. Are we having fun yet?

    For years, people have been using TEXT entities to report tabulated data on drawings without the benefit of tabulation tools to do it, and a TEXT
    entity doesn't know the difference between a dollar and a toilet. Spreadsheets have been cranking out this kind of information for over a decade. Think
    hard about this one, folks. Maybe, just maybe, the process of creating schedules is possible without getting a headache.




    Figure 1: Creating a schedule even this simple is time-consuming to construct and tally
    without a spreadsheet.


    Reason #7: True Text-Formatting Control

    At the recent AUGI® Annual General Meeting, the top 10 wish-list items were announced for the year and right up there near the top (for the
    umpteenth year in a row) was "tabbing for MTEXT"). If you've ever tried to align columns of TEXT or reorganize the line-by-line textual content of
    an entity after its assigned width space changed (a very painful process), or if you've ever tried to perform any number of basic text-formatting


www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    manipulations within AutoCAD software (which are a cinch to do in Excel), you know how hard it is. Now you don't have to wait for such tools. By
    importing spreadsheet data (or even my linking and/or embedding it), you can bring full text control to your AutoCAD drawings (see Figure 2).
    Changing the assigned width of columns of textual or numerical data can be as simple as a drop-and-drag operation.




    Figure 2: When you bring Excel grids into your drawings, you have complete control over
    text formatting, column width, cell border thickness, and even background patterning.


    Reason #8: Formulas?

    What else can a spreadsheet do with data in a cell? Well for one thing, it can contain a formula to generate cell content based on other cells. That
    means that you can actually apply formulas for TEXT entities in your drawings! Aside from obvious uses of this capability (such as generating totals
    after a part count), you could even use Excel to figure out (through conditional states) what text, notes, or details need to be imported into a drawing
    based on parts found in a drawing. Yes, folks... this starts getting wild.

    Reason #9: Automated Linework Generation
    And while we're on the subject of things being imported based on queries and tallies and other amazing spreadsheet operations, we can also generate
    linework itself from spreadsheets. You can convert databases to an Excel format, and easily view and edit them. And once you have that data in a
    spreadsheet format, it is possible to start generating linework from it! Imagine that you have a basic COGO point set of X, Y, and Z values broken
    down into three columns. Contour lines or TINs could be generated from this information.

    Reason #10: Costing Out a Drawing
    Excel spreadsheets make great reference tables. Imagine that you have a list of prices for items you insert into your drawings, and imagine that you
    want a part count and a financial assessment of a drawing. To do this, you can begin with surveys of the entity database, pulling the necessary
    computational information into preset columns in a spreadsheet, and then price out the cost of the entire project based on an entirely different
    spreadsheet of price-per-item values. Inventories on steroids, and generated no less by a CAD user!

    I could give you in a heartbeat another 10 reasons to think seriously about this stuff, from enhanced job opportunities and more competitive salaries to
    the complete historical tracking of any drawing. In the coming articles, we'll examine sample code and develop microapplications that accomplish the
    types of operations described above. And it's not miracle working. It is, however, exciting and profitable stuff... for anyone willing to learn the game.




www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    VBA: Integrating with Microsoft Excel - Part 2

    By dave espinosa-aguilar

    This article is the second in a series on integrating the programming power of AutoCAD® VBA with the functionality found in other Windows
    applications—specifically, Microsoft® Excel. In the first article, we discussed what is possible; now it's time to examine how it's possible. And that
    begins with a quick overview of the AutoCAD VBA Integrated Development Environment (or IDE).

    The IDE
    At the command prompt, enter the command VBAIDE. This initializes the IDE in a separate window. You can use the ALT+TAB keys to switch back
    and forth between the IDE and your AutoCAD session. If you close AutoCAD, the IDE window closes with it. The IDE is used to develop and debug
    your program code and your dialogs and to view the information your program is processing (see Figure 1). Once you have finished using the IDE
    (usually after saving your programming work), you can close the IDE window without closing AutoCAD.




    View Larger Image


    Figure 1: The IDE enables
    you to simultaneously view
    your application user forms,
    code, the object browser,
    the toolbox, object
    properties, references and
    more.


    The VBA Manager

    You can also bring up the IDE through the use of the VBAMAN command. Unlike VBAIDE, the VBAMAN command enables you to load (and
    unload) existing VBA project files to/from memory before jumping to the IDE to develop or modify them. You can work with several project files
    open at once. When you use the VBAMAN command, the VBA Manager dialog box opens. The Visual Basic Editor button in this dialog box takes
    you to the IDE (see Figure 2). The VBA Manager is also where you create new project files, develop macros to run your project files, or embed your
    programs in any of your open drawings.




www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    Figure 2: The VBA Manager Dialog loads, unloads, and exports application code.


    Our First User Program

    Rather than try to explain every button and function in these interfaces, we're going to go straight to developing our very first program. It's objective is
    simple enough: open and close Excel. Make sure you have Excel already installed on your system, and then follow along as we begin to develop our
    first application:

    1. Start by making sure you're in a brand new AutoCAD session.

    2. Use the VBAMAN command to bring up the VBA Manager dialog box.

    3. Click the New button. This creates a new project with a default project name (ACADProject)

    4. Click the Visual Basic Editor button to go to the IDE.

    5. Enter Control+R or select Project Explorer from the View pull-down menu to view the Project Explorer. It may be docked to one side of your
    window or it may be a floating window. In either case, it should be reporting your ACADproject.

    6. If the project name displays a + (plus) sign to the left, click on the plus sign to see subitem ThisDrawing (see Figure 3).




www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    Figure 3: The project window reports projects loaded, and UserForms included in a project.




    View Larger Image

    Figure 4: A UserForm is created for the application. The Project window and Toolbox are organized for easy object placement.

    7. From the Insert pull-down menu, select UserForm pull-down to insert a UserForm into this project. Stretch the UserForm dialog box and the
    window the UserForm sits in so that they take up approximately the space shown in Figure 4.

    8. From the View pull-down menu select Toolbox, which opens the Toolbox dialog box. You can also stretch the Toolbox dialog box.

    Setting Up an Excel Reference
    Once you've added the UserForm to the project, the next step is telling AutoCAD software how to speak Excel-ese. References expand AutoCAD
    VBA to include new tools and capabilities, and for this application we need to be able to speak in terms of worksheets, cells, ranges, and other
    concepts associated with spreadsheets. Here's how we do it:

    1. From the Tools pull-down menu, select References, which brings up the References dialog box.

    2. Several references at the top of the Available References list may already be selected. Leave them as they are, and search down through the list of
    available references until you see one that looks like Microsoft Excel 9.0 Object Library (this assumes Microsoft Excel 2000 is already installed).
    Select the box to the left of the Library as shown in Figure 5. When you do this, the Location label at the bottom of the dialog box should report a path
    similar to c:\Program Files\Microsoft Office\Office\EXCEL9.OLB.




    Figure 5: Adding the Microsoft Excel 9.0 Object Library reference enables your application
    to speak "Excel-ese."


    3. Click the OK button, which returns you to the IDE.



www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    That's all it takes to expand our application to speak in Excel terms.

    Adding Buttons to the UserForm
    Now that you've told the application how to speak Excel-ese, let's create some buttons on our UserForm dialog box that launch Excel, close Excel, and
    Quit out of our application. Here we go:

    1. Move your cursor over the various objects in the Toolbox dialog box and pay close attention to the tooltips. Find the tool with tooltip
    CommandButton and click it. Your cursor changes to CommandButton draw mode.




    View Larger Image

    Figure 6: You can re-organize the look and layout of your application and development interfaces at any time.

    2. Move your cursor over the UserForm and pick an upper-left point. This will spot a button on the UserForm. Click and drag on the button grips to
    stretch it into any size you like. Create two more buttons and stretch them so that they appear similar to those shown in Figure 6.

    3. Slowly click twice over a button to move the cursor inside the button, enabling you to change the button's label.

    If you click twice too quickly, a code window will appear instead. Close out any code windows that appear and try again if necessary, clicking slowly
    inside each button to move the cursor into the button text.

    4. Change the label for each button so that CommandButton1 text reads Launch Excel, CommandButton2 text reads Close Excel, and
    CommandButton3 text reads Quit as shown in Figure 7.




    Figure 7: It is a helpful practice to include a Quit button in your primary UserForm
    to provide an easy means to stop your application when it is running.




www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    Your First Coded Object

    Now that you have finished the dialog box, it's time assign code to each button. Again, we're going to start very simple by writing the code for the
    Quit button.

    1. Double-click the Quit button to bring up the button's code window.

    The code window shows a combo box with the value CommandButton3 in the upper-left corner so you know the code you're writing will pertain to
    this button only. In the upper-right corner of the code window is another combo box with value Click, which tells you that the code will be called up
    when this button is clicked while the application is running. In the main part of the code window are two already typed statements:

    Private Sub CommandButton3_Click()

    End Sub

    2. Click between these two statements and enter the word End so that the code statement now appears as:

    Private Sub CommandButton3_Click()
       End
    End Sub

    Each button acts as a routine unto itself. The END command in Visual Basic terminates a running application. We're going to run our little program
    and use this button to stop it. The indenting of the command isn't vital to the program running correctly, but it does help keep command statements
    visually organized.

    3. Close the code window.

    4. From the Run pull-down menu, select Run Sub > UserForm menu item to launch our program.

    Your dialog box should appear over your AutoCAD session. Clicking on the upper two buttons will do nothing, but if you click the Quit button, you
    should return to the IDE.

    5. Click the Quit button to terminate the program.

    You just ran your first program! You can also run a program by typing the F5 key when you are in the IDE.

    Launching and Closing Excel
    This next part is going to take some explaining. But first, let's get the code for the top two buttons assigned.

    1. Double-click the Launch Excel button to bring up its code window and type the following code into it:




www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    2. Likewise, double-click the Close Excel button to bring up its code window and type the following code into it:




    Let's take a closer look at the code for the Launch Excel button. The code for this object begins by dimensioning variable excelApp as an Excel
    application object type, variable wbkObj as an Excel Workbook object type, and variable shtObj as an Excel Worksheet object type (all three of which
    are types now available to us with the excel 9.0 Object Library referenced). If any error occurs, the code is instructed to continue on instead of
    stopping at the error. The UserForm is hidden from view and the error state variable is cleared (set to a value of zero). Here's the relevant code:




    Next, the application variable is set to a currently running session of Excel. This may seem a little weird at first, but the code does not start by
    launching Excel. Now, if Excel isn't running, this statement generates an error (which has a nonzero value). If an error is generated, then we know that
    Excel isn't running yet. So we clear the error state and use the CreateObject function to launch an Excel session. So we're essentially saying "If Excel
    is already running, set the variable to what is running. Otherwise, start up Excel." If launching Excel generates an error, we know that Excel isn't even
    installed. We report this through a message box exclamation function and end the program. Here's that code:




www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    Once the application variable is set to either the currently running session of Excel or a new session of Excel, the UserForm is made visible again, and
    the workbook and worksheet variables are set to the first workbook and the first worksheet in that session. This done, the UserForm is made visible
    again so that we can click the buttons to close Excel down or quit our VBA program. Here's that code:




    The code for the Close Excel button is fairly imitative of that for the Launch Excel button. Variable excelApp is dimensioned from scratch again in
    this procedure as an Excel application object type. The code is instructed again to continue if an error is encountered, the UserForm is hidden, and the
    error state is cleared. Variable excelAPP is set to an assumed already running session of Excel. If Excel isn't running (someone may click Close Excel
    first—after all, you have to consider every possibility), an error is generated, and we are told Excel isn't running. If no error is generated, then the
    application quits. The UserForm is made visible again so that we can click our three buttons:




    When running this VBA routine, it helps to use ALT+TAB to switch between Excel and AutoCAD after Excel has been launched. Viewing the
    Windows Task Manager (CTL+ALT+DEL) is also helpful to check if you've left any Excel application open. The code in the Close Excel button does
    shut down the currently running session, but if you check the Task Manager after using Close Excel, you should see an icon for the currently running
    Excel session. You can always shut down any Excel sessions through the Task Manager if needed. What happens if you press Launch Excel twice in a
    row? What happens if you then press Close Excel?

    In Conclusion
    In future segments we'll discuss how to pass information to Excel from our VBA routine, how to format that passed information, and how to channel
    it to a single cell or multiple cells and ranges. We'll also look at how to pull information from a spreadsheet into a UserForm list box, edit box, combo
    box, and so on. For now, make sure you understand clearly how to make the Excel connection.




www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    VBA: Integrating with Microsoft Excel - Part 3

    By dave espinosa-aguilar

    This article is the third in a series on integrating the programming power of AutoCAD® VBA with the
    functionality found in other Windows applications—specifically, Microsoft Excel. In the first segment we
    discussed what was possible, in the second segment we discussed how to establish a basic link with Excel through
    AutoCAD VBA to open an Excel session and close it down, and in this session we discuss how to port formatted
    data from AutoCAD VBA to Excel worksheets.

    Objects of Our Desire

    Now that you know how to make an AutoCAD VBA routine speak "Excel-ese," and how to launch a session of
    Excel, let's examine the Workbook and the Worksheet objects in Excel.

    You can study the properties of these and other Excel objects through the AutoCAD VBA IDE (integrated
    development environment) by:

    1.    Running the IDE (use the VBAIDE command).
    2.    Using the Tools/References pulldown menu option to activate a Microsoft Excel Object Library reference
       by placing a check next to this entry in the References listbox.
    3.    Using the Object Browser tool (use the F2 function key in the VBA IDE or use the View/Object Browser
       pull-down menu option) with the primary Combobox set to Excel (see Figure 1).




www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    Figure 1: The Object Browser.




    View Larger Image


    Figure 2: Create a
    UserForm as shown.


    The Object Browser reports the properties, classes, and variable types associated with objects. For example, if you
    select a Worksheet class, it has a Cells property as a Range class. If you select the Range class, it has a Value
    property, which holds a variant value. Let's use this information to pump different types of values (integers, real
    numbers, and text) into cells in an opened spreadsheet.

    1. Use the process outlined in the last tutorial to create a UserForm as shown in Figure 2.

    2. Add a Microsoft Excel Object Library reference to the form.

    3. Stretch the UserForm and CommandButtons so that they can accommodate the button text.

    4. Apply the following code to the top button, CommandButton1, and bottom button, CommandButton3:




www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    We added an End command to the code for CommandButton3 so that when this button is clicked, not only is the
    current spreadsheet closed, but the VBA routine is also exited, thus returning us to the VBA IDE. You can run the
    routine at this point, but remember, once the Excel spreadsheet appears on your screen, you must toggle back to
    your AutoCAD session (use ALT+TAB to move back and forth between the current Excel session and AutoCAD
    software) to see your UserForm. Let's examine the code for CommandButton2.

    Going Out for the Pass

    The code for this CommandButton assumes that Excel is already running. Three variables are established to hold
    an integer, a real number, and a text value. These values are passed to the Excel Worksheet object cell-value
    property using (row,column) format and notation.

    1. Apply the following code to CommandButton2:




    2. Run the application now.

    3. Click on the top button to open a spreadsheet.

    4. Click on the middle button to fill the first column with values.

    5. Click the third button to shut down the application and the spreadsheet.

     Note: Excel will not completely close down until after you've told it not to save the spreadsheet.




www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    Excel VBA IDE

    AutoCAD software isn't the only application with a VBA IDE. Microsoft Excel also has a VBA IDE, which you
    can access from the Excel main menu by selecting Tools > Macro > Visual Basic Editor (see Figure 3).




    View Larger Image

    Figure 3: Accessing the Excel VBA IDE.

    What if you want to close down the Excel spreadsheet without having to click a DisplayAlert dialog box that asks
    you if you want to save the workbook? Go to the Excel VBA IDE Help facility to find this information
    (DisplayAlert property of an Application object).

    1. Replace the code for CommandButton3 with the following code, which completely closes Excel without first
    calling a DisplayAlert prompt:




    2. Run the application again, clicking on the top button and then the middle button.

    Did you notice that the value 1.5 was not reported properly in cell 2,1? This is because each cell has default
    formatting, and we have not instructed Excel to change its formatting once data has been passed to its cells. With
    the Format function you can set the formatting for any cell by passing formatted values to the cell.

    3. Quit the application.

    4. Replace the code for CommandButton2 with the following code:




www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    5. Restart the application.

    6. Use the top and middle buttons to send the new values to the spreadsheet.

    7. Remember to stretch the column width of the first column to accommodate the values passed to it.

    The resulting values passed to the spreadsheet should look like those shown in Figure 4.




www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    Figure 4: The values are passed to the spreadsheet.


    Keeping Up Appearances

    In addition to cell value formatting, you can also control cell sizing, boldfacing, italicizing, and column widths.

    1. Replace the code in CommandButton2 with the following code:




www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    2. Run the application again to see how to control the appearance of values.

    Home on the Range
    Sometimes you will want to pass a value to many cells or to pass a function to a cell for a spreadsheet calculation.
    So let's end this segment by examining the code that accomplishes this.




    2. Run the application again to see the effect.

    Not much needs to be said here. The code uses the Range class of the object with a cell range format value and the
    Formula property of the Range class to assign a formula to a cell.

    The value of 4 is passed to four cells, and the average, sum, and a subtraction of values is calculated in adjacent
    cells.


www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    Next time, we'll discuss pulling values from an Excel spreadsheet into AutoCAD VBA objects such as ListBoxes
    and ComboBoxes, and we'll also get our first look at code to survey counts of objects within an AutoCAD drawing
    and report them in a spreadsheet.




www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    VBA: Integrating with Microsoft Excel - Part 4

    By dave-espinosa-aguilar

    This article is the fourth in a series on integrating the programming power of AutoCAD® VBA with the
    functionality found in other Windows applications—specifically, Microsoft Excel. In the first three segments, I
    outlined the reasons for merging these two applications, the procedure for establishing a link between them, and a
    method to pass information from the AutoCAD VBA environment to Excel. In this segment, we focus on the
    reverse process: pulling information from a preexisting Excel spreadsheet into the AutoCAD VBA environment.
    We also examine our first sample application, which queries an AutoCAD drawing for all user-inserted blocks and
    reports its findings in a UserForm and a new spreadsheet.

    Pulling Values

    Why might you want to pull values from specific cells in a preexisting Excel spreadsheet into the Textboxes,
    Listboxes, and Comboboxes in an AutoCAD UserForm? Suppose that you have an existing spreadsheet with two
    columns of data in it: the first column lists Block names typically found in your drawings and the second column
    has a price for each Block. By referring to any row in this spreadsheet, you could write an AutoCAD VBA routine
    to find the total cost of all Blocks found in any drawing!

    Before we build such a neat tool, let's examine the code that connects AutoCAD VBA objects with a preexisting
    spreadsheet. First examine the simple spreadsheet shown in Figure 1.




    Figure 1: A simple sample spreadsheet.


    The sample application below pulls cell values from this spreadsheet and its cell values into a Textbox, a Listbox,
    and a Combobox. The code also demonstrates how cell values can be reported in columns for a Combobox.

    Create the spreadsheet shown in Figure 1 and save it in a known directory. In a new VBA project, insert a
    UserForm and add CommandButtons, a Textbox, Listboxes, and Comboboxes as shown in Figure 2. Now apply
    the sample code to each CommandButton.




www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    Figure 2: UserForm layout for importing a spreadsheet's cell values.


    Run the application using ALT+TAB to verify the result of each CommandButton when it is clicked. You must
    modify the path string in the code for the CommandButton that opens the existing spreadsheet to make these
    subroutines work properly. You must also expand the Combobox to see the columnar data pulled from the
    spreadsheet. It goes without saying that for any of the remaining sample applications shared in this series, you
    must always add a Microsoft Excel Object Library reference before your code can speak "Excelese."




www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    Now that you have an idea of how to move information back and forth between AutoCAD VBA objects and a new
    or existing spreadsheet, let's examine our first really powerful yet simple sample application.

    Sample Application: Block Reporter

    Now things get exciting. This small utility surveys a drawing for any user-created Blocks and tallies them for you.
    It also offers you the option of pumping this tallied information into a new spreadsheet. The routine first builds a
    list of all Block names found in the drawing (excluding any that begin with an asterisk) and adds them to a
    Listbox. It then cycles through that list of Block names and counts the total number of Block Inserts found for
    each name. Since several buttons use the same variable names for accessing Excel objects, these variables are
    declared publicly in the General Declarations area rather than in each subroutine. You can see the utility interface
    in Figure 3.




    Figure 3: Block Reporter interface.


    At the risk of stating the obvious, to test this utility, you must either create a new drawing with some blocks in it or
    open an existing drawing with blocks in it before running it. Remember, too, to create your Excel reference before
    running this utility!




www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    Figure 4: The Block Reporter in action.


    In the first segment of this series, I offered several reasons why any AutoCAD or AutoCAD-based product user
    would even care about this topic of integrating Microsoft Excel functionality with an AutoCAD-based product
    through AutoCAD VBA. It is the primary focus of the remaining segments of this series to examine and provide
    several simple yet demonstrative sample applications which point to the full potential of these merged
    technologies.




www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    VBA: Integrating with Microsoft Excel - Part 5

    By dave espinosa-aguilar

    This article is the fifth in a series on integrating the programming power of AutoCAD® VBA with the
    functionality found in other Windows® applications—specifically, Microsoft® Excel. Previously, we've explored
    the benefits of merging these two technologies (for example, creating part counts) and in the previous segment we
    looked at a sample application that, in fact, generated part counts. In this segment, we'll examine two sample
    applications developed to query drawing data and report findings in spreadsheet format, including the reporting
    of layer-entity counts using spreadsheet graphics, and we'll examine a truly powerful utility which can externalize
    data by using a spreadsheet row as an entity ATTRIBUTE substitute.

    Sample Application #2: Query Reporter

    With this application, you can select LINE entities, tally the lengths of all lines found on each selected LINE's
    layer, and report them to a spreadsheet. If a non-LINE is selected, it is ignored. If the layer of a selected LINE has
    already been gleaned from a previously selected LINE, it is also ignored. The interface for the utility is shown in
    Figure 1, and the code for the interface is provided below the figure.

    The first ListBox reports all layers gleaned from all selected LINEs, and the second ListBox reports those same
    layer names without duplicates. Therefore, this utility shows one way to delete duplicate values found in a ListBox
    object—a very handy algorithm. Remember to create a Microsoft® Tool Reference (for example, Microsoft Excel
    9.0 Object Library) before running the utility. The CommandButton's WordWrap property is set to True to
    accommodate the button text (see the code below). As with Sample Application #1 provided in the previous
    segment, global variables used for spreadsheet linking are placed in the General Declarations section.




    Figure 1: Interface for Query Reporter.




www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    The routine builds a list of layer names from the selected LINEs. It then creates a new list of layer names without
    duplicate values. The entire drawing database (ModelSpace) is surveyed for LINEs on the layer name list, and
    each LINE's length is added to a total per layer. These length totals are passed to the new spreadsheet. In building
    construction drawings, you could use this utility as a way to calculate HVAC venting values, pipe GPM values,
    wall surface area values, or electrical wiring lengths.

    Sample Application #3: Spreadsheet Graphics Reporter

    Microsoft Excel can present numeric information in ways very different from those found in AutoCAD software.
    This application shows how you can present layer surveys using Excel graphics. A list is made of all layers found
    in the drawing. A total entity count for each layer is then calculated and the results are reported in percentages
    using an Excel Graphics pie chart. The interface for the utility is shown in Figure 2, and the code for the interface
    is shown below Figure 3. Be sure to create several entities on several layers to test this routine.




www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    Figure 2: Interface for Spreadsheet Graphics Reporter and a sample drawing.


    Figure 2 not only shows the interface for this utility, but also an example drawing in which several POINT entities
    have been created on various layers, including Layer 0. When the utility is run on this drawing, the spreadsheet
    shown in Figure 3 is generated. Notice that 13 entities are found on Layer4 (see #5 in Legend), which comprise 40
    percent of all point entities found in the drawing.




    Figure 3: Spreadsheet showing Entity Percentages by Layer.




www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    Sample Application #4: The Externalizer

    In the last sample application we'll look at in this segment, we break all the rules in AutoCAD about a BLOCK
    being the only type of entity that can "contain ATTRIBUTE values." In essence, this application makes it possible
    for any entity type to "contain ATTRIBUTE values." These "ATTRIBUTE values" are not actually created as
    ATTRIBUTE entities in the drawing. Instead, these values are stored in a spreadsheet as rows of cell values.

    Each entity with its own ATTRIBUTES is linked to a unique spreadsheet row by using the entity's handle value (a
    unique hexadecimal value generated by AutoCAD software at the time of entity creation). The first cell in any row
    is dedicated to the handle value of the entity to which it is linked. The application can survey any selected entity
    for its handle and create a new row of values for it if that handle is not already found in the spreadsheet. If a
    handle value is found in the spreadsheet, then the existing values for that entity can be reported, edited, or deleted.
    Slick.

www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    Using this type of utility requires that the spreadsheet of "ATTRIBUTE values" travel with the drawing, so that
    the values for attached entities can be edited, queried, and so on. You're already used to having font files, shape
    files, external reference file, and other file types travel with their corresponding drawings, so requiring a
    spreadsheetfile to travel with corresponding drawing files should be no great burden. By naming the spreadsheet
    file the same file name as a drawing file, you can keep related spreadsheets and drawings together. Hey, improved
    processes sometimes require new standards! This application is provided as a glimpse of the kinds of things that
    can be accomplished with Excel and AutoCAD VBA: the routine could be modified in countless ways to extend
    this ATTRIBUTE power even further.

    If it isn't obvious already, the real power of this application is its ability to externalize ATTRIBUTE data to an
    Excel spreadsheet. Because the ATTRIBUTE values are stored outside the drawing, the drawing itself is much
    smaller (no ATTRIBUTE entities are in it), so the drawing is faster to load, edit, and save. Externalizing
    ATTRIBUTE data provides a number of other powerful (and perhaps not obvious) benefits:

             "ATTRIBUTE values" created in this way can be applied to any entity type, not just BLOCKs. Wow!
             "ATTRIBUTE values" stored in this fashion can be edited in Excel without AutoCAD software even
    running! If "ATTRIBUTE values" for an entity are edited in Excel, the next time AutoCAD is launched and that
    entity is surveyed with this VBA utility, the updated "ATTRIBUTE values" will be reported properly.
             Have you ever tried globally editing actual ATTRIBUTE values, in different ATTRIBUTE tags across
    different BLOCK insertions, in a drawing all at once? It's a piece of cake with Excel using Search and Replace
    functions on cells throughout a spreadsheet. The ramifications for "ATTRIBUTE value" editing using this
    methodology are staggering.
             Storing "ATTRIBUTE values" in this way means that ATTRIBUTEs could hold formulas! Don't even get
    me started on how wild this gets.
             Unlike a BLOCK which has a fixed number of ATTRIBUTES that can be filled, a spreadsheet can hold
    virtually unlimited "ATTRIBUTE values" for any entity since its values are stored as unlimited cell values in a
    dedicated row. One inserted BLOCK might hold three "ATTRIBUTE values" while the same BLOCK inserted
    elsewhere in the drawing might hold 20 "ATTRIBUTE values." Using normal AutoCAD BLOCK objects, this
    type of information-packing would be impossible.
             "ATTRIBUTE values" stored in this way can be stored as actual numbers, or currency values, or dates if
    needed! Up until now, ATTRIBUTEs in AutoCAD have only been able to store values as strings (an ATTRIBUTE
    value of 100 was not stored as a numeric value of 100 but as a word value of "100"). "ATTRIBUTE values" in a
    spreadsheet can actually be added to each other, processed in formulas, or even evaluated against calendars! How
    many years have you been waiting for this capability?
             BLOCK ATTRIBUTE values always use the same ATTRIBUTE value order. With a spreadsheet used for
    "ATTRIBUTE values," you can mix the order of values in any way you choose, regardless of what entity type
    they're attached to. You can create a cell/ATTRIBUTE sequence standard so that values always report in the
    same order, too, if you want value order maintained.

    I told you this AutoCAD VBA/Excel integration stuff gets wild. I wasn't kidding. Now that we have an idea of how
    this application works logistically and what benefits it can provide us, let's examine the application interface
    shown in Figure 4 below. TextBox1 in the code provided is actually the textbox object for ATTRIBUTE
    attachment, and TextBox2 is the textbox for the complete XLS filename (drive, directory, file name, extension).

    The utility requires some existing drawing entities already in place in order to have "ATTRIBUTE values"
    attached to them, and of course you'll need to make a Tools/Reference to Excel to make it work. This utility does
    not declare public variables. Each button is self-contained to open and close Excel for its own uses rather than
    leaving Excel open until the application is terminated.

    A final reminder: these sample applications are created to demonstrate, not optimize, the points being made in this
    series. There are countless ways to rewrite them more efficiently and more powerfully. The essential goal behind
    them is to make clear their potential to the reader.

www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    In this application, the Reporting function will bring up an alert if values do not exist for a selected entity
    (meaning there is currently no dedicated row for it in the spreadsheet). If an entity has a corresponding row of
    information in the spreadsheet, all row values are listed in the ListBox. The Attachment button searches the
    spreadsheet for the handle of a selected entity. If it finds the handle, it attaches the new value in TextBox1 (within
    the frame) to the end of the handle row. If a handle does not exist, a new row is linked with the handle and the
    value is placed to the right of it.

    If a spreadsheet does not exist with the same file name of the current drawing, by default a new spreadsheet is
    created bearing the same name as the drawing. The Delete Attribute function clears a linked row with previously
    reported ListBox values except for a required single selected value. The Clear All Attributes function deletes
    (using the xlShiftUp function) the entire linked row, which has the selected entity's handle value.




    Figure 4: The interface for the Externalizer.




www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    Conclusion

    There are many ways to externalize data in a drawing besides moving ATTRIBUTE data into a spreadsheet
    format. Large notes, schedules, and title-block data are examples of other information that might be well suited to
    a spreadsheet format, especially considering the more powerful text-formatting control available in Excel. In the
    final segment of this series, we will examine more sample applications which further illustrate just how valuable
    integrating these technologies can be.

    In the meantime, enjoy tweaking the applications we covered in this article!




www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    VBA: Integrating with Microsoft Excel - Part 6

    By dave espinosa-aguilar

    This article is the last in a series on integrating the programming power of AutoCAD® VBA with the functionality
    found in other Windows applications—specifically, Microsoft® Excel. In the five previous segments, we looked at
    some of the productivity benefits that arise from merging these two technologies, and I provided several sample
    applications to demonstrate them. These samples included automated part counts and graphical and textual
    reporting of entity counts and properties.

    Part 5 of this series demonstrated a totally new productivity concept: how to externalize attribute data to a
    spreadsheet so that you can reduce drawing size and edit attribute values outside of AutoCAD software. This
    process also provides a way to treat attribute data as more than a string by enabling you to assign numeric values,
    currency, dates, and so on to it. In this final segment, we examine sample applications that do cost analysis using a
    pricing index spreadsheet and layer delegation based on a drawing block's attribute values.

    Sample Application #5: Cost Analysis
    Examine the pricing index spreadsheet in Figure 1. The names of four drawing blocks appear in column A, and a
    corresponding price for each block appear in column B. This application searches for occurrences of these four
    blocks in any drawing, calculates their cost, and provides a grand total. The spreadsheet itself specifies the query
    criteria since only those blocks listed in column A are searched for in the drawing. You could easily tailor this
    application to search for other entity types found on specified layers.




    Figure 1: A block pricing-index
    spreadsheet.


    The application reads all values found in column A until it finds an empty cell. It then searches the drawing and
    reports the number of instances of each block it finds. Next it multiplies this quantity by the corresponding block
    price found in column B to arrive at a total cost for each block named in the spreadsheet. Finally, it adds up these
    costs and delivers a grand total. Previous sample applications have also shown how this data itself can be exported
    to a spreadsheet report. Anyone can generate a new pricing index sheet or edit an existing one to perform this type
    of cost analysis.

    The interface for this application, shown in Figure 2, is fairly straightforward. Listboxes report block names,
    quantities, prices by block name, and totals. The spreadsheet file name is specified in the Price Spreadsheet text
    box, and the Grand Total appears in the Grand Total text box. You'll need to activate an Excel Object Library
    Reference as usual, and it goes without saying that to test this application, you'll need corresponding block
    insertions in a drawing. Figure 2 shows the application running with blocks in a drawing.




www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    Figure 2: Cost-analysis interface along with block insertions.


    Here's the code for this sample application:




    View Larger Image


    Sample Application #6: Layer Delegator

    With an application of this type, you can examine information contained in an AutoCAD entity according to
    criteria specified in a spreadsheet and then treat that data accordingly in AutoCAD. In brief, the Layer Delegator
    sample application sends drawing blocks to new layers based on block attribute values. Blocks with a single
    attribute (indicating a numeric value) created on Layer 0 are compared with ranges specified in column A
    (minimum) and column B (maximum) of the spreadsheet, and then moved to a corresponding layer name in
    Column C.

www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    By changing the spreadsheet column values, you can delegate blocks to practically any Layering standard at any
    time with no entity picking whatsoever. Figure 3 shows the spreadsheet used for defining ranges and assigning
    layers. Figure 4 shows the interface for the application and three blocks originally created on Layer 0 converted to
    new layers based on their single attribute value. The application searches for all insertions of the block whose
    name is entered in the "Block name to treat" text box, and then assigns them to new layers based on what range
    they fall in.




    Figure 3: Minimum and maximum attribute values and layer.




    Figure 4: Application interface shown with blocks after conversion.


    Here's the code for this sample application:




    View Larger Image


    When you run this application, the block with value 34 is moved to layer100, the block with value 120 to layer200,
    and the block with value 270 to layer300. The layers of all three blocks could easily be reset to Layer 0, and you
    could set up new ranges with new layer names in the ranges.xls spreadsheet.

    Again, this sample application demonstrates how you can use a spreadsheet to analyze and modify AutoCAD
    entities for AutoCAD-based reporting. You could easily modify this application to control the new layer
    assignment color, line type, or even whether the block is visible. This functionality has strong potential for
    facilities management applications.



www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com
    Conclusion

    Some things aren't worth waiting around for when you can easily build them yourself. AutoCAD VBA, when
    combined with Microsoft Excel, can provide new and powerful ways to get work completed, to optimize AutoCAD
    performance and expand its capabilities, and to report information found in AutoCAD more intuitively.

    You can easily modify and/or adapt the sample applications presented in this series to meet your own office's
    unique work processes. Spend some time examining how you normally get work done on any given day and ask
    yourself if there are ways in which Excel might be able to give you a hand.

    Numerous VBA forums on the Internet these days are populated by AutoCAD VBA users willing to discuss the
    unlimited possibilities of automating your work. Check them out. If you have Microsoft Excel loaded on your
    AutoCAD system—put it to work!




www.cadfamily.com
The document is for study only,if any tort to your rights,Please inform us,we will delete it
Contact:cadserv21@hotmail.com

				
DOCUMENT INFO
About