convert date format to text in all excel files in a folder

[farrukh]

Hi All,

i need a code that open all excel files change the specific cell date format to text format.

I have 100's of files it is difficult to open each file and convert date to text format.

Thanks
Farrukh

[nimrod]

What file extention are you xls files ? What sheets ? What cells ?

[farrukh]

Thanks for your reply
i have a file extention is .xls, Sheet1 only have the data and cell is I13 having the date in all excel files.

Thanks and Regards
Farrukh

[nimrod]

Public Sub UpdateFiles()
    Dim fs, f, f1, fc, s
    Dim Value1 As String
    Dim Value2 As String
    Dim Value3 As String
    
    msg = "Enter Full Path ...including ending slash !"
    Path = Application.InputBox(msg, "Update Files", "c:\example\")
    If Path = False Then Exit Sub
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(Path)
    Set fc = f.Files
    Col = 0
   
    For Each f1 In fc
    
    'NAME
    FullPath = f1.Path
    'EXTENSION
    FileExtention = fs.GetExtensionName(f1)
    
    If FileExtention = "xls" Then
       Workbooks.Open Filename:=FullPath
        
       If SheetExists("Sheet1") Then
          ActiveWorkbook.Sheets("Sheet1").Activate
          
          ' CHANGE FORMAT HERE
          Range("I13").NumberFormat = "@"
          
       End If
       
       ActiveWorkbook.Close SaveChanges:=True
    
    End If
    
    Next
End Sub

Private Function SheetExists(sname) As Boolean
'   Returns TRUE if sheet exists in the active workbook
    Dim x As Object
    On Error Resume Next
    Set x = ActiveWorkbook.Sheets(sname)
    If Err = 0 Then SheetExists = True _
        Else SheetExists = False
End Function

[farrukh]

Thanks a lot the code perfectly works only the problem i have while converting the date to text suppose the date is 01-jan-2009 (dd-mmm-yyyy) it convert it like that 39814, i need to convert the date as it is like 01-jan-2009.

Thanks
Farrukh

[nimrod]

Public Sub demo()

   ' CHANGE FORMAT HERE
        With Range("I13")
           .NumberFormat = "@"
           If IsNumeric(.Value) Then
              .Value = Format(.Value, "dd-mmm-yyyy")
           End If
        End With
        
End Sub

[farrukh]

Dear Nimrod

Thank you for your kind and nice support thanks alot