How to change date format in VBA
Glad you got it working--sorry about the typo.
Frank wrote:
Thank you for another solution that also worked just fine. (I only had to
change the last End With to End If)
"Dave Peterson" wrote:
Record a macro when you select that column of cells that look like 13.07.2006.
Then use data|text to columns
fixed width
Date format (dmy)
and then change the format to yyyymmdd
And you'll have your code that looks kind of like:
Option Explicit
Sub ChangeDateFormat()
Dim strFileName As Variant
Dim wkbk As Workbook
strFileName = Application.GetOpenFilename("Report (*.xls),*.xls")
Application.ScreenUpdating = False
If strFileName < False Then
Set wkbk = Workbooks.Open(Filename:=strFileName)
With wkbk.Worksheets(1) 'do you know the name of the worksheet?
With .Range("a:a")
.TextToColumns Destination:=.Cells(1), _
DataType:=xlFixedWidth, FieldInfo:=Array(0, 4)
.NumberFormat = "yyyymmdd"
End With
End With
End With
End Sub
Frank wrote:
I want to change the date format in VBA from "13.07.2006" to "20060713".
If I try to record a macro applying the Format, Format cells... function,
nothing happends.
The excel workbook has date values in column A with the wrong format
"13.07.2006". The correct format is "20060713".
I try to open the workbook and create a new column B with correct date format.
Then I try to overwrite column A with a pasteSpecial command.
When I run the macro, I return runtime error 1004.
Any suggestions to make this work?
Regards
Frank Krogh
Sub ChangeDateFormat()
strFilename = Application.GetOpenFilename("Report (*.xls),*.xls")
Application.ScreenUpdating = False
If strFilename < "False" Then
Workbooks.Open strFilename
Columns("B:B").Select
Application.Workbooks(Workbooks.Count).Activate
With Selection
.Formula = "=CONCATENATE(LEFT(A,7,4),(LEFT(A,1,2),(LEFT(A,4,2 ))"
End With
Columns("B:B").Select
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End Sub
--
Dave Peterson
--
Dave Peterson
|