Saturday, August 11, 2012

DXFert: An Example VBA Add-In with Custom Ribbon

I implemented this project in Excel 2010.  Only the Ribbon modifications are likely to be version dependent (unless you go way, way back).  The form and macro in this project should work in any version of Excel.  

To use the macro launchmyForm(control as IRibbonControl)without the Ribbon buttons, such as you would need to do in an earlier version of Excel, you will need to either change

   launchmyForm(control As IRibbonControl) to launchmyForm() 


write another macro that calls launchmyForm(someDummyObject).

You can download DXFert here to see an example (my previous post explains how to install an Excel Add-In in Excel 2010).  In this post, I am going to walk through the building blocks of the project.  Except for Excel, everything you need for this project is freely available for download.

What You Will Need to Develop Your Own Similar Project
  1. Excel 2007 or later.
  2. DXF file format documentation from AutoDesk
  3. CustomUI Editor Tool from Open XML Developer
  4. A program to allow you to test your DXF output (if that's what you're going for), such as DWG TrueView.

You need to 
  1. create a UserForm within a macro-enabled workbook (*.xlsm)
  2. write a one-liner macro that invokes the UserForm
  3. create a Ribbon interface which references the one-liner macro, and, finally,
  4. save the project as an Add-In (*.xlam) and activate it.
Creating the UserForm

If you don't have the developer tab available to you,...why ever not?  Turn the silly thing on!  (See here for instructions.)  To learn about developing user forms in Excel generally, there are lots of examples on the web, but I'll just give you a few key pieces of code that were important in my project.

Here's the code I used to open a file SaveAs dialog to help the user indicate the name and path for the file that the results will be exported:

This should work in very early versions of Excel all the way up to the present (version 2010) and probably future versions as well.  My main beef with it is there is a lack of control.  I would like it to complain about the selection of a file which already exists, but if I really wanted to I could include that complaint in my macro code right before I follow through with the export (which I didn't do—but you can, if you wish).

When you want to terminate the form (either by cancelling or after successful completion), you'll want use something like this:

You can learn a lot about the DXF format by looking at my code in the download.  (Hint:  If you have the Add-In added, you don't need to open up the file separately to see the code.  Just go to the Visual Basic Editor (button on the developer tab) and locate the user form DXFout_Form.  Right-click it and choose View Code.)  See page 143 of the AutoDesk's DXF format documentation for a description of the fields in the polyline Entity.

The one-liner code which the Ribbon will call is pretty easy:

The Ribbon expects to pass this control parameter to the subroutines it calls, whether you need to use the parameter or not.

Create the Ribbon Interface

I overcomplicated this my first few tries at it.  Which is what a lot of people on the internet do.  If you want your add-in to have its own ribbon elements, you create those elements using CustomUI Editor (referred to above) within a regular *.xlsm file.  When you are ready to finalize it as an Add-In you just save it as an add-in and activate it.  The same custom UI that showed up in the original *.xlsm file, will then show up all the time.  No fuss, no muss.  No Workbook_AddinInstall() code, no SetCustomUI(longComplicatedXMLString), no nothing, it just works!  You do have to look at a bit of XML code, mind you.

But first, close down your *.xlsm file and open up CustomUI.  Open up your *.xlsm file from within CustomUI.  Go to the Insert menu and select Office 2010 Custom Part and, likewise, Office 2007 Custom Part.  Now it is time for something ugly.  Here is the xml code I used in my project:

For 2010 (customUI14):

For 2007 (customUI):

If you look at the <mso:button> tag you'll notice the launchMyForm bit.  That's the name of the procedure that is to be called.  One parameter is passed, namely, the a reference to the ribbon control that called the subroutine.

Save As an Add-In

At any point, you can turn your *.xlsm project into an Add-In.  (You can even continue editing it while it is an Add-In.)  Go to File, Save As, and select Excel Add-In from the file type options in the SaveAs dialog box.  My previous post talks about installing and activating an Excel Add-In.

Saturday, August 4, 2012

DXFert: Creating a DXF file in Excel

DXF files are a means of providing interoperability between AutoCAD and other programs.  AutoCAD can read and write *.dxf files and so can a number of other programs.  It is fairly straightforward to write a program which outputs a DXF file—provided you just want to draw something simple.  DXF files are a great option for auto-generating a bunch of drawings which can then be opened up in a CAD program and edited and saved in that program's native format.

I have written an Excel Add-In, DXFert (say, D-X-F-ert), in VBA which outputs a selection of points as either a 2D or 3D polyline.  It has been tested in 2010, but hopefully works in many earlier versions.  Here's how to install and use the add-in.

How to Install DXFert
  1. Download DXFert from my google shared document found here.
  2. Open Excel.
  3. Go to File → Options
  4. Select Add-Ins
  5. Click the Go... button beside Manage |Excel Add-Ins|.
  6. Click Browse... and navigate to the download location of the add-in and select the file (DXFert.xlam).
  7. Click the check box beside DXFert and click OK.
You should now see a DXF tab.

How to Use DXFert

Using DXFert is straightforward.  First you'll need to create two or three columns of numbers which represent the x, y, (and z) coordinates of the vertices of the would-be polyline.  Having so done:
  1. Optionally, pre-select the cells (headings, if any, not included) which contain the data you want to export as a polyline.
  2. Go to DXF → Export to DXF.
  3. In the dialog, select whether you want to export as 2D or 3D polyline.
  4. If you have not already selected the relevant cells, click the button beside the cell range input box and select the cells (when you have made your selection, you need to click the button on the right hand side of the window that came up for the cell reference selection to get back to the DXFert dialog).  Alternatively, type the cell reference.  You must choose a simple contiguous selection. I haven't added the smarts to the program to do anything more intelligent or convenient (so far anyway).
  5. Type a file name to save the exported file as or browse to a file name.  (NOTE:  DXFert is not careful about overwriting files so make sure you give your file a new name if you don't want to overwrite an existing file.)  It is in your interest (normally) to make sure you give the file a .dxf extension (which the Browse... option enforces).  
  6. Click Export.  The file will be created silently and the dialog will close.
How to View DXF Files

Several programs open DXF files including several AutoDesk programs (like AutoCAD) and some free viewers.  AutoDesk has their own free viewer program:  DWG TrueView.

Friday, August 3, 2012

AutoDesk Design Review 2013 (0x2: Failed to download component.)

I said yes to updating my Design Review (2011) to the current version (2013) and got the above error message.  The solution in my case was to download the MSI file.  (Note:  click on the language you want under the Choose Your MSI column after instruction 2 at the linked location.)

After unzipping the .zip file you can just run the Setup.exe file.  The principle difference is that the downloading has already taken place and the setup program will look at your local or network location for the relevant files to install.

Now, to use Design Review 2013 without AutoCAD installed, you need to install TrueView.  If you have a version of AutoCAD installed on your computer which is temporarily without a current license (but it is installed on your computer), you'll need to explicitly tell Design Review to use TrueView.

To do so:

Within Design Review 2013, go to the main drop down menu (big "D" in top-left)
Options...(at the bottom middle right of drop down)
→ Click on the DWG Viewing tab
→ and check the box beside Open DWG files using DWG TrueView
→ Click OK.