Did you know we can code in Excel???
I’m not a frequent Excel user — honestly, I rarely use it. But Today, I needed to combine about 30 Excel files into one workbook and modify some dates while also changing the names of sheets for a project I am working on.
So, I asked a friend of mine, who’s kind of an Excel expert (not a programmer), for advice. She told me I had to do everything manually — yikes!, there was a way to combine the files, but I didn’t want to use that option. I wanted a solution that would save me time and effort.
Instinctively I started googling “Python program to combine excel files”. That’s when I miss clicked and opened Microsoft documentation and discovered that you could write code directly in Excel using VBA (Visual Basic for Applications)!
I was blown away! I didn’t know Excel had that capability. So, instead of writing a separate Python script, I decided to go with VBA.
Here’s simplified steps how I automated my task in MS Excel (2013):
1. Open New MS Excel Workbook
2. Press Alt+F11 , this will open VBA editor window.
3. In VBA editor window : Insert > Module (this will make new module file , here you will write your code.)
VBA code to Combine Excel Files Into One Workbook:
(before that make a folder in file explorer and copy all the excel files you want to combine and your combined workbook will be created here )
Sub CombineExcelFilesIntoOneWorkbook()
Dim FolderPath As String
Dim FileName As String
Dim WorkbookSource As Workbook
Dim WorkbookDestination As Workbook
Dim Sheet As Worksheet
Dim OriginalFileName As String
' Path to the folder containing Excel files
FolderPath = " "
' Create a new workbook to store all the sheets
Set WorkbookDestination = Workbooks.Add
' Loop through all Excel files in the folder
FileName = Dir(FolderPath & "*.xls*")
Do While FileName <> ""
' Open each file
Set WorkbookSource = Workbooks.Open(FolderPath & FileName)
' Extract the file name without extension
OriginalFileName = Left(FileName, InStrRev(FileName, ".") - 1)
' Copy the sheet from the source workbook to the destination workbook
For Each Sheet In WorkbookSource.Sheets
Sheet.Copy After:=WorkbookDestination.Sheets(WorkbookDestination.Sheets.Count)
' Name the copied sheet after the original file
WorkbookDestination.Sheets(WorkbookDestination.Sheets.Count).Name = OriginalFileName
Next Sheet
' Close the source workbook without saving
WorkbookSource.Close False
' Get the next file
FileName = Dir()
Loop
' Remove the default empty sheets from the new workbook
On Error Resume Next
Application.DisplayAlerts = False
For Each Sheet In WorkbookDestination.Sheets
If Sheet.Name Like "Sheet*" Then
If Sheet.Index <= 3 Then Sheet.Delete
End If
Next Sheet
Application.DisplayAlerts = True
On Error GoTo 0
' Save the combined workbook
WorkbookDestination.SaveAs FolderPath & "CombinedWorkbook.xlsx"
WorkbookDestination.Close
MsgBox "All files have been combined into 'CombinedWorkbook.xlsx'!"
End SubVBA code to Combine Excel Files and Modify Dates:
Sub CombineExcelFilesByDateModified()
Dim FolderPath As String
Dim FSO As Object
Dim Folder As Object
Dim File As Object
Dim FilesCollection As Object
Dim FilesSorted As Object
Dim WorkbookSource As Workbook
Dim WorkbookDestination As Workbook
Dim OriginalFileName As String
Dim i As Integer
' Path to the folder containing Excel files
FolderPath = ""
' Initialize FileSystemObject
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Folder = FSO.GetFolder(FolderPath)
Set FilesCollection = New Collection
' Add files to collection with their modified dates
For Each File In Folder.Files
If File.Type Like "Microsoft Excel*" Then
FilesCollection.Add Array(File.Name, File.DateLastModified)
End If
Next File
' Sort files by Date Modified (Descending)
Set FilesSorted = SortFilesByDate(FilesCollection)
' Create a new workbook to store all the sheets
Set WorkbookDestination = Workbooks.Add
' Loop through sorted files
For i = 1 To FilesSorted.Count
' Extract file name and date from sorted array
FileName = FilesSorted(i)(0)
' Open the file
Set WorkbookSource = Workbooks.Open(FolderPath & FileName)
' Extract the file name without extension
OriginalFileName = Left(FileName, InStrRev(FileName, ".") - 1)
' Copy the sheet from the source workbook to the destination workbook
For Each Sheet In WorkbookSource.Sheets
Sheet.Copy After:=WorkbookDestination.Sheets(WorkbookDestination.Sheets.Count)
' Name the copied sheet after the original file
WorkbookDestination.Sheets(WorkbookDestination.Sheets.Count).Name = OriginalFileName
Next Sheet
' Close the source workbook without saving
WorkbookSource.Close False
Next i
' Remove the default empty sheets from the new workbook
On Error Resume Next
Application.DisplayAlerts = False
For Each Sheet In WorkbookDestination.Sheets
If Sheet.Name Like "Sheet*" Then
If Sheet.Index <= 3 Then Sheet.Delete
End If
Next Sheet
Application.DisplayAlerts = True
On Error GoTo 0
' Save the combined workbook
WorkbookDestination.SaveAs FolderPath & "CombinedWorkbook_SortedByDate.xlsx"
WorkbookDestination.Close
MsgBox "All files have been combined into 'CombinedWorkbook_SortedByDate.xlsx' in descending date modified order!"
End Sub
Function SortFilesByDate(FilesCollection As Collection) As Collection
Dim i As Long, j As Long
Dim Temp As Variant
Dim SortedCollection As Collection
' Convert collection to array for sorting
Dim FileArray() As Variant
ReDim FileArray(1 To FilesCollection.Count)
For i = 1 To FilesCollection.Count
FileArray(i) = FilesCollection(i)
Next i
' Bubble Sort by date modified (Descending)
For i = LBound(FileArray) To UBound(FileArray) - 1
For j = i + 1 To UBound(FileArray)
If FileArray(i)(1) < FileArray(j)(1) Then
Temp = FileArray(i)
FileArray(i) = FileArray(j)
FileArray(j) = Temp
End If
Next j
Next i
' Add sorted files back to collection
Set SortedCollection = New Collection
For i = LBound(FileArray) To UBound(FileArray)
SortedCollection.Add FileArray(i)
Next i
Set SortFilesByDate = SortedCollection
End FunctionReplace path in code with your folder path you have created. And Press F5 to run the program.
4. Enable the Developer Tab:
File > Options > Customize Ribbon > Check “Developer” > Ok
5. Switch to Developer Tab and Click Macros Option
This will open a window with all the programs (module) you wrote.
Select the function and click Run button.
Your Program will be executed.
Discovering that Excel allows coding directly in VBA was a game-changer for me. It saved me a lot of time. I found it amusing how the developers of Excel were like,
“I’ve given you 100s of functions to work with, and if it’s still not enough, go write it yourself, you loco!” 😂
But seriously, it’s incredible how much flexibility VBA offers.
Bottom line:
coding still has its perks, even in 2025. Whether it’s automating stuff in Excel or building something bigger, knowing how to code is always a win.
Links to My Other Blogs:
