Tag Extraction to Excel

Hi everyone just wondering if i can get some help on a code for extracting tag text to excel.

The code I have used is out of the Book Learning Microstation VBA by Jerry Winters

the code is listed below, the only things i have changed is the target tag to reflect my tag set and the folder location where i want it to look.

i get a Run Time Error '91' Object Variable or With Block Variable not set on the Set my folder  line 

any help would be much appreciated

Sub ExportTags()

Dim myDGN As DesignFile
Dim myFSO As Scripting.FileSystemObject
Dim myFolder As Scripting.Folder
Dim myFile As Scripting.File
Dim myTagSet As TagSet
Dim myTagDef As TagDefinition
Dim TargetTagset As String
Dim myTag As TagElement
Dim myElemEnum As ElementEnumerator
Dim myFilter As New ElementScanCriteria


'New Declarations
Dim myExcel As Excel.Application
Dim myWS As Excel.Worksheet
Dim CurRow As Long
'New Code
Set myExcel = New Excel.Application
myExcel.Visible = True
myExcel.Workbooks.Add
Set myWS = myExcel.ActiveSheet
CurRow = 2

TargetTagset = "WDG-A1"
Set myFolder = myFSO.GetFolder("C:\Users\Westech\Desktop\Testing Folder")

For Each myFile In myFolder.Files
Select Case myFile.Type
Case "Bentley MicroStation Design File"
'File Name and Merge Cells
myWS.Cells(CurRow, 1) = myFile.Path
myWS.Range("A" & CurRow & ":F" & CurRow).MergeCells = True
myWS.Range("A" & CurRow, "F" & CurRow).BorderAround _
, xlThick

'Header
CurRow = CurRow + 1
myWS.Range("B" & CurRow) = "Tag Set Name"
myWS.Range("B" & CurRow).Font.Bold = True
myWS.Range("C" & CurRow) = "Tag Name"
myWS.Range("C" & CurRow).Font.Bold = True
myWS.Range("D" & CurRow) = "Tag Value"
myWS.Range("D" & CurRow).Font.Bold = True
myWS.Range("E" & CurRow) = "ID High"
myWS.Range("E" & CurRow).Font.Bold = True
myWS.Range("F" & CurRow) = "ID Low"
myWS.Range("F" & CurRow).Font.Bold = True
CurRow = CurRow + 1

'Open the File
Set myDGN = Application.OpenDesignFileForProgram( _
myFile.Path, True)
For Each myTagSet In myDGN.TagSets
Select Case UCase(myTagSet.Name)
Case UCase(TargetTagset)
myFilter.ExcludeAllTypes
myFilter.IncludeType msdElementTypeTag
Set myElemEnum = myDGN.Models(1).Scan(myFilter)
While myElemEnum.MoveNext
Set myTag = myElemEnum.Current

'Write to Excel
myWS.Cells(CurRow, 2) = TargetTagset
myWS.Cells(CurRow, 3) = myTag.TagDefinitionName
myWS.Cells(CurRow, 4) = myTag.Value
myWS.Cells(CurRow, 5) = myTag.ID.High
myWS.Cells(CurRow, 6) = myTag.ID.Low
CurRow = CurRow + 1
Wend
End Select
Next
myDGN.Close
End Select

Next
End Sub
  • Without testing  I'm missing:

    Dim myFSO As NEW Scripting.FileSystemObject

    More info: http://support.microsoft.com/kb/186118/EN-US

    Regards

    Frank

    since 1985: GIS, CAD, Engineering (Civil)  Senior Consultant : [Autodesk Civil 3D , Esri ArcGIS, VertiGIS: in previous days : Bentley MS V4 - V8i, GeoGraphics, Bentley Map V8i, InRoads,  HHK Geograf, IBr DAVID] :  Dev: [C, C++, .NET, Java, SQL, FORTRAN, UML]
    [direct quote by: http://en.wikipedia.org/wiki/Helmut_Schmidt]: "Wer Kritik übel nimmt, hat etwas zu verbergen"
    Wer Grammatik- und/oder Rechtschreibfehler findet, der darf sie behalten :-)

  • VBA References

    Unknown said:
    Dim myFSO As Scripting.FileSystemObject
    Dim myExcel As Excel.Application

    External applications require a VBA Reference.  Use the Tools|References menu in the VBA Interactive Development Environment (IDE) to pop the References dialog.  You'll see a long list of objects that you can choose.  The object for the Microsoft Scripting Runtime is scrrun.dll, for example.

     
    Regards, Jon Summers
    LA Solutions

  • MicroStation Tag Tools

    Unknown said:
    Just wondering if I can get some help on a code for extracting tag text to Excel...

    MicroStation's built-in tag reporting tools let you create a CSV text file.  Excel can open a CSV file.  No programming required! The downside is that you have to fight your way through the MicroStation documentation about tag reports.

    MicroStation menu Element|Tags|Generate Reports pops the Tag Reports dialog.  This is initially mistifying, because it wants you to identify a report template (.tmp) file.  Make a report template from menu Element|Tags|Generate Templates, which pops the Generate Templates dialog.

    With the Generate Templates dialog open...

    1. Choose your tag set
    2. Choose those tags whose values you want to appear in the report
    3. Save the template

    Once you have a report template, you can generate a tag report from the Tag Reports dialog.

     
    Regards, Jon Summers
    LA Solutions

  • Hi Frank thanks for your help, it has stopped the run error But all i get is excel to open but it wont export the tag data. need to have a look at this in the new year. thanks for your help

  • Thanks Jon, I know that there is the tag export function but it doesn't seem to work very well in the new AECO Sim. Still having problems with my code the "AS NEW" suggested has stopped the run error i was getting but now it only opens excel but it does not export the data form the DGN files, will have to look at this in the new year, thanks for all your help

  • FlexiTable™ Exports Tags to Excel

    Unknown said:
    Just wondering if I can get some help on a code for extracting tag text to excel

    You can accomplish your task, without any programming, using FlexiTable.

    LA Solutions: FlexiTable

    FlexiTable can harvest all kinds of data, including tag values, from a MicroStation DGN model. You can save data in several formats, including a Excel workbook, HTML, XML and CSV.

     
    Regards, Jon Summers
    LA Solutions

  • Hi Dabid,

    i checked your code and it almost worked. Please see my modifications below.

    I identified a weakness in your code when it examines the file type. Maybe it would be better to compute the file extension and use the result in the Select-Case-Statement.

    Merry Christmas.

    Sub ExportTags()

       Dim myDGN As DesignFile

       'SB: Changed

       Dim myFSO As New Scripting.FileSystemObject

       Dim myFolder As Scripting.Folder

       Dim myFile As Scripting.File

       Dim myTagSet As TagSet

       Dim myTagDef As TagDefinition

       Dim TargetTagset As String

       Dim myTag As TagElement

       Dim myElemEnum As ElementEnumerator

       Dim myFilter As New ElementScanCriteria

       'New Declarations

       'SB: Changed

       Dim myExcel As New Excel.Application

       Dim myWS As Excel.Worksheet

       Dim CurRow As Long

       'New Code

       'SB: Changed

       'Set myExcel = New Excel.Application

       myExcel.Visible = True

       myExcel.Workbooks.Add

       Set myWS = myExcel.ActiveSheet

       CurRow = 2

       TargetTagset = "Sample"

       Set myFolder = myFSO.GetFolder("D:\Projekte\2013\BentleyForum_TagExport")

       For Each myFile In myFolder.Files

           'not sure if that is the best way to examine file type. What about file extensions

           Debug.Print myFile.Type

           Select Case myFile.Type

               'SB: Changed

               Case "Bentley MicroStation Design"

                   'File Name and Merge Cells

                   myWS.Cells(CurRow, 1) = myFile.Path

                   myWS.Range("A" & CurRow & ":F" & CurRow).MergeCells = True

                   myWS.Range("A" & CurRow, "F" & CurRow).BorderAround _

                                                    , xlThick

                   'Header

                   CurRow = CurRow + 1

                   myWS.Range("B" & CurRow) = "Tag Set Name"

                       myWS.Range("B" & CurRow).Font.Bold = True

                   myWS.Range("C" & CurRow) = "Tag Name"

                       myWS.Range("C" & CurRow).Font.Bold = True

                   myWS.Range("D" & CurRow) = "Tag Value"

                       myWS.Range("D" & CurRow).Font.Bold = True

                   myWS.Range("E" & CurRow) = "ID High"

                       myWS.Range("E" & CurRow).Font.Bold = True

                   myWS.Range("F" & CurRow) = "ID Low"

                       myWS.Range("F" & CurRow).Font.Bold = True

                   CurRow = CurRow + 1

                   'Open the File

                   Set myDGN = Application.OpenDesignFileForProgram( _

                                                   myFile.Path, True)

                   For Each myTagSet In myDGN.TagSets

                       Select Case UCase(myTagSet.Name)

                           Case UCase(TargetTagset)

                               myFilter.ExcludeAllTypes

                               myFilter.IncludeType msdElementTypeTag

                               Set myElemEnum = myDGN.Models(1).Scan(myFilter)

                               While myElemEnum.MoveNext

                                   Set myTag = myElemEnum.Current

                                   'Write to Excel

                                   myWS.Cells(CurRow, 2) = TargetTagset

                                   myWS.Cells(CurRow, 3) = myTag.TagDefinitionName

                                   myWS.Cells(CurRow, 4) = myTag.Value

                                   myWS.Cells(CurRow, 5) = myTag.ID.High

                                   myWS.Cells(CurRow, 6) = myTag.ID.Low

                                   CurRow = CurRow + 1

                               Wend

                       End Select

                   Next

                   myDGN.Close

           End Select

           Next

    End Sub

  • Thanks Stefan,

    I have managed to get this working, but you are correct I need to specify the file extension as the only files that it reports the tag sets on is .hln files and doesn't do any of the .dgn files. but i cant work out how to call it out.

    Has anyone got any suggestions??

    Sub ExportTags()

      Dim myDGN As DesignFile

      Dim myFSO As New Scripting.FileSystemObject

      Dim myFolder As Scripting.Folder

      Dim myFile As Scripting.File

      Dim myTagSet As TagSet

      Dim myTagDef As TagDefinition

      Dim TargetTagset As String

      Dim myTag As TagElement

      Dim myElemEnum As ElementEnumerator

      Dim myFilter As New ElementScanCriteria

      Dim myExcel As New Excel.Application

      Dim myWS As Excel.Worksheet

      Dim CurRow As Long

      myExcel.Visible = True

      myExcel.Workbooks.Add

      Set myWS = myExcel.ActiveSheet

      CurRow = 2

      TargetTagset = "WDG-A1"

      Set myFolder = myFSO.GetFolder("C:\Users\Westech\Desktop")

      For Each myFiles In myFolder.Files

          'not sure if that is the best way to examine file type. What about file extensions

          Debug.Print myFiles.Type

          Select Case myFiles.Type

              'SB: Changed

              Case "Bentley MicroStation Design"

                  'File Name and Merge Cells

                  myWS.Cells(CurRow, 1) = myFiles.Path

                  myWS.Range("A" & CurRow & ":F" & CurRow).MergeCells = True

                  myWS.Range("A" & CurRow, "F" & CurRow).BorderAround _

                                                   , xlThick

                  'Header

                  CurRow = CurRow + 1

                  myWS.Range("B" & CurRow) = "Tag Set Name"

                      myWS.Range("B" & CurRow).Font.Bold = True

                  myWS.Range("C" & CurRow) = "Tag Name"

                      myWS.Range("C" & CurRow).Font.Bold = True

                  myWS.Range("D" & CurRow) = "Tag Value"

                      myWS.Range("D" & CurRow).Font.Bold = True

                  myWS.Range("E" & CurRow) = "ID High"

                      myWS.Range("E" & CurRow).Font.Bold = True

                  myWS.Range("F" & CurRow) = "ID Low"

                      myWS.Range("F" & CurRow).Font.Bold = True

                  CurRow = CurRow + 1

                  'Open the File

                  Set myDGN = Application.OpenDesignFileForProgram( _

                                                  myFiles.Path, True)

                  For Each myTagSet In myDGN.TagSets

                      Select Case UCase(myTagSet.Name)

                          Case UCase(TargetTagset)

                              myFilter.ExcludeAllTypes

                              myFilter.IncludeType msdElementTypeTag

                              Set myElemEnum = myDGN.Models(1).Scan(myFilter)

                              While myElemEnum.MoveNext

                                  Set myTag = myElemEnum.Current

                                  'Write to Excel

                                  myWS.Cells(CurRow, 2) = TargetTagset

                                  myWS.Cells(CurRow, 3) = myTag.TagDefinitionName

                                  myWS.Cells(CurRow, 4) = myTag.Value

                                  myWS.Cells(CurRow, 5) = myTag.ID.High

                                  myWS.Cells(CurRow, 6) = myTag.ID.Low

                                  CurRow = CurRow + 1

                              Wend

                      End Select

                  Next

                  myDGN.Close

          End Select

          Next

    End Sub

  • New Topic: New Thread

    Your question has little to do with this thread's title about Tags.  I suggest that you post a new question.

    Unknown said:

    Set myFolder = myFSO.GetFolder("C:\Users\Westech\Desktop")
    For Each myFiles In myFolder.Files
      Debug.Print myFiles.Type

    You're getting all the files in C:\Users\Westech\Desktop.  Those can be any file type.  The Debug.Print statement tells you what you've found.  You could add other members of MyFiles (e.g. MyFiles.Name) to that print statement to clarify what you're getting.

    Unknown said:

    Select Case myFiles.Type
    Case "Bentley MicroStation Design"

    That test ensures that you're working only with those files whose type Windows believes to be Bentley MicroStation Design.  I don't see how that can result in only .hln files, unless there are no .dgn files in that folder.  If you examine that folder, what files are present?

    Microsoft File System Object

    You can find out more about the File System Object.

     
    Regards, Jon Summers
    LA Solutions