VBA Rename File Date 1-31 เปลี่ยนชื่อไฟล์ ตามวันเดือนปีที่กำหนด และแก้ไขข้อมูลในไฟล์
Sub test()
Dim OldFile As String
Dim NewFile As String
fecha_ini = 1
fecha_fin = 31
i = 1
For Count = fecha_ini To fecha_fin
OldFile = "C:\Users\xxx\Desktop\test2222\test\" & Sheets("sheet1").Range("D2").Value & " " & Sheets("sheet1").Range("A2") & "\test " & Sheets("sheet1").Range("c" & Count).Value & Sheets("sheet1").Range("f2").Value & Sheets("sheet1").Range("a2").Value & ".xls"
NewFile = "C:\Users\xxx\Desktop\test2222\test\" & Sheets("sheet1").Range("D2").Value & " " & Sheets("sheet1").Range("A2") & "\test " & Sheets("sheet1").Range("c" & Count).Value & Sheets("sheet1").Range("b2").Value & Sheets("sheet1").Range("a2").Value & ".xls"
Name OldFile As NewFile
NewFile2 = "C:\Users\xxx\Desktop\test2222\test\" & Workbooks("test.xlsm").Sheets("sheet1").Range("D2").Value & " " & Workbooks("test.xlsm").Sheets("sheet1").Range("A2") & "\test " & Workbooks("test.xlsm").Sheets("sheet1").Range("c" & Count).Value & Workbooks("test.xlsm").Sheets("sheet1").Range("b2").Value & Workbooks("test.xlsm").Sheets("sheet1").Range("a2").Value & ".xls"
Workbooks.Open(NewFile2).Sheets("sheet1").Range("G5").Value = "Date: " & Workbooks("test.xlsm").Sheets("sheet1").Range("C" & Count).Value & " " & Workbooks("test.xlsm").Sheets("sheet1").Range("D2").Value & " " & Workbooks("test.xlsm").Sheets("sheet1").Range("A2").Value
Workbooks("test " & Workbooks("test.xlsm").Sheets("sheet1").Range("c" & Count).Value & Workbooks("test.xlsm").Sheets("sheet1").Range("b2").Value & Workbooks("test.xlsm").Sheets("sheet1").Range("a2").Value & ".xls").Save
Workbooks("test " & Workbooks("test.xlsm").Sheets("sheet1").Range("c" & Count).Value & Workbooks("test.xlsm").Sheets("sheet1").Range("b2").Value & Workbooks("test.xlsm").Sheets("sheet1").Range("a2").Value & ".xls").Close
i = i + 1
Next Count
End Sub
config sheet1
| year | Month | 01 | New | Old | Month |
| 2022 | 08 | 02 | Aug | 05 | |
| 03 | |||||
| 04 | |||||
| 05 | |||||
| 06 | |||||
| 07 | |||||
| 08 | |||||
| 09 | |||||
| 10 | |||||
| 11 | |||||
| 12 | |||||
| 13 | |||||
| 14 | |||||
| 15 | |||||
| 16 | |||||
| 17 | |||||
| 18 | |||||
| 19 | |||||
| 20 | |||||
| 21 | |||||
| 22 | |||||
| 23 | |||||
| 24 | |||||
| 25 | |||||
| 26 | |||||
| 27 | |||||
| 28 | |||||
| 29 | |||||
| 30 | |||||
| 31 |
0 ความคิดเห็น