change the format of a column in a excel spredsheet

Hi Experts, I have a excel SS that is created and one of the fields is coming out as mm/dd/yy how can I write a macro to change that columns format to yyyymmdd. Thanks for any help.
needhelpfast569Asked:
Who is Participating?
 
helpfinderConnect With a Mentor IT ConsultantCommented:
Unfortunately this I do not know. But I know how to run macro when that file is open, if it is enough for you.

You just open that Visual Basic and double click ThisWorkBook. From left drop down menu where General is by default choose Workbook and from right drop down menu choose Open. I generates a part of code for you. BEtween the lines you will call the macro I posted above, so it will looks like:
Private Sub Workbook_Open()
Call FormatChange
End Sub

This makes macro to run every time file is opened

I am attaching also sample where this is working (try to change dates in A1-A3 e.g. to 1.1.1960, 12.4.1984 etc, save the file, close it and reopen.
format-date.xlsm
0
 
helpfinderIT ConsultantCommented:
Hi, do you need to perform this only with macro for some special reason?
because you can simple format cell(s) and set custom format yyyymmdd
0
 
needhelpfast569Author Commented:
the file is being created from access and the file is created as a new file each time and overwrites the existing file. I tried that one.
0
 
helpfinderIT ConsultantCommented:
so try this macro

Sub FormatChange()
    Range("A1:A3").Select
    Selection.NumberFormat = "yyyymmdd"
End Sub
0
 
needhelpfast569Author Commented:
Hi helpfinder, that works great do you know how I can set that macro to run after the file is built from ms access?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.