Need to pad a file or name (like Slide1) to have zero placeholders? Here is a quick REGEXP routine for VBA to PAD numbers in a string. Works in Excel and would work in a VBS as well.

Private Sub Test_Regexp_pad()
   Debug.Print Regexp_pad("9")
   Debug.Print Regexp_pad("12345")
   
End Sub
Private Function Regexp_pad(strFP As String) As String 'vALUE As String, Optional Pad As Integer) 'As String
  Dim r                                      '''As RegExp '''include "microsoft vbscript regular expressions 5.5" if debugging...
  Set r = CreateObject("vbscript.regexp")    '''Create Regexp
  
  If Pad = 0 Then Pad = 4                    '''If pad not specified they get 4
  
  r.Pattern = "(.*?)(\d+)(.)(.*)"                '''Items in () are returned in result set, objects without are dropped
  r.IgnoreCase = True
  
  Dim m                                      '''As MatchCollection   '''Holds the results. Nested (foo*(Bar*)) searches cause multiple m.item()
  Set m = r.Execute(strFP)
  
  On Error GoTo REGEXP_PAD_ERR               ''on bomb - return same value

   Dim i As Integer
   Dim intMyVal As Integer
   For i = 0 To m.Item(0).SubMatches.Count - 1
      If i <> 1 Then
         Regexp_pad = Regexp_pad & m.Item(0).SubMatches.Item(i)
      Else
         intMyVal = Int(m.Item(0).SubMatches.Item(i))
         'If Len(Trim(intMyVal)) > Pad Then Pad = Len(Trim(intMyVal))             ''if numbers exceed padding AND truncate
         Regexp_pad = Regexp_pad & Format(intMyVal, String(Pad, "0"))
      End If
   Next i
Exit Function
REGEXP_PAD_ERR:
   On Error Resume Next
   Regexp_pad = strFP
   'Err.Raise 1, , "Unsupported number padding type or type without number"
   
End Function

Comments

Popular posts from this blog

Revit area plans adding new types and references (Gross and rentable)

Organization and management of data in Revit across omniclass, Masterformat, Uniclass, and Uniformat