Nguyen Anh Dung
Board Regular
- Joined
- Feb 28, 2020
- Messages
- 180
- 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.So what exactly is the problem?
What can't you just change the format of the column to the format that you want?
yes, only column A.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.
Is it the same column in every xls and csv file in a particular folder?
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).thank you! But i think use vba run for all file xls.
yes, sorry!!! you can download file with link: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, i want format column A.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?
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