Writing VBA Reports to MS Excel or Word

Every so often, I get an email from Autodesk TechNews – it’s called TechFAQ. I get it for Mechanical, Architectural, Civil, and Platform (that’s plain-jane AutoCAD for anyone who didn’t know) technologies. I promptly delete all but the Civil TechFAQ’s and then browse to see if there’s something in there that would be useful to me. Typically, it’s mostly old news, but one that I received about a week ago made me think. So, I’m filing it here – mainly for my own documentation, but hopefully someone else will get some use out of it.

So, how many of you use the reporting feature inside the Toolbox in Civil 3D? As you probably have seen, there are two types of reports – VBA and XML. Both reports open in Internet Explorer. And, if you’re like me, you have to tell IE that yes, this is trusted data before it will display. I often get asked “Why IE? Why not Word (or Excel?)” I typically tell people to just copy and paste, but here’s an easier solution. NOTE: This is only for VBA reports, not XML reports. Don’t ask me how to change the display feature of XML reports, ’cause I don’t know…

Click more to find out how it’s done.

First, type VBALOAD at the command line. In the following dialog box, browse to the C:\Program Files\Autodesk Civil 3D 2007\Data\Reports\VBA directory, then select the report that you want to edit and click Open. VERY IMPORTANT NOTE: Please copy the file that you want to edit and save the copy with a new name – this way you always have a backup in case you hose a report. You might get a warning telling you that custom macros can be bad, but you already know this, so click OK to allow them in your file.

Next, at the command prompt, enter VBAIDE. This will open the Microsoft Visual Basic Editor. Expand the Forms folder, select the report that you want to edit and right click, then selct View Code. Your window should look like the following:

Next, go to the Edit menu and click Find. In the Find What field, type ExecuteButton, then select Find Next.

Now we get to the editing – in the ExecuteButton section, scroll down to the bottom and look for the following string: “exeStr = exeStr + “\Internet Explorer\iexplore.exe” & g_reportFileName” Once you find that string, change it to the following: “exeStr = exeStr + “\Microsoft Office\OFFICE11\Excel.exe” & g_reportFileName” (if this isn’t the path to your excel.exe file, enter your path to that file as indicated.) If you’d like to use Microsoft Word instead of Excel, you should change Excel.exe to WinWord.exe

Here are the finished results:

Note: as of press time, this only works for Civil 3D 2007

Be careful with this, and have fun!

Comments are closed.