List All Stencil Names in Folder in Excel

Started by Luke.Batty, February 20, 2018, 04:37:02 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Luke.Batty

I am looking for a way to loop through all vss stencil files in a folder and put all the names of the masters from within those files into a excel spreadsheet with the name of the stencil file next to it

Visio Guy

Hi Luke,

This code, when pasted into Excel's VBA and run from Excel will list the stencils in a directory on the active worksheet.

Note: the code will clear the contents of columns 1 and 2 of the active Excel sheet, so make sure you've got the right worksheet active!



Option Explicit

Public Sub ListStencilsInTemplate()

  '// Set the target folder path:
  Dim sFolder As String
  sFolder = "C:\Users\YOUR_USER_NAME\Documents\My Shapes\" '// <-- user sets this
 
  '// Filter out files:
 
  '// All Visio stencils:
  Const sAllowableExtensions$ = "vss,vssx,vssm,vsx" '// <-- user sets this
 
  '// Visio 2013 and newer stencils:
  'Const sAllowableExtensions$ = "vssx,vssm"
 
  '// Visio 2010 and older stencils only:
  'Const sAllowableExtensions$ = "vss"
 
 

  '// Note, this doesn't appear to recurse subdirectories.
  '// The flag VbFileAttribute.vbDirectory exists to
  '// traverse directories.
 
 
  Dim exts() As String
  exts() = Split(LCase(Replace(sAllowableExtensions$, " ", "")), ",")
 
  '// Get the active Excel worksheet:
  Dim xlSht As Excel.Worksheet
  Set xlSht = Excel.Application.ActiveSheet
 
  '// CLEAR contents of columns 1 and 2:
  xlSht.Columns(1).ClearContents
  xlSht.Columns(2).ClearContents
 
  '// Write folder path and current date-time:
  xlSht.Cells(1, 1).Value = "Folder:"
  xlSht.Cells(1, 2).Value = sFolder
  xlSht.Cells(2, 1).Value = "Date:"
  xlSht.Cells(2, 2).Value = Format(Now, "YYYY.MM.DD - HH:mm:ss")
 
  Const FirstFileRow% = 3
 
 
  '// Get the first file using the Dir() command:
  Dim sFile As String
  sFile = Dir(sFolder, VbFileAttribute.vbNormal)
 
  Dim bContinue As Boolean: bContinue = True
  Dim iRow As Integer: iRow = 1
  While bContinue = True
 
    If (sFile = vbNullString) Then
        bContinue = False
    Else
   
      '// Check the file extension:
      Dim ext As String
      ext = LCase(Right(sFile, Len(sFile) - InStrRev(sFile, ".")))
     
      Dim bExtOk As Boolean: bExtOk = False
      Dim iExt As Integer
      For iExt = LBound(exts) To UBound(exts)
        If (ext = exts(iExt)) Then
          bExtOk = True
        End If
      Next iExt
     
     
      If (bExtOk) Then
       
        '// List the file:
        Debug.Print iRow & ". " & sFile
       
        '// Dump to Excel:
        xlSht.Cells(FirstFileRow% + iRow, 1).Value = iRow
        xlSht.Cells(FirstFileRow% + iRow, 2).Value = sFile
       
        '// Increment our count:
        iRow = iRow + 1
       
      End If
     
      '// Get the next file with this weird-o call:
      sFile = Dir
       
    End If
   
  Wend
   
  Set xlSht = Nothing

End Sub
For articles, tips and free content, see the Visio Guy Website at http://www.visguy.com
Get my Visio Book! Using Microsoft Visio 2010

Luke.Batty

Hi,

Thanks for the reply, this is good in the fact that list all files but I would also like to list all the objects within the vss files

Thanks

Visio Guy

I edited your first question to reflect that you want to list all masters in all stencils, not just list stencils.

You would have to open each file in Visio, then list the masters. Keep in mind a lot of masters are hidden because they are used for custom patterns and data graphics.

Generally:


Dim path As string
path = "some filepath.vss"

Dim stn As Visio.Document
Set stn = Visio.Application.Documents.Open(path)

Dim mst As Visio.Shape
For Each mst In stn.Masters
  '// Excel code goes here, instead of the debug dump:
  Debug.Print mst.Name
Next mst

For articles, tips and free content, see the Visio Guy Website at http://www.visguy.com
Get my Visio Book! Using Microsoft Visio 2010