No announcement yet.

VBA Format Number With Leading Zero

  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA Format Number With Leading Zero

    Hi All,

    I have the following script which is working fine execpt for its limited to running the loop 10 times. The macro is looking for files where the file extension could be anything between .P01 and .P99. Currently, the macro looks for file.P01 then file.P02 etc until it gets to file.P09 by increasing the value of 'i'. The trouble I am having is to get the variable 'i' to have a leading zero when the value is less than 10. Does anyone have any suggestions?

    Thanks heaps

    Sub rename_print_files()
    Dim OldName, NewName
    Dim i As Integer
    Dim strResult, strOldName, strNewName As String
    Range("E14").Activate ' set first file
    Application.DisplayAlerts = False
    i = 0
    Do Until ActiveCell = "" ' looping through all files
    strNewName = ActiveCell.Offset(0, 3)
    strOldName = ActiveCell
        Do While i < 11 ' loop through ten times then error message
        strResult = Dir(Range("E9") & strOldName & ".P0" & i, vbNormal)
          Select Case strResult
           Case ""  'Not exists
              i = i + 1
              If i = 10 Then
                Application.DisplayAlerts = True
                MsgBox ("ERROR: Unable to find Print File: " & strOldName & Chr(10) & _
                "File will be skipped")
                blnError = True   ' report error
                Exit Do
              End If
           Case Else 'Exists
              OldName = Range("E9") & strOldName & ".P0" & i: NewName = Range("E10") & strNewName & ".txt"   ' Define file names.
              Name OldName As NewName    ' Rename file.
              i = 12 ' exit loop
          End Select
     i = 0 ' reset count
     ActiveCell.Offset(1, 0).Activate ' start on next file
    Application.DisplayAlerts = True
    End Sub

  • #2
    Re: Format Number With Leading Zero

    Concatenate the i for the filename using format.
    Format(i, "00")


    • #3
      Re: Format Number With Leading Zero

      thanks for that, worked a treat