Hi Phil,
If you have access to the original code then you may be able to work out how
to do it from this script anyway. The script creates an Excel Object and
manipulates each Excel file using that object's methods etc.
There are 4 variables that you may need to change, all listed at the
beginning of the script. These are the computer name, the drive letter, the
path and the file extension (I've assumed xls). If it is a remote computer,
but you have the location on a networked drive then just use the local
computer setting, ".", as I have, and the appropriate drive letter. If it is
not a networked drive then you will need permissions for the computer you
name.
There isn't much error checking in this script, but I have tried to be as
careful as I can. I have written two lines that save the amended file, one I
have commented out. The one left in saves the files with an amended filename
and a date/time stamp. This is to prevent errors where the filename may
already exist. This may still happen, but it is unlikely. I also used SaveAs
with the new filename as it should prevent errors if a file is already being
accessed by somebody.
It is written so that it will only amend and save files where gridlines are
not shown. The only problem with this is that renaming the files means that
the originals will still have no gridlines, and will therefore be processed
again, if they remain in the same folder. If this causes problems then it is
possible to look into it further to make sure the file is not already open
etc, and to save using the original filename. If you want to just go ahead
and use the original filename, knowing that the file won't already be being
accessed, then just comment out the SaveAs line and uncomment the Save line.
Early in the code there is a line .Visible = False. This keeps Excel hidden
from your view. However, if the code stops then it may leave Excel open. You
can change this line if you prefer to see Excel working.
Let me know if you have any problems, or want any lines clarified etc.
Cheers,
Sean.
'SET VARIABLES
'strComputer IS THE COMPUTER NAME, USING "." MEANS THE LOCAL
'COMPUTER THAT THE SCRIPT IS RUNNING ON
strComputer = "."
strDrive = "C:"
'PATH REQUIRES DOUBLE \\ INSTEAD OF \
strPath = "\\Excel_Export_Files\\"
strExt = "xls"
'OPEN EXCEL
Set objXL = WScript.CreateObject("Excel.Application")
With objXL
.Visible = False
End With
intSecurity = objXL.AutomationSecurity
objXL.AutomationSecurity = 3
'FIND ANY FILES
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colFiles = objWMIService.ExecQuery _
("SELECT * FROM CIM_DataFile WHERE Drive = '" & strDrive & "' AND " & _
"Path = '" & strPath & "' AND Extension = '" & strExt & "'")
'DATE & TIME STAMP
strTimeStamp = day(now) & "_" & month(now) & "_" & year(now) & "_" &
hour(now) & "_" & minute(now) & "_" & second(now)
intFileCount = 0
'LOOP THROUGH FILES
For Each objFile in colFiles
boolGridlinesAdded = False
On Error Resume Next
Set objWrkBk = objXL.WorkBooks.Open (objFile.Name,0)
If Err.Number = 0 Then
'FILE OPENED
On Error Goto 0
For Each objSheet in objWrkBk.Worksheets
objSheet.Activate
'CHECK IF IT NEEDS AMENDING
If objXL.ActiveWindow.DisplayGridlines = False Then
objXL.ActiveWindow.DisplayGridlines = True
boolGridlinesAdded = True
End If
Next
If boolGridlinesAdded = True Then
'SAVING AMENDED FILE
intFileCount = intFileCount + 1
objWrkBk.SaveAs(Left(objFile.Name,Len(ObjFile.Name)-(Len(strExt)+1)) &
"_GRID_" & strTimeStamp & "_." & strExt)
' objWrkBk.Save
End If
objWrkBk.Close(False)
Else
'COULD NOT OPEN FILE
Err.Clear
On Error Goto 0
Wscript.Echo("Could not open file: " & objFile.Filename)
End If
Next
objXL.AutomationSecurity = intSecurity
objXl.Quit
Wscript.Echo("Done - processed " & intFileCount & " file(s).")
Wscript.Quit(0)
