Questions similar to this appear on the Bentley Discussion Groups. These problems appeared in the VBA discussion group.

• How do I do xxx in Excel with MVBA?
• How do I copy data to/from Excel with MVBA?
• How do I display an Excel worksheet in MicroStation using MVBA?
• Are there any text books that deal with Excel VBA programming?

Q How do I do xxx in Excel with MVBA?

A Like MicroStation, Microsoft Excel™ has its own implementation of Visual Basic for Applications. Excel has included VBA for many years over several versions. Even today, with Microsoft tempting us with .NET, Excel (and each of the other Microsoft Office applications) continues to include VBA.

With Excel installed on your computer, you can create a reference to its object library (the same applies to any other application that implements VBA).

With MicroStation's VBA Interactive Design Environment (IDE) open, take the following steps …

1. Use the Tools|References menu to pop the References dialog
2. Browse the list to find your installed version of Microsoft Excel
3. Click the check box to instruct VBA to reference this library
4. Click OK

Once you've referenced the Excel.Application, you have access to all its properties and methods. With the IDE open, press the F2 key (or Edit|View Object Browser) to pop VBA's Object Browser dialog. In the Object Browser, you can focus on the properties or methods of any referenced object …

Q How do I display an Excel worksheet in MicroStation using MVBA?

A You could write some code around something like a DataGrid component, and make it look like an Excel worksheet. You still have the problem of importing and exporting data from Excel.

The simplest, quickest, and easiest route is to purchase a plug-in spreadsheet component. For example, GrapeCity PowerTools Spread COM. A plug-in component costs you a license fee, but that's a lot cheaper than writing your own.

Q How do I copy data to/from Excel with MVBA?

A The simplest, quickest, and easiest route is to purchase a plug-in spreadsheet component. For example, GrapeCity PowerTools Spread COM. A plug-in component costs you a license fee, but that's a lot cheaper than writing your own.

Excel has a hierarchy of objects, starting with the Workbook, which corresponds to an Excel file, through the Worksheet, to the Range. It is the Range object that is the gateway to working with Excel data. A Range can refer to a single Excel cell or a range of cells. The range can be a row, column or block of cells, up to an entire worksheet.

I'm not providing an Excel VBA tutorial here: there are plenty of developers' web sites that can help you.

Q Are there any books about Excel VBA programming?

A There are some text books for Excel VBA programmers mentioned here.

Q How do I do xxx in Excel with MVBA?

A Here's a fragment of code that show how to create an Excel.Application object reference and use it to open a workbook …

Option Explicit

' ---------------------------------------------------------------------
'   Example MicroStation VBA code to create and use an
'	Excel application object
' ---------------------------------------------------------------------
'
Sub ExampleExcelReference()
   Dim oExcel As New Excel.Application
   Dim oWorkbook As Excel.Workbook
   Set oWorkbook = oExcel.Workbooks.Open("your-workbook.xls")
   Set oExcel = Nothing
End Sub