Gabriel Rotar
New Member
- Joined
- Mar 5, 2021
- Messages
- 7
- Office Version
- 2013
- Platform
- Windows
Hello,
I have a database of 500 excel files, on which operates multiple persons and sometimes are needed bulk changes on all files at ones.
For this reason, I'm trying to create an excel macro with an InputBox, but with the code that I wrote, the input box appears every time when a file is open.
Better said, if I want to change the creation date for instance on all 500 files, then I have to fill in 500 times the Date in InputBox, which cannot happen.
Please help me, below is my written code. Thank you!
I have a database of 500 excel files, on which operates multiple persons and sometimes are needed bulk changes on all files at ones.
For this reason, I'm trying to create an excel macro with an InputBox, but with the code that I wrote, the input box appears every time when a file is open.
Better said, if I want to change the creation date for instance on all 500 files, then I have to fill in 500 times the Date in InputBox, which cannot happen.
Please help me, below is my written code. Thank you!
VBA Code:
Sub Bulk_Update_CreationDate()
Dim dte As Date
Dim folder_path As String
Dim wb As Workbook
Dim ws As Worksheet
folder_path = "D:\PLs"
my_files = Dir(folder_path & "\*.xlsx")
Do While my_files <> ""
Set wb = Workbooks.Open(folder_path & "\" & my_files)
mbox = InputBox("Enter new Creation Date")
Sheets("Product Line Information").Select
If IsDate(mbox) Then
dte = CDate(mbox)
Cells.Find(What:="Creation Date", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Cells.End(xlToRight).Select
ActiveCell = dte
Else
MsgBox "The entered date is not valid"
End If
Range("A1").Select
Application.DisplayAlerts = False
ActiveWorkbook.RemovePersonalInformation = False
ActiveWorkbook.Save
wb.Close True
my_files = Dir()
Loop
MsgBox ("All Files are Updated")
End Sub
Last edited by a moderator: