Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: VBA Format Number With Leading Zero

  1. #1
    Join Date
    23rd March 2006
    Location
    Canberra, Australia
    Posts
    27

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    2nd November 2005
    Location
    Tecumseh, OK
    Posts
    1,283

    Re: Format Number With Leading Zero

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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    23rd March 2006
    Location
    Canberra, Australia
    Posts
    27

    Re: Format Number With Leading Zero

    thanks for that, worked a treat

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 4 users browsing this thread. (0 members and 4 guests)

Possible Answers

  1. Number Format To Keep Leading Zeros
    By ScratchWeasel in forum EXCEL HELP
    Replies: 3
    Last Post: June 25th, 2008, 13:26
  2. Join Text With Number With Leading Zeros
    By dh273 in forum EXCEL HELP
    Replies: 4
    Last Post: July 25th, 2007, 19:36
  3. Retain Leading Zero in Custom Format
    By mini12 in forum EXCEL HELP
    Replies: 3
    Last Post: November 24th, 2006, 12:52
  4. Changing numbers in text format to number format.
    By holbrookp in forum EXCEL HELP
    Replies: 2
    Last Post: March 8th, 2005, 02:51
  5. Replies: 5
    Last Post: July 1st, 2003, 07:01

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno