Convert Date Stored as Text with VBA in many Workbooks

[prkhan56]

Hello All,
I am using Windows XP/Office 2003
I have many workbooks stored in C:\Temp
All these work books have date stored in mixed format in two columns
viz Column A and Column D as follows:


12/31/06 ----> stored as text
13/1/06
15/1/06
1/16/06 ----> stored as text
17/1/06
20/1/06


As can be seen from above data sample the dates are stored in mixed
format.

I have run the following macro which works for Column A only (for
Column D I need to change the Range every time and there are many
workbooks so it is very time consuming process

Sub test()
With ActiveSheet.Range("A1:A100")
..NumberFormat = "dd/mm/yyyy"
..Value = .Value
End With
End Sub

My requirement is to have a macro to open all the workbooks one after
another in Folder C:\Temp and run the macro on Sheet1 in Columns A and
D and convert the dates stored in mixed format to proper date format

Can this be achieved?

Thanks in advance

Rashid Khan

 

[Tom Ogilvy]

Sub test()
Dim sName as String
Dim sPath as String
Dim bk as Workbook
Dim rngA as Range, rngB as Range
sPath = "C:\Temp\"
sname = Dir(sPath & "*.xls")
do while sName <> ""
set bk = Workbooks.Open(sPath & sName)
With bk.worksheets(1)
set rngA = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
set rngD = .Range(.Cells(1,4),.Cells(rows.count,4).End(xlup))
End with
With rngA
.NumberFormat = "dd/mm/yyyy"
.Value = .Value
End With
With rngD
.NumberFormat = "dd/mm/yyyy"
.Value = .Value
End with
bk.Close SaveChanges:=True
sName = dir()
Loop
End Sub

 

[Mike Fogleman]

One small change to Tom's code, change this line:
Dim rngA as Range, rngB as Range
to
Dim rngA as Range, rngD as Range

Mike F

 

[prkhan56]

Sub test()
Dim sName as String
Dim sPath as String
Dim bk as Workbook
Dim rngA as Range, rngB as Range
sPath = "C:\Temp\"
sname = Dir(sPath & "*.xls")
do while sName <> ""
set bk = Workbooks.Open(sPath & sName)
With bk.worksheets(1)
set rngA = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
set rngD = .Range(.Cells(1,4),.Cells(rows.count,4).End(xlup))
End with
With rngA
.NumberFormat = "dd/mm/yyyy"
.Value = .Value
End With
With rngD
.NumberFormat = "dd/mm/yyyy"
.Value = .Value
End with
bk.Close SaveChanges:=True
sName = dir()
Loop
End Sub

--
Regards,
Tom Ogilvy














- Show quoted text -

Thanks Tom
Works great.....you are a great help always.
Also thanks to Mike for pointing out the typo.