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)
         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
   On Error Resume Next
   Regexp_pad = strFP
   'Err.Raise 1, , "Unsupported number padding type or type without number"
End Function


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