In VB Script command to save an EXCEL file with a different file name

morinia
morinia used Ask the Experts™
on
I have the folllowing VB Script that is going through a folder and adding the date to "A1", How can I save the modified file with a different name and add change "Sheet1" to "Today" and close all open spreadsheets.

Set objShell = CreateObject ("Shell.Application")
Set objFolder = objShell.Namespace ("C:\Documents and Settings\George A. Blocker\My Documents\Question_9")
For Each strFileName in objFolder.Items
    MsgBox strFileName
    objExcel.Workbooks.open(objFolder & "\" & strFileName )
    objExcel.Cells(1, 1) = Now()
    ******Add Code Here*******
 Next
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
Continuing your code:

    With objExcel.ActiveWorkbook
        .Worksheets("Sheet1").Name = "Today"
        .SaveAs objFolder & "\" & Format(Now, "yyyy-mm-dd hhmmss " & strFileName
        .Close
    End With

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial