VBA Format Number With Leading Zero

#VBA Format Number With Leading Zero | 来源: 网络整理| 查看: 265

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 Display More

【本文地址】

公司简介

联系我们