VBScript / VBA / Excel

I have over 70 Excel files with macroses in them, Excel files are set up to run after I open them and then they are closed by itself after all procedures are done. I created vbs script that open them and then close and everything works perfect. Additionally, I created a list of Excel files that VBS run and placed it in a separate Excel file, my question is how to create VBScript that would record time when every Excel file was open through VBS script.

For one file

Option     Explicit
Dim oExcel, oSheet

Set oExcel = CreateObject("Excel.Application")

oExcel.workbooks.open "C:\Docum..............\data.xlsm"

Set oSheet = oExcel.ActiveWorkbook.Worksheets(1)


oExcel.ActiveWorkbook.sAVE
oExcel.ActiveWorkbook.Close
oExcel.Quit

Set oSheet = Nothing
Set oExcel = Nothing

开发者_C百科

---

OR for "i" number of files

---

Option Explicit
Dim filePath(1)
Dim oExcel, oSheet
Dim i

filePath(0) = "C:\Docum..........\data.xlsm"
filePath(1) = "C:\Docum..........\data1.xlsm"
Set oExcel = CreateObject("Excel.Application")

For i = 0 To 1
oExcel.Workbooks.Open (filePath(i))

Set oSheet = oExcel.ActiveWorkbook.Worksheets(1)
oExcel.Run "macro1"

oExcel.ActiveWorkbook.Save
oExcel.ActiveWorkbook.Close
oExcel.Quit
Set oSheet = Nothing
Next
Set oExcel = Nothing

Sub Macro1()
    Sheets("Sheet1").Select
    Range("A5").Select
    ActiveCell.Value = "1"
End Sub

---

---

how to create VBScript that would record time when every Excel file was open

Your question isn't very clear (where do you want to record the time?), but:

• VBScript has a function "Now" that will return the current date/time

• Excel exposes an object model which will allow you to save the time somewhere in the workbook.

• VBScript has an object Scripting.FileSystemObject which you can use to open a file and write the current time.