Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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


    VB:
    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,082

    Re: Format Number With Leading Zero

    Concatenate the i for the filename using format.
    VB:
    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 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Number Format To Keep Leading Zeros
    By ScratchWeasel in forum EXCEL HELP
    Replies: 3
    Last Post: June 25th, 2008, 12:26
  2. Join Text With Number With Leading Zeros
    By dh273 in forum EXCEL HELP
    Replies: 4
    Last Post: July 25th, 2007, 18:36
  3. Retain Leading Zero in Custom Format
    By mini12 in forum EXCEL HELP
    Replies: 3
    Last Post: November 24th, 2006, 11:52
  4. Changing numbers in text format to number format.
    By holbrookp in forum EXCEL HELP
    Replies: 2
    Last Post: March 8th, 2005, 01:51
  5. Replies: 5
    Last Post: July 1st, 2003, 06: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