I have inherited an old VB6 app that uses Crystal Reports files and generates
output files in both PDF and XLS format.
For the Excel files (Excel 2002), users want them to show gridlines by
default. There's probably an export option that can be set to accomplish
this, but I have not been able to find it. I appreciate whatever help you can
provide.

Thanks!

Phil

Hi Phil,

I imagine that it is the VB6 app that is creating these files without the
gridlines. By default Excel will create them, but each sheet has it's own
property set as whether or not to display these. I'm guessing that you want
something that will set this property to display them, rather than looking to
amend the VB6 app.

Do these output files get generated in a single folder? If so I can give you
a script that will go through all Excel files in a specified folder on a
specified drive and make sure all gridlines are showing in each one, and save
them this way.

It may be possible to be even more versatile if you can give more
information as to where the files are. If they are all centrally located (at
least after creation) then it is fairly simple with a script.

Sean.


--
(please remember to click yes if replies you receive are helpful to you)


"PhilSky" wrote:

I have inherited an old VB6 app that uses Crystal Reports files and generates
output files in both PDF and XLS format.
For the Excel files (Excel 2002), users want them to show gridlines by
default. There's probably an export option that can be set to accomplish
this, but I have not been able to find it. I appreciate whatever help you can
provide.

Thanks!

Phil

Hello Sean,

Yes, all the generated files are stored in a single directory, and yes, all
of them should show the gridlines by default. A script to do that would be
great, but I was looking to modify the VB6 app so that the files would not
need to be processed further after being created.

THANKS!!

Phil

"SeanC UK" wrote:

Hi Phil,

I imagine that it is the VB6 app that is creating these files without the
gridlines. By default Excel will create them, but each sheet has it's own
property set as whether or not to display these. I'm guessing that you want
something that will set this property to display them, rather than looking to
amend the VB6 app.

Do these output files get generated in a single folder? If so I can give you
a script that will go through all Excel files in a specified folder on a
specified drive and make sure all gridlines are showing in each one, and save
them this way.

It may be possible to be even more versatile if you can give more
information as to where the files are. If they are all centrally located (at
least after creation) then it is fairly simple with a script.

Sean.


--
(please remember to click yes if replies you receive are helpful to you)


"PhilSky" wrote:

I have inherited an old VB6 app that uses Crystal Reports files and generates
output files in both PDF and XLS format.
For the Excel files (Excel 2002), users want them to show gridlines by
default. There's probably an export option that can be set to accomplish
this, but I have not been able to find it. I appreciate whatever help you can
provide.

Thanks!

Phil

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)







--
(please remember to click yes if replies you receive are helpful to you)





"PhilSky" wrote:

Hello Sean,

Yes, all the generated files are stored in a single directory, and yes, all
of them should show the gridlines by default. A script to do that would be
great, but I was looking to modify the VB6 app so that the files would not
need to be processed further after being created.

THANKS!!

Phil

"SeanC UK" wrote:

Hi Phil,

I imagine that it is the VB6 app that is creating these files without the
gridlines. By default Excel will create them, but each sheet has it's own
property set as whether or not to display these. I'm guessing that you want
something that will set this property to display them, rather than looking to
amend the VB6 app.

Do these output files get generated in a single folder? If so I can give you
a script that will go through all Excel files in a specified folder on a
specified drive and make sure all gridlines are showing in each one, and save
them this way.

It may be possible to be even more versatile if you can give more
information as to where the files are. If they are all centrally located (at
least after creation) then it is fairly simple with a script.

Sean.


--
(please remember to click yes if replies you receive are helpful to you)


"PhilSky" wrote:

I have inherited an old VB6 app that uses Crystal Reports files and generates
output files in both PDF and XLS format.
For the Excel files (Excel 2002), users want them to show gridlines by
default. There's probably an export option that can be set to accomplish
this, but I have not been able to find it. I appreciate whatever help you can
provide.

Thanks!

Phil

PS in case you don't know, you will need to paste the code into notepad and
save it with the extension .vbs then just double click that file, or right
click and open with notepad to edit.
--
(please remember to click yes if replies you receive are helpful to you)


"PhilSky" wrote:

Hello Sean,

Yes, all the generated files are stored in a single directory, and yes, all
of them should show the gridlines by default. A script to do that would be
great, but I was looking to modify the VB6 app so that the files would not
need to be processed further after being created.

THANKS!!

Phil

"SeanC UK" wrote:

Hi Phil,

I imagine that it is the VB6 app that is creating these files without the
gridlines. By default Excel will create them, but each sheet has it's own
property set as whether or not to display these. I'm guessing that you want
something that will set this property to display them, rather than looking to
amend the VB6 app.

Do these output files get generated in a single folder? If so I can give you
a script that will go through all Excel files in a specified folder on a
specified drive and make sure all gridlines are showing in each one, and save
them this way.

It may be possible to be even more versatile if you can give more
information as to where the files are. If they are all centrally located (at
least after creation) then it is fairly simple with a script.

Sean.


--
(please remember to click yes if replies you receive are helpful to you)


"PhilSky" wrote:

I have inherited an old VB6 app that uses Crystal Reports files and generates
output files in both PDF and XLS format.
For the Excel files (Excel 2002), users want them to show gridlines by
default. There's probably an export option that can be set to accomplish
this, but I have not been able to find it. I appreciate whatever help you can
provide.

Thanks!

Phil

PPS you may want to test it on some dummy files first to make sure that it
works OK for you before attempting it for real. Or comment out some portions
of the code whilst you test to see if you can get any necessary network
access etc.

Sean.
--
(please remember to click yes if replies you receive are helpful to you)


"PhilSky" wrote:

Hello Sean,

Yes, all the generated files are stored in a single directory, and yes, all
of them should show the gridlines by default. A script to do that would be
great, but I was looking to modify the VB6 app so that the files would not
need to be processed further after being created.

THANKS!!

Phil

"SeanC UK" wrote:

Hi Phil,

I imagine that it is the VB6 app that is creating these files without the
gridlines. By default Excel will create them, but each sheet has it's own
property set as whether or not to display these. I'm guessing that you want
something that will set this property to display them, rather than looking to
amend the VB6 app.

Do these output files get generated in a single folder? If so I can give you
a script that will go through all Excel files in a specified folder on a
specified drive and make sure all gridlines are showing in each one, and save
them this way.

It may be possible to be even more versatile if you can give more
information as to where the files are. If they are all centrally located (at
least after creation) then it is fairly simple with a script.

Sean.


--
(please remember to click yes if replies you receive are helpful to you)


"PhilSky" wrote:

I have inherited an old VB6 app that uses Crystal Reports files and generates
output files in both PDF and XLS format.
For the Excel files (Excel 2002), users want them to show gridlines by
default. There's probably an export option that can be set to accomplish
this, but I have not been able to find it. I appreciate whatever help you can
provide.

Thanks!

Phil

Hi Sean,

I'm trying to implement your code in my VB app, and I think it will work -
THANKS for all your help!! - but my VB6 is more than a little rusty, so I
hope you can help a bit further.

The code opens the Crystal Reports file, sets parameters, export options,
etc, and then does:
Report.Export False

Here's where I want to implement your code. I need to create the
Excel.Application object, open the file that was created, and then loop thru
setting DisplayGridlines on each worksheet. However I get some compilation
errors, and documentation / help files are not up to date on this old PC I'm
using. Can you dispel some of my confusion?

Thanks again!!
Phil

"SeanC UK" wrote:

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)







--
(please remember to click yes if replies you receive are helpful to you)





"PhilSky" wrote:

Hello Sean,

Yes, all the generated files are stored in a single directory, and yes, all
of them should show the gridlines by default. A script to do that would be
great, but I was looking to modify the VB6 app so that the files would not
need to be processed further after being created.

THANKS!!

Phil

"SeanC UK" wrote:

Hi Phil,

I imagine that it is the VB6 app that is creating these files without the
gridlines. By default Excel will create them, but each sheet has it's own
property set as whether or not to display these. I'm guessing that you want
something that will set this property to display them, rather than looking to
amend the VB6 app.

Do these output files get generated in a single folder? If so I can give you
a script that will go through all Excel files in a specified folder on a
specified drive and make sure all gridlines are showing in each one, and save
them this way.

It may be possible to be even more versatile if you can give more
information as to where the files are. If they are all centrally located (at
least after creation) then it is fairly simple with a script.

Sean.


--
(please remember to click yes if replies you receive are helpful to you)


"PhilSky" wrote:

I have inherited an old VB6 app that uses Crystal Reports files and generates
output files in both PDF and XLS format.
For the Excel files (Excel 2002), users want them to show gridlines by
default. There's probably an export option that can be set to accomplish
this, but I have not been able to find it. I appreciate whatever help you can
provide.

Thanks!

Phil

Sean,

Please disregard my previous post asking for further assistance. Between the
hints I derived from your code, the Object Browser and other documentation, I
figured out everything I need. It's now working exactly as I want it to.

Thanks again!
Phil


"SeanC UK" wrote:

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)







--
(please remember to click yes if replies you receive are helpful to you)





"PhilSky" wrote:

Hello Sean,

Yes, all the generated files are stored in a single directory, and yes, all
of them should show the gridlines by default. A script to do that would be
great, but I was looking to modify the VB6 app so that the files would not
need to be processed further after being created.

THANKS!!

Phil

"SeanC UK" wrote:

Hi Phil,

I imagine that it is the VB6 app that is creating these files without the
gridlines. By default Excel will create them, but each sheet has it's own
property set as whether or not to display these. I'm guessing that you want
something that will set this property to display them, rather than looking to
amend the VB6 app.

Do these output files get generated in a single folder? If so I can give you
a script that will go through all Excel files in a specified folder on a
specified drive and make sure all gridlines are showing in each one, and save
them this way.

It may be possible to be even more versatile if you can give more
information as to where the files are. If they are all centrally located (at
least after creation) then it is fairly simple with a script.

Sean.


--
(please remember to click yes if replies you receive are helpful to you)


"PhilSky" wrote:

I have inherited an old VB6 app that uses Crystal Reports files and generates
output files in both PDF and XLS format.
For the Excel files (Excel 2002), users want them to show gridlines by
default. There's probably an export option that can be set to accomplish
this, but I have not been able to find it. I appreciate whatever help you can
provide.

Thanks!

Phil

Hi Phil,

Sorry I hadn't replied earlier, I've not been around for a while. Anyway,
I'm glad to hear that you've sorted it though.

Sean.
--
(please remember to click yes if replies you receive are helpful to you)


"PhilSky" wrote:

Sean,

Please disregard my previous post asking for further assistance. Between the
hints I derived from your code, the Object Browser and other documentation, I
figured out everything I need. It's now working exactly as I want it to.

Thanks again!
Phil


"SeanC UK" wrote:

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)







--
(please remember to click yes if replies you receive are helpful to you)





"PhilSky" wrote:

Hello Sean,

Yes, all the generated files are stored in a single directory, and yes, all
of them should show the gridlines by default. A script to do that would be
great, but I was looking to modify the VB6 app so that the files would not
need to be processed further after being created.

THANKS!!

Phil

"SeanC UK" wrote:

Hi Phil,

I imagine that it is the VB6 app that is creating these files without the
gridlines. By default Excel will create them, but each sheet has it's own
property set as whether or not to display these. I'm guessing that you want
something that will set this property to display them, rather than looking to
amend the VB6 app.

Do these output files get generated in a single folder? If so I can give you
a script that will go through all Excel files in a specified folder on a
specified drive and make sure all gridlines are showing in each one, and save
them this way.

It may be possible to be even more versatile if you can give more
information as to where the files are. If they are all centrally located (at
least after creation) then it is fairly simple with a script.

Sean.


--
(please remember to click yes if replies you receive are helpful to you)


"PhilSky" wrote:

I have inherited an old VB6 app that uses Crystal Reports files and generates
output files in both PDF and XLS format.
For the Excel files (Excel 2002), users want them to show gridlines by
default. There's probably an export option that can be set to accomplish
this, but I have not been able to find it. I appreciate whatever help you can
provide.

Thanks!

Phil