I have 250 different excel files inside a folder (with same layout) with columns A to F. I need to add a new column on column G. The conventional approach would be opening each file and adding new column at G. Is there any simple process using Excel macro or any other tools to get this done?
Asked
Active
Viewed 2,847 times
3
-
2Yes, you can do this using a macro. See under "Related" down on the right of this page for similar questions which should give you a start. – Tim Williams Feb 07 '17 at 19:00
-
http://www.xlorate.com/vba-examples.html#Loop%20Through%20Folder – Davesexcel Feb 07 '17 at 19:38
2 Answers
3
This link helped me. Following is my solution, which works:
Sub LoopThroughFolder()
Dim MyFile As String, Str As String, MyDir As String, Wb As Workbook
Dim Rws As Long, Rng As Range
Set Wb = ThisWorkbook
'change the address to suite
MyDir = "C:\Users\dell\Desktop\Folder1\" 'Your Directory
MyFile = Dir(MyDir & "*.xlsx") 'Your excel file extension
ChDir MyDir
Application.ScreenUpdating = 0
Application.DisplayAlerts = 0
Do While MyFile <> ""
Workbooks.Open (MyFile)
Range("G1").Value = "NewColumn" 'New Column Name
ActiveWorkbook.Save
ActiveWorkbook.Close True
MyFile = Dir()
Loop
End Sub
Martin S
- 428
- 3
- 12
0
Yes here's the code for open all excel files in a folder: macro - open all files in a folder
In that loop you can add a new Column to that files
