Announcement

Collapse
No announcement yet.

VBA Format Number With Leading Zero

Collapse
X
  • 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


    Code:
    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
        
        Loop
     i = 0 ' reset count
     ActiveCell.Offset(1, 0).Activate ' start on next file
    
    Loop
    
    Application.DisplayAlerts = True
    
    End Sub

  • #2
    Re: Format Number With Leading Zero

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

    Comment


    • #3
      Re: Format Number With Leading Zero

      thanks for that, worked a treat

      Comment

      Working...
      X