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

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,066
Office Version
365
Platform
Windows
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

New Member
Joined
Feb 28, 2020
Messages
42
Office Version
2016
Platform
Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,066
Office Version
365
Platform
Windows
Is it the same column in every xls and csv file in a particular folder?
 

Nguyen Anh Dung

New Member
Joined
Feb 28, 2020
Messages
42
Office Version
2016
Platform
Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,066
Office Version
365
Platform
Windows
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

New Member
Joined
Feb 28, 2020
Messages
42
Office Version
2016
Platform
Windows
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:
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,066
Office Version
365
Platform
Windows
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

New Member
Joined
Feb 28, 2020
Messages
42
Office Version
2016
Platform
Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,066
Office Version
365
Platform
Windows
OK, this should work:
VBA Code:
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.
 

Forum statistics

Threads
1,089,220
Messages
5,406,927
Members
403,113
Latest member
ms_excel_recal_or_die

This Week's Hot Topics

Top