Help for importing Excel data into MicroStation J files

Original Article Date: April 5, 2001

Read this article to find a few hints and tricks when trying to import Excel spreadsheet data into your MicroStation /J design files.
2001-04-05 fixed spelling errors.
2004-01-08 fixed broken links.

The following issues were complied from contributions made to comp.cad.microstation:

Many thanks to: John Hilton, Graham Cockburn, Kevin Knepper, Simon Wurster, Larry Whitt, and the rest of the group for posting these suggestions!

I'd also like to thank to Don Lynch and Pete Burkhimer for additional contributions to this article!

• The Windows Clipboard is limited in size; this will restrict the amount of data you can copy. One workaround is to copy/paste your data in "chunks". To help define these "chunks", you can define a range of cells in Excel and give them a name; you can then paste this named range of cells into your drawing. The size of these named ranges that windows will let you copy is hit and miss and is dependent upon such things as: cell size, gridlines, and the amount of text.

• If the font that you've used in Excel doesn't exist in MicroStations font library it will report an error message and use a default font.

• You can add configuration variables which will allow you to "map" the spreadsheet fonts to MicroStation fonts:
  MS_OLECNTR_FONTMAP_windows_font_name = MicroStation_Font_name

• When adding true type fonts via the Font Installer, ensure that the Character Set is set to 1250 Windows Latin. MicroStation has a vector based font, while the Windows True Type fonts are encoded in Unicode. Adjusting this setting ensures that the conversion takes place correctly.

• When editing, the spreadsheet might grow too large in Excel for the link to hold all the information. The may result in rows and/or columns dropping off in your drawing. To overcome, break the link in the design file, delete the graphics, and re-define the named chunk in Excel to a smaller size.

• If the text is at 90 Degrees in Excel, then on import into MS it will come in as text.

• OLE functionality does not work with rotated views.

• When placing the rectangle, try placing the point for the lower left corner first, then place the upper right.

• All text characters copied from Excel/Word into MicroStation using OLE will be separate text strings. To resolve this add or edit this variable as needed:
  
  MS_OLECNTR_CONFIG_UseInterCharacterSpacing = 1 for individual text characters
  
  MS_OLECNTR_CONFIG_UseInterCharacterSpacing = 0 for text strings

• The following variable will control how the data is imported:
  
  MS_OLECNTR_CONFIG_CREATEOBJECTSASCELLS = 1
  The data will come in as an orphan cell unless it exceeds the 128k cell size limit.
  When this occurs, the data to will be imported as lines and text.
  
  MS_OLECNTR_CONFIG_CREATEOBJECTSASCELLS = 0
  Forces the data to come in as lines and text.

• When importing spreadsheets that contain filled objects to represent a background colour, the fill may cover the text.
  Use these variables to control how the fill is handled:
  
  MS_OLECNTR_CONFIG_AllowFilledBackgroundText
  MS_OLECNTR_CONFIG_AllowFullImageFill

• To automatically launch or start the source application when you open a MicroStation file that contains a linked object, use the following configuration variable:
  MS_OLECNTR_CONFIG_ActivateAllObjectOnDgnLoad = 1

• Pete Burkhimer offers the following suggestion: "We gain a lot of flexibility and control over our linked Excel files by bringing them into an empty design file, which we use as a "container." We then attach the container file as a reference into the ultimate destination design file. This may be a little awkward..but the reward is that we get all the ease of moving, scaling, rotating, etc. associated with referenced files."

• Pete also mentions that "If the oft-mentioned 8.5" x 11" Windows size limit is a problem, why not pack a
  lot of cells into a spreadsheet using a tiny font, then just scale it up?"

• Still frustrated? Take a look at using QUANTAB authored by Tim Shoemaker. It's available from: http://www.geocities.com/tshoemaker/

• Another gem is SURGERY by John Chugh which you can get from Sean Forwards site at: http://members.optusnet.com.au/~forwardthinking/

AskInga Article #40