Formating several files based on one

[juansalix]

Hello,

I wonder if somebody could help me with this one. I have around 400 files with the exact same format and need to add an extra column to every single one of them. The column also needs to be time formated to hh:mm:ss.000.

I've attached an example of the first file. This one could be use as a "model file". The new column to be added starts in cell J17, and contains the following formula: =A17/86400 which should be dragged down depending on the number of cells of column A. As mentionned previously, the new column J also needs custom formating to hh:mm:ss.000

Note that on all of the files the length of column A varies, although column J always starts on cell J17 with the same exact formula.

Any help is appreciated! I've been trying to do this task manually but my guess is that there should be a better way to do this and hopefuly I will also learn something new out of it.

Very best
Juan

[davsth]

duplicate post

[davsth]

Are the files all in the same folder? And are either other files in the folder?

There are many bits of vba code on here that loop through all the files in a folder you put this code into a master workbook and then run it for all the files in a folder the you define. It is looking for all xls files but you could change it to fit accordingly, but hopefully a start

Sub OpenFiles()
Dim MyFolder As String
Dim MyFile As String
MyFolder = "C:\example"
MyFile = Dir(MyFolder & "\*.xls")
Do While MyFile <> ""
    Workbooks.Open Filename:=MyFolder & "\" & MyFile
LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
Range("J17:j"& lastrow).FormulaR1C1 = "=RC[-9]/86400"
Range("J17:j"& lastrow)..NumberFormat = " hh:mm:ss.000"
 ActiveWorkbook.Close SaveChanges:=True
Loop
End Sub