VBA to Generate Schematics from Excel

Hi,

because you want to develop it in VB and if you want to use MicroStation V8i or higher, you are in the right forum :-)

The "problem" is, that there is no question in your post - it is only information, what you want to do, so we can only assume your questions:

Can it be done? I am sure yes.

Can it be done in VB? I think yes, I personally prefer VBA, but both languages will be sufficient.

Is it easy task? Well, it depends how complex the drawings will be. If it will be only about to find text and to replace it with the appropriate cell value, it sound like simple task.

What is your knowledge of VB/VBA, MicroStation and MicroStation VBA development?

With regards,

 Jan

MicroStation Tag Elements

Unknown said:

I would like to replace all text values from that template by the corresponding text in the Excel file.

I recommend that you investigate MicroStation Tag Elements.

Locating and updating MicroStation text can be difficult, for various reasons. Tags provide structured data within a DGN model, similar to a table in a database. You can search for a named tag and update its value using VBA.

This article about Reading and Writing Tag Data may be useful.

Regards, Jon Summers
LA Solutions

Hello Jan,

Thank you for taking interest in my post.

Effectively, I forgot the most important...the question!!

so the question is, where do I start?

To answer your points:

   - I know it is possible cos i have seen something similar made in BASIC (compile so can not read the code)

   - I have microstation V8i.

   - The routine is only about opening the correct 'template' and find text and replace it with appriopriate cell value.

   - knowledge in Microstation development is nul.

   - Pretty good level in Excel Macros development.

If I could get the code to start the declaration and variable and the listing of the object models for Microstation...

I should be able to manage the loop after that

Thank you for you tips and advised.

Realise as well I never signed!

Shame on me!

Heve

Thank you Jon,

I will investigate that way.

Hello again,

I had a lood at Tag Elements but not familiar and bit tricky.

feel more confortable with a find replace function...

Thanks in advance

Herve

Herve,

If you have experience with Excel VBA, then this MicroStation VBA code sample should get you started. Good luck.

Sub ReplaceText()
    Dim ee As ElementEnumerator
    Dim es As ElementScanCriteria
    Dim elArray() As Element
    Dim i As Long
    Dim iStart As Long
    Dim iEnd As Long
   
    '
    ' set element scan criteria to find only text elements
    '
    Set es = New ElementScanCriteria
    es.ExcludeAllTypes
    es.IncludeType msdElementTypeText
   
    '
    ' set enumerator from active model
    '
    Set ee = ActiveModelReference.Scan(es)
   
    '
    ' get an element array of all elements found
    '
    elArray = ee.BuildArrayFromContents
    iStart = LBound(elArray)
    iEnd = UBound(elArray)
   
    '
    ' loop through array and replace text
    '
    For i = iStart To iEnd
        If StrComp(elArray(i).AsTextElement.Text, "$MOTOR No$", vbTextCompare) = 0 Then
            elArray(i).AsTextElement.Text = "PM-01A"
            elArray(i).Rewrite
        End If
    Next
   
End Sub

Hello Rod,
I am busy these days and was not able to get in touch earlier.
Thank you for the code above. I manage to make the loops and I have now to clean a bit the code as it contains lots of garbage.

To summarize, the Excel macro does:
  - Loop in Column B,
  - Open the template,
  - Save Template as value in Column A.

  - Loop in Row 1,
  -  Replace Text on Row 1 by text in Row xx

Now, everything is working very well with Microstation V8 (bit slow but working) but with Microstation J, I have a "Runtime error '70' - Permnission Denied" at the line code:    elArray(i).AsTextElement.Text = cell3.Value.

If anyone have an idea?

Here below is the Code I have:

Sub Open_DGN()

Dim oCell As CellElement
Dim oScanCriteria As ElementScanCriteria
Dim dFile As DesignFile
Dim curCell As Object
Dim MyDir As String
Dim strPath As String
Dim svPath As String
Dim Path As String
Dim LastRow As Long, LastCol As Long
Dim iRow As Long, iCol As Long
Dim cell1, cell2, cell3 As Range

''--- SET THE SCAN CRITERIA ---"
    Dim startPoint As Point3d
    Dim point As Point3d, point2 As Point3d
    Dim lngTemp As Long

    LastRow = Range("B65536").End(xlUp).Row             ' Last cell in Column B
    LastCol = Range("A1").End(xlToRight).Column         ' Last cell in Row 1

''--- Loop until last Cell in Column B ---''
    For iRow = 2 To LastRow
        Set cell1 = Cells(iRow, 2)
            cell1.Select

        MyDir = ActiveWorkbook.Path                                                    ' Current path
        strPath = MyDir & "\" & cell1                                                       ' Template name

        Set dFile = OpenDesignFile(strPath, False)                         ' Open Template Name
'        Set oScanCriteria = New ElementScanCriteria
        svPath = MyDir & "\" & cell1.Offset(0, -1)
        dFile.SaveAs (svPath)                                                                 ' Save Template Name as...

''   Start a command
'    CadInputQueue.SendCommand "MDL KEYIN FINDREPLACETEXT CHANGE DIALOGTEXT"

LastCol = Range("A1").End(xlToRight).Column            ' Last cell in Row 1
''--- Loop until last Cell in Column B ---''
    For iCol = 2 To LastCol
        Set cell2 = Cells(1, iCol)
        Set cell3 = Cells(iRow, iCol)
            cell3.Select
   
''--- Loop through array and replace text ---''
''--- Set element scan criteria to find only text elements ---''

Set es = New ElementScanCriteria
es.ExcludeAllTypes
es.IncludeType msdElementTypeText

''--- Set enumerator from active model ---''
Set ee = ActiveModelReference.Scan(es)
''--- Get an element array of all elements found ---''
elArray = ee.BuildArrayFromContents
iStart = LBound(elArray)
iEnd = UBound(elArray)

For i = iStart To iEnd
      If StrComp(elArray(i).AsTextElement.Text, cell2.Value, vbTextCompare) = 0 Then
            elArray(i).AsTextElement.Text = cell3.Value
            elArray(i).Rewrite
      End If
Next

   
   
   Next iCol
   Next iRow

End Sub

VBA is V8 Technology

Unknown said:

with Microstation J, I have a "Runtime error"

That's easy to understand: VBA was introduced with MicroStation V8 in 2001. MicroStation/J does not include VBA.

Regards, Jon Summers
LA Solutions

VBA is V8 Technology

Unknown said:

with Microstation J, I have a "Runtime error"

That's easy to understand: VBA was introduced with MicroStation V8 in 2001. MicroStation/J does not include VBA.

Regards, Jon Summers
LA Solutions

Thank you Jon, this is why in the Excel VB Help, there is the reference to V8 then.

However, why some part of the code above is working with Version J ?

i.e. If I remove the following part of code,  the rest of the process works perfectly:

   For i = iStart To iEnd

       If StrComp(elArray(i).AsTextElement.Text, cell2.Value, vbTextCompare) = 0 Then

           elArray(i).AsTextElement.Text = cell3.Value

           elArray(i).Rewrite

       End If

   Next

Thanks for your support.

Regards Herve