Help code format column Date/time with VBA for multiple file!!!

[Nguyen Anh Dung]

As picture below. Column A with format mm:ss.0 i want change format "yyyy-mm-dd hh:mm:ss" with multiple file *.xls,*.csv.

 

[Joe4]

So what exactly is the problem?
What can't you just change the format of the column to the format that you want?

 

[Nguyen Anh Dung]

So what exactly is the problem?
What can't you just change the format of the column to the format that you want?

if I change I have to open a lot of files and change the format "yyyy / mm / dd hh: mm: ss" for each file. I want to help the code run and change without having to open each file.

 

[Joe4]

Is it the same column in every xls and csv file in a particular folder?

 

[Nguyen Anh Dung]

if I change I have to open a lot of files and change the format "yyyy / mm / dd hh: mm: ss" for each file. I want to help the code run and change without having to open each file.

yes, only column A.

Is it the same column in every xls and csv file in a particular folder?

thank you! But i think use vba run for all file xls.

 

[Joe4]

thank you! But i think use vba run for all file xls.

Yes, I know the intention is to use VBA to do it. But in order to write the VBA code, we need to know more details (you didn't give us very much to start with).
Is the intention to run this on every xls and csv in some specified folder?
If not, how are we to know which files to run this on?
Where are they located?
And what is the logic for determining which ones to run against and which to skip?

 

[Nguyen Anh Dung]

Yes, I know the intention is to use VBA to do it. But in order to write the VBA code, we need to know more details (you didn't give us very much to start with).
Is the intention to run this on every xls and csv in some specified folder?
If not, how are we to know which files to run this on?
Where are they located?
And what is the logic for determining which ones to run against and which to skip?

yes, sorry!!! you can download file with link:

file.rar

drive.google.com

 

[Joe4]

You still haven't answered my question.
You talk about wanting to do this om a lot of files.
Please answer ALL of the questions below.

1. Are all of the files you want to do this in the same folder (directory)?
2. Do you want to run this against EVERY file in that folder?
3. Which column(s) need to be re-formatted?

 

[Nguyen Anh Dung]

You still haven't answered my question.
You talk about wanting to do this om a lot of files.
Please answer ALL of the questions below.

1. Are all of the files you want to do this in the same folder (directory)?
2. Do you want to run this against EVERY file in that folder?
3. Which column(s) need to be re-formatted?

Yes, i want format column A.
All file in the same folder.

 

[Joe4]

OK, this should work:

Sub MyFormatMacro()

    Application.ScreenUpdating = False

    Dim myFolder As String
    Dim myFile As String
    Dim wb As Workbook
        
'   Designate folder to loop through
    myFolder = "C:\Temp\Joe"
    If Right(myFolder, 1) <> "\" Then myFolder = myFolder & "\"
    
'   Loop through all Excel files in folder
    myFile = Dir(myFolder & "*.xls*")
    Do While myFile <> ""
        Set wb = Workbooks.Open(Filename:=myFolder & myFile)
'       Format column A
        wb.Worksheets(1).Columns("A:A").NumberFormat = "yyyy-mm-dd hh:mm:ss"
'       Save and close workbook
        wb.Close SaveChanges:=True
'       Get next file name
        myFile = Dir
    Loop

'   Loop through all CSV files in folder
    myFile = Dir(myFolder & "*.csv")
    Do While myFile <> ""
        Set wb = Workbooks.Open(Filename:=myFolder & myFile)
'       Format column A
        wb.Worksheets(1).Columns("A:A").NumberFormat = "yyyy-mm-dd hh:mm:ss"
'       Save and close workbook
        wb.Close SaveChanges:=True
'       Get next file name
        myFile = Dir
    Loop

    Application.ScreenUpdating = True

    MsgBox "Macro complete!"

End Sub

Just change the file path to match your file path.
There may be a way to combine the two loops into one, but that will just make the macro a little shorter, and probably won't have much affect on run time.