Modifying the "Scaling" setting of an .xls file

[Everett]

Hello All!

I am using Excel 2000 to open and view several
spreadsheets.

These spreadsheets are generated nightly via a conversion
of a scheduled Crystal Report exported to Excel for each
customer in our customer base. An .xls file get created
for each customer.

When the files are generated, as it relates to "scaling",
the value that is desired varies from client to client.
Client A wants this value set at 50% normal size value
while Client B, C, D, E want this value set at 75% and
still other clients want it set to some other value.

If I wanted to do this manually, I would click on
File\Page Setup. Then, after I get the Page Setup dialog,
in the scaling section, I would set the "Adjust to __ %
normal size value" based on which client the spreadsheet
belongs to.

If I only had a few clients, that may be an option.
However, we are generating hundreds of these files
nightly. So, I would like to know is it possible to
"Automate" or programatically modify the setting of
the "scaling" percentage via interfacing with the
worksheet/workbook/application object?

If indeed it is possible, how would I go about doing so?

Also, if I wanted to set the "ReadOnly" property of
each .xls once I am done with it, how would I go about
doing so?

Thanks in advance for all ideas and suggestions.

Everett

 

[Dave Peterson]

I think I'd make a table of customer to scaling. Put that in a separate
workbook and call the worksheet Table.

Then put this code in that same workbook:

Option Explicit
Sub testme01()

Application.ScreenUpdating = False

Dim myFiles() As String
Dim fCtr As Long
Dim iCtr As Long
Dim myFile As String
Dim myPath As String
Dim AddressToCheck As String
Dim tempWks As Worksheet
Dim myZoom As Variant
Dim pSetup As String

AddressToCheck = "A1"

myPath = "c:\my documents\excel\test"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

myFile = Dir(myPath & "*.xls")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile <> ""
fCtr = fCtr + 1
ReDim Preserve myFiles(1 To fCtr)
myFiles(fCtr) = myFile
myFile = Dir()
Loop

If fCtr > 0 Then
For iCtr = LBound(myFiles) To UBound(myFiles)
Application.StatusBar _
= "Processing: " & myFiles(iCtr) & " at: " & Now

Set tempWks = Nothing
On Error Resume Next
Application.EnableEvents = False
Set tempWks = Workbooks.Open(Filename:=myPath & myFiles(iCtr), _
UpdateLinks:=0).Worksheets(1)
Application.EnableEvents = True
On Error GoTo 0

If tempWks Is Nothing Then
MsgBox "couldn't open: " & myPath & myFiles(iCtr)
Else
With tempWks
myZoom = Application.VLookup _
(.Range(AddressToCheck).Value, _
ThisWorkbook.Worksheets("TABLE").Range("A:B"), _
2, False)
If IsError(myZoom) Then
myZoom = 100
End If
'.PageSetup.Zoom = myZoom

pSetup = "PAGE.SETUP(,,,,"
pSetup = pSetup & ",,,,,"
pSetup = pSetup & ",,," & myZoom & ","
pSetup = pSetup & ",,,,"
pSetup = pSetup & ",,,)"
Application.ExecuteExcel4Macro pSetup

.Parent.Close SaveChanges:=True
End With
End If
Next iCtr
End If

With Application
.ScreenUpdating = True
.StatusBar = False
End With

End Sub

The macro looks in A1 of the first worksheet to find a match in your table. If
no match, then put the zoom at 100%.

I expected the ".pagesetup.zoom = myzoom" line to change the zoom factor. When
I stepped through the code, it seemed to change it, but when I reopened the
workbook, the zoom had reverted.

I took some code posted by John Green (an old xl4 macro) and slightly modified
it. It seemed to stick.

I'm not quite sure what you mean by readonly. Do you mean protect the
worksheet, or protect the workbook or mark the file Readonly.

If you mean do a file|saveAs and set a password to make changes, then replace
this line:

.Parent.Close SaveChanges:=True
with
Application.DisplayAlerts = False
.Parent.SaveAs Filename:=.Parent.FullName, _
WriteResPassword:="hi"
Application.DisplayAlerts = True
.Parent.Close SaveChanges:=False 'we just saved

And give it a memorable password.

 

[Dave Peterson]

And I meant to mention that I was using xl2002 and the scaling/zoom factor
didn't stick using ".PageSetup.Zoom = myZoom"


<<snipped>>

 

[Everett]

Dave:

Thanks a million for your reply. Although I have not
implemented it yet, it looks like your suggestion will be
exactly what I need to do.

Again, many thanks!

Everett

 

[Everett]

Dave:

Again, thanks for your suggestion. I have actually taken
the code sample that you sent to me and I have modified it
and implemented it in my Delphi application. The only
thing that I need to do at this point is to suppress a
message dialog that I am seeing. In my app, once I change
the "zoom" percentage, I save the Application object.
However, at that point, I get a message like the following:

"FileABC.xls is a Microsoft Excel 5.0/95 Workbook. Do yo
want to overwrite it with the latest Excel format?

* To overwrite it, click Yes.
* To save in the current format, click No."

Right now, I am electing to click on "NO" thereby saving
my file in the original format. Is there a way make this
invisible to the user? I don't want them to have to click
anything as I need to make this invisible to them.

Any suggestions or ideas?

Thanks,
Everett

 

[Dave Peterson]

Change this line:

..Parent.Close SaveChanges:=True

To these three:
application.displayalerts = false
..Parent.Close SaveChanges:=True
application.displayalerts = true

 

[Everett]

Dave:

Thanks again for your reply!

I modified my code as you suggested, but, the changes that
I am making to the Scaling percent now are not saving.
For example, my code looks like the following:

XLApp.WorkBooks.Open('abc.xls', 0, false);
sheet := XLApp.WorkBooks[1].Worksheets[1];
MyZoom := 75;
pSetup := 'PAGE.SETUP(,,,,';
pSetup := pSetup + ',,,,,';
pSetup := pSetup + ',,,' + IntToStr(myZoom) + ',';
pSetup := pSetup + ',,,,';
pSetup := pSetup + ',,,)';
XLApp.Workbooks.Application.ExecuteExcel4Macro(pSetup);
XLApp.DisplayAlerts := False;
XLApp.ActiveWorkbook.Save;
XLApp.WorkBooks.Close;
XLApp.DisplayAlerts := True;

HOWEVER, when I go an open the spreadsheet to see if the
changes took, the zoom is still set the the same
percentage that it was at before I attempted to make the
changes.

Also, based on info that I found on the MS site, it says
in one of the examples that :

This example closes the workbook Book1.xls and doesn't
prompt the user to save changes. Any changes to Book1.xls
aren't saved.

Application.DisplayAlerts = False
Workbooks("BOOK1.XLS").Close
Application.DisplayAlerts = True

I would indeed want to save the changes, so, how can I
suppress the dialog AND save the changes?

Thanks in advance,
Everett

 

[Dave Peterson]

First, I'm not sure what happened to the code you pasted, but the double quotes
are gone (replaced with apostrophes) and lines end with a semicolon. This
doesn't look like VBA to me.

And this portion of your code will close the workbook:

Application.DisplayAlerts = False
Workbooks("BOOK1.XLS").Close
Application.DisplayAlerts = True

But I suggested this:

application.displayalerts = false
..Parent.Close SaveChanges:=True
application.displayalerts = true

The leading dot in front of .parent means that .parent belongs to the previous
With Statement--which happened to be: With tempWks

The tempWks was my variable name for a Worksheet. The parent of the worksheet
is the workbook. And I had Savechanges:=true to force the save.

If you ran your code without saving, then that would be an excellent reason for
the settings not to be saved <vbg>.

But I don't have a reason why it didn't get saved if you actually saved it. (It
worked for me in xl2002.)

Dave:

Thanks again for your reply!

I modified my code as you suggested, but, the changes that
I am making to the Scaling percent now are not saving.
For example, my code looks like the following:

XLApp.WorkBooks.Open('abc.xls', 0, false);
sheet := XLApp.WorkBooks[1].Worksheets[1];
MyZoom := 75;
pSetup := 'PAGE.SETUP(,,,,';
pSetup := pSetup + ',,,,,';
pSetup := pSetup + ',,,' + IntToStr(myZoom) + ',';
pSetup := pSetup + ',,,,';
pSetup := pSetup + ',,,)';
XLApp.Workbooks.Application.ExecuteExcel4Macro(pSetup);
XLApp.DisplayAlerts := False;
XLApp.ActiveWorkbook.Save;
XLApp.WorkBooks.Close;
XLApp.DisplayAlerts := True;

HOWEVER, when I go an open the spreadsheet to see if the
changes took, the zoom is still set the the same
percentage that it was at before I attempted to make the
changes.

Also, based on info that I found on the MS site, it says
in one of the examples that :

This example closes the workbook Book1.xls and doesn't
prompt the user to save changes. Any changes to Book1.xls
aren't saved.

Application.DisplayAlerts = False
Workbooks("BOOK1.XLS").Close
Application.DisplayAlerts = True

I would indeed want to save the changes, so, how can I
suppress the dialog AND save the changes?

Thanks in advance,
Everett

-----Original Message-----
Change this line:

..Parent.Close SaveChanges:=True

To these three:
application.displayalerts = false
..Parent.Close SaveChanges:=True
application.displayalerts = true






--

Dave Peterson
(e-mail address removed)
.

 

[Everett]

Dave, again, thanks for your reply!

In the code sample that I pasted, I should have noted that
I "retrofitted" the snippet that you initially gave me so
that it would work within my Delphi application. Thus, I
was not writing VBA at that point, but, instead,
converting from VBA.

Next, I did not know that the leading dot in front
of .Parent implied that parent belonged to the previous
with. However, with that in mind, I should be able to get
this to work as you originally noted.

Again, thanks for your feedback,
Everett

-----Original Message-----
First, I'm not sure what happened to the code you pasted, but the double quotes
are gone (replaced with apostrophes) and lines end with a semicolon. This
doesn't look like VBA to me.

And this portion of your code will close the workbook:

Application.DisplayAlerts = False
Workbooks("BOOK1.XLS").Close
Application.DisplayAlerts = True

But I suggested this:

application.displayalerts = false
..Parent.Close SaveChanges:=True
application.displayalerts = true

The leading dot in front of .parent means that .parent belongs to the previous
With Statement--which happened to be: With tempWks

The tempWks was my variable name for a Worksheet. The parent of the worksheet
is the workbook. And I had Savechanges:=true to force the save.

If you ran your code without saving, then that would be an excellent reason for
the settings not to be saved <vbg>.

But I don't have a reason why it didn't get saved if you actually saved it. (It
worked for me in xl2002.)

Dave:

Thanks again for your reply!

I modified my code as you suggested, but, the changes that
I am making to the Scaling percent now are not saving.
For example, my code looks like the following:

XLApp.WorkBooks.Open('abc.xls', 0, false);
sheet := XLApp.WorkBooks[1].Worksheets[1];
MyZoom := 75;
pSetup := 'PAGE.SETUP(,,,,';
pSetup := pSetup + ',,,,,';
pSetup := pSetup + ',,,' + IntToStr(myZoom) + ',';
pSetup := pSetup + ',,,,';
pSetup := pSetup + ',,,)';
XLApp.Workbooks.Application.ExecuteExcel4Macro(pSetup);
XLApp.DisplayAlerts := False;
XLApp.ActiveWorkbook.Save;
XLApp.WorkBooks.Close;
XLApp.DisplayAlerts := True;

HOWEVER, when I go an open the spreadsheet to see if the
changes took, the zoom is still set the the same
percentage that it was at before I attempted to make the
changes.

Also, based on info that I found on the MS site, it says
in one of the examples that :

This example closes the workbook Book1.xls and doesn't
prompt the user to save changes. Any changes to Book1.xls
aren't saved.

Application.DisplayAlerts = False
Workbooks("BOOK1.XLS").Close
Application.DisplayAlerts = True

I would indeed want to save the changes, so, how can I
suppress the dialog AND save the changes?

Thanks in advance,
Everett

-----Original Message-----
Change this line:

..Parent.Close SaveChanges:=True

To these three:
application.displayalerts = false
..Parent.Close SaveChanges:=True
application.displayalerts = true





Everett wrote:

Dave:

Again, thanks for your suggestion. I have actually taken
the code sample that you sent to me and I have

modified
it

and implemented it in my Delphi application. The only
thing that I need to do at this point is to suppress a
message dialog that I am seeing. In my app, once I change
the "zoom" percentage, I save the Application object.
However, at that point, I get a message like the following:

"FileABC.xls is a Microsoft Excel 5.0/95 Workbook. Do yo
want to overwrite it with the latest Excel format?

* To overwrite it, click Yes.
* To save in the current format, click No."

Right now, I am electing to click on "NO" thereby saving
my file in the original format. Is there a way make this
invisible to the user? I don't want them to have to click
anything as I need to make this invisible to them.

Any suggestions or ideas?

Thanks,
Everett

-----Original Message-----
Dave:

Thanks a million for your reply. Although I have not
implemented it yet, it looks like your suggestion

will
be

exactly what I need to do.

Again, many thanks!

Everett
-----Original Message-----
And I meant to mention that I was using xl2002 and the
scaling/zoom factor
didn't stick using ".PageSetup.Zoom = myZoom"


<<snipped>>
--

Dave Peterson
(e-mail address removed)
.

.


--

Dave Peterson
(e-mail address removed)
.

--

Dave Peterson
(e-mail address removed)
.

 

[Dave Peterson]

Ahh. Good luck on the modifications.

Dave, again, thanks for your reply!

In the code sample that I pasted, I should have noted that
I "retrofitted" the snippet that you initially gave me so
that it would work within my Delphi application. Thus, I
was not writing VBA at that point, but, instead,
converting from VBA.

Next, I did not know that the leading dot in front
of .Parent implied that parent belonged to the previous
with. However, with that in mind, I should be able to get
this to work as you originally noted.

Again, thanks for your feedback,
Everett

-----Original Message-----
First, I'm not sure what happened to the code you pasted, but the double quotes
are gone (replaced with apostrophes) and lines end with a semicolon. This
doesn't look like VBA to me.

And this portion of your code will close the workbook:

Application.DisplayAlerts = False
Workbooks("BOOK1.XLS").Close
Application.DisplayAlerts = True

But I suggested this:

application.displayalerts = false
..Parent.Close SaveChanges:=True
application.displayalerts = true

The leading dot in front of .parent means that .parent belongs to the previous
With Statement--which happened to be: With tempWks

The tempWks was my variable name for a Worksheet. The parent of the worksheet
is the workbook. And I had Savechanges:=true to force the save.

If you ran your code without saving, then that would be an excellent reason for
the settings not to be saved <vbg>.

But I don't have a reason why it didn't get saved if you actually saved it. (It
worked for me in xl2002.)

Dave:

Thanks again for your reply!

I modified my code as you suggested, but, the changes that
I am making to the Scaling percent now are not saving.
For example, my code looks like the following:

XLApp.WorkBooks.Open('abc.xls', 0, false);
sheet := XLApp.WorkBooks[1].Worksheets[1];
MyZoom := 75;
pSetup := 'PAGE.SETUP(,,,,';
pSetup := pSetup + ',,,,,';
pSetup := pSetup + ',,,' + IntToStr(myZoom) + ',';
pSetup := pSetup + ',,,,';
pSetup := pSetup + ',,,)';
XLApp.Workbooks.Application.ExecuteExcel4Macro(pSetup);
XLApp.DisplayAlerts := False;
XLApp.ActiveWorkbook.Save;
XLApp.WorkBooks.Close;
XLApp.DisplayAlerts := True;

HOWEVER, when I go an open the spreadsheet to see if the
changes took, the zoom is still set the the same
percentage that it was at before I attempted to make the
changes.

Also, based on info that I found on the MS site, it says
in one of the examples that :

This example closes the workbook Book1.xls and doesn't
prompt the user to save changes. Any changes to Book1.xls
aren't saved.

Application.DisplayAlerts = False
Workbooks("BOOK1.XLS").Close
Application.DisplayAlerts = True

I would indeed want to save the changes, so, how can I
suppress the dialog AND save the changes?

Thanks in advance,
Everett

-----Original Message-----
Change this line:

..Parent.Close SaveChanges:=True

To these three:
application.displayalerts = false
..Parent.Close SaveChanges:=True
application.displayalerts = true





Everett wrote:

Dave:

Again, thanks for your suggestion. I have actually
taken
the code sample that you sent to me and I have modified
it
and implemented it in my Delphi application. The only
thing that I need to do at this point is to suppress a
message dialog that I am seeing. In my app, once I
change
the "zoom" percentage, I save the Application object.
However, at that point, I get a message like the
following:

"FileABC.xls is a Microsoft Excel 5.0/95 Workbook. Do yo
want to overwrite it with the latest Excel format?

* To overwrite it, click Yes.
* To save in the current format, click No."

Right now, I am electing to click on "NO" thereby saving
my file in the original format. Is there a way make
this
invisible to the user? I don't want them to have to
click
anything as I need to make this invisible to them.

Any suggestions or ideas?

Thanks,
Everett

-----Original Message-----
Dave:

Thanks a million for your reply. Although I have not
implemented it yet, it looks like your suggestion will
be
exactly what I need to do.

Again, many thanks!

Everett
-----Original Message-----
And I meant to mention that I was using xl2002 and the
scaling/zoom factor
didn't stick using ".PageSetup.Zoom = myZoom"


<<snipped>>
--

Dave Peterson
(e-mail address removed)
.

.


--

Dave Peterson
(e-mail address removed)
.

--

Dave Peterson
(e-mail address removed)
.

 

[Everett]

Hello Again Dave!

I just wanted to be sure to post a "success" message
concerning this thread.

First, let me commend you for your follow through on my
original post. I was really not certain that anyone would
post suggestions and/or ideas. Not only that, after I got
your first reply, I was certain that you, and no one else
would go back to previous days to follow up with "older"
issues. However, I am pleased to state that I was wrong,
and, I am very impressed with your level of expertise and
professionalism.

At this point, my solution has been "cleared for
release". With your help and direction, I was able to
implement a solution that not only met, but, exceeded the
users' expectations.

Again, I just wanted to say "Thank You Much" for your help.

Regards,
Everett

-----Original Message-----
Ahh. Good luck on the modifications.

Dave, again, thanks for your reply!

In the code sample that I pasted, I should have noted that
I "retrofitted" the snippet that you initially gave me so
that it would work within my Delphi application. Thus, I
was not writing VBA at that point, but, instead,
converting from VBA.

Next, I did not know that the leading dot in front
of .Parent implied that parent belonged to the previous
with. However, with that in mind, I should be able to get
this to work as you originally noted.

Again, thanks for your feedback,
Everett

-----Original Message-----
First, I'm not sure what happened to the code you

pasted,
but the double quotes

are gone (replaced with apostrophes) and lines end

with a
semicolon. This

doesn't look like VBA to me.

And this portion of your code will close the workbook:

Application.DisplayAlerts = False
Workbooks("BOOK1.XLS").Close
Application.DisplayAlerts = True

But I suggested this:

application.displayalerts = false
..Parent.Close SaveChanges:=True
application.displayalerts = true

The leading dot in front of .parent means that .parent belongs to the previous
With Statement--which happened to be: With tempWks

The tempWks was my variable name for a Worksheet. The parent of the worksheet
is the workbook. And I had Savechanges:=true to force the save.

If you ran your code without saving, then that would be an excellent reason for
the settings not to be saved <vbg>.

But I don't have a reason why it didn't get saved if

you
actually saved it. (It

worked for me in xl2002.)

Everett wrote:

Dave:

Thanks again for your reply!

I modified my code as you suggested, but, the changes that
I am making to the Scaling percent now are not saving.
For example, my code looks like the following:

XLApp.WorkBooks.Open('abc.xls', 0, false);
sheet := XLApp.WorkBooks[1].Worksheets[1];
MyZoom := 75;
pSetup := 'PAGE.SETUP(,,,,';
pSetup := pSetup + ',,,,,';
pSetup := pSetup + ',,,' + IntToStr(myZoom) + ',';
pSetup := pSetup + ',,,,';
pSetup := pSetup + ',,,)';
XLApp.Workbooks.Application.ExecuteExcel4Macro (pSetup);
XLApp.DisplayAlerts := False;
XLApp.ActiveWorkbook.Save;
XLApp.WorkBooks.Close;
XLApp.DisplayAlerts := True;

HOWEVER, when I go an open the spreadsheet to see if the
changes took, the zoom is still set the the same
percentage that it was at before I attempted to make the
changes.

Also, based on info that I found on the MS site, it says
in one of the examples that :

This example closes the workbook Book1.xls and doesn't
prompt the user to save changes. Any changes to Book1.xls
aren't saved.

Application.DisplayAlerts = False
Workbooks("BOOK1.XLS").Close
Application.DisplayAlerts = True

I would indeed want to save the changes, so, how can I
suppress the dialog AND save the changes?

Thanks in advance,
Everett

-----Original Message-----
Change this line:

..Parent.Close SaveChanges:=True

To these three:
application.displayalerts = false
..Parent.Close SaveChanges:=True
application.displayalerts = true





Everett wrote:

Dave:

Again, thanks for your suggestion. I have actually
taken
the code sample that you sent to me and I have modified
it
and implemented it in my Delphi application. The only
thing that I need to do at this point is to

suppress
a

message dialog that I am seeing. In my app, once I
change
the "zoom" percentage, I save the Application object.
However, at that point, I get a message like the
following:

"FileABC.xls is a Microsoft Excel 5.0/95 Workbook. Do yo
want to overwrite it with the latest Excel format?

* To overwrite it, click Yes.
* To save in the current format, click No."

Right now, I am electing to click on "NO" thereby saving
my file in the original format. Is there a way make
this
invisible to the user? I don't want them to have to
click
anything as I need to make this invisible to them.

Any suggestions or ideas?

Thanks,
Everett

-----Original Message-----
Dave:

Thanks a million for your reply. Although I have not
implemented it yet, it looks like your suggestion will
be
exactly what I need to do.

Again, many thanks!

Everett
-----Original Message-----
And I meant to mention that I was using xl2002

and
the

scaling/zoom factor
didn't stick using ".PageSetup.Zoom = myZoom"


<<snipped>>
--

Dave Peterson
(e-mail address removed)
.

.


--

Dave Peterson
(e-mail address removed)
.


--

Dave Peterson
(e-mail address removed)
.

--

Dave Peterson
(e-mail address removed)
.

 

[Dave Peterson]

Glad you got it working.

But there are lots of people who followup on followups (hehe).

I may be the only one who needs 6 attempts to get to the "real" answer, though
<vbg>.

Hello Again Dave!

I just wanted to be sure to post a "success" message
concerning this thread.

First, let me commend you for your follow through on my
original post. I was really not certain that anyone would
post suggestions and/or ideas. Not only that, after I got
your first reply, I was certain that you, and no one else
would go back to previous days to follow up with "older"
issues. However, I am pleased to state that I was wrong,
and, I am very impressed with your level of expertise and
professionalism.

At this point, my solution has been "cleared for
release". With your help and direction, I was able to
implement a solution that not only met, but, exceeded the
users' expectations.

Again, I just wanted to say "Thank You Much" for your help.

Regards,
Everett

-----Original Message-----
Ahh. Good luck on the modifications.

Dave, again, thanks for your reply!

In the code sample that I pasted, I should have noted that
I "retrofitted" the snippet that you initially gave me so
that it would work within my Delphi application. Thus, I
was not writing VBA at that point, but, instead,
converting from VBA.

Next, I did not know that the leading dot in front
of .Parent implied that parent belonged to the previous
with. However, with that in mind, I should be able to get
this to work as you originally noted.

Again, thanks for your feedback,
Everett
-----Original Message-----
First, I'm not sure what happened to the code you pasted,
but the double quotes
are gone (replaced with apostrophes) and lines end with a
semicolon. This
doesn't look like VBA to me.

And this portion of your code will close the workbook:

Application.DisplayAlerts = False
Workbooks("BOOK1.XLS").Close
Application.DisplayAlerts = True

But I suggested this:

application.displayalerts = false
..Parent.Close SaveChanges:=True
application.displayalerts = true

The leading dot in front of .parent means that .parent
belongs to the previous
With Statement--which happened to be: With tempWks

The tempWks was my variable name for a Worksheet. The
parent of the worksheet
is the workbook. And I had Savechanges:=true to force
the save.

If you ran your code without saving, then that would be
an excellent reason for
the settings not to be saved <vbg>.

But I don't have a reason why it didn't get saved if you
actually saved it. (It
worked for me in xl2002.)

Everett wrote:

Dave:

Thanks again for your reply!

I modified my code as you suggested, but, the changes
that
I am making to the Scaling percent now are not saving.
For example, my code looks like the following:

XLApp.WorkBooks.Open('abc.xls', 0, false);
sheet := XLApp.WorkBooks[1].Worksheets[1];
MyZoom := 75;
pSetup := 'PAGE.SETUP(,,,,';
pSetup := pSetup + ',,,,,';
pSetup := pSetup + ',,,' + IntToStr(myZoom) + ',';
pSetup := pSetup + ',,,,';
pSetup := pSetup + ',,,)';
XLApp.Workbooks.Application.ExecuteExcel4Macro (pSetup);
XLApp.DisplayAlerts := False;
XLApp.ActiveWorkbook.Save;
XLApp.WorkBooks.Close;
XLApp.DisplayAlerts := True;

HOWEVER, when I go an open the spreadsheet to see if the
changes took, the zoom is still set the the same
percentage that it was at before I attempted to make the
changes.

Also, based on info that I found on the MS site, it says
in one of the examples that :

This example closes the workbook Book1.xls and doesn't
prompt the user to save changes. Any changes to
Book1.xls
aren't saved.

Application.DisplayAlerts = False
Workbooks("BOOK1.XLS").Close
Application.DisplayAlerts = True

I would indeed want to save the changes, so, how can I
suppress the dialog AND save the changes?

Thanks in advance,
Everett

-----Original Message-----
Change this line:

..Parent.Close SaveChanges:=True

To these three:
application.displayalerts = false
..Parent.Close SaveChanges:=True
application.displayalerts = true





Everett wrote:

Dave:

Again, thanks for your suggestion. I have actually
taken
the code sample that you sent to me and I have
modified
it
and implemented it in my Delphi application. The
only
thing that I need to do at this point is to suppress
a
message dialog that I am seeing. In my app, once I
change
the "zoom" percentage, I save the Application object.
However, at that point, I get a message like the
following:

"FileABC.xls is a Microsoft Excel 5.0/95 Workbook.
Do yo
want to overwrite it with the latest Excel format?

* To overwrite it, click Yes.
* To save in the current format, click No."

Right now, I am electing to click on "NO" thereby
saving
my file in the original format. Is there a way make
this
invisible to the user? I don't want them to have to
click
anything as I need to make this invisible to them.

Any suggestions or ideas?

Thanks,
Everett

-----Original Message-----
Dave:

Thanks a million for your reply. Although I have
not
implemented it yet, it looks like your suggestion
will
be
exactly what I need to do.

Again, many thanks!

Everett
-----Original Message-----
And I meant to mention that I was using xl2002 and
the
scaling/zoom factor
didn't stick using ".PageSetup.Zoom = myZoom"


<<snipped>>
--

Dave Peterson
(e-mail address removed)
.

.


--

Dave Peterson
(e-mail address removed)
.


--

Dave Peterson
(e-mail address removed)
.

--

Dave Peterson
(e-mail address removed)
.