Hi all,

We are using Excel for reporting in our organization.
Basically there is a Java web application which instantiates Excel 2007 with
an XLAM. This XLAM opens a Report Template(an .xls, .xlsm file, name
supplied as
command line parameter). The Template has a data sheet where we have multiple
ranges and the Report sheet which shows the final report based on the data
from the DataSheet. The ReportSheet contains formulas to show the final
output. It could contain Charts/Graphs also. The data is fetched from SQL
server and is pasted on the Ranges. And finally when all the data is pasted
and Macro is run the Template is saved as a Final Report somewhere on the
Report Server.

The Excel runs in the background in all this process. There could be
multiple instances of Excel running on the same box. The instances run under
separate WindowStation.

In this process depending on the options selected by the end user, the Report
options are applied on the final report workbook. The some of the Report
options are Protect Workbook, Protect Worksheets, Protect Macro Code,
Delete Macro code, etc.

I am facing a problem when it comes to Protecting Macro Code.
I believe that there is no API/Excel Object Mode available to accomplish
this task.

Given below is the code I am using for protecting the macro code.

Sub protectMacroCode()

With Application
.CutCopyMode = False
.DisplayAlerts = False

.SendKeys "%{F11}", True
.SendKeys "%T", True
.SendKeys "E", True
.SendKeys "^{TAB}", True
.SendKeys "%V", True
.SendKeys "{TAB}" & Trim("XXXXXX"), True
.SendKeys "{TAB}" & Trim("XXXXXX"), True
.SendKeys "~", True
.SendKeys "%{F11}", True
End With
End Sub

The XLAM writes the above code to the Report Workbook and Excutes this
macro code. The macro is successfully written in the workbook but does
not work as expected when call from XLAM.

Whereas this particular piece of code works fine when the workbook has
this macro already present and the XLAM just executes it.

If anybody have any idea please let me know as it is a blocking issue in our
process.

Any help appreciated.

Thanks in advance.

Why do you need to protect the workbook dynamically, why not just set the
password in the design mode?

--
__________________________________
HTH

Bob

"Anand Nichkaode" wrote in
message ...
Hi all,

We are using Excel for reporting in our organization.
Basically there is a Java web application which instantiates Excel 2007
with
an XLAM. This XLAM opens a Report Template(an .xls, .xlsm file, name
supplied as
command line parameter). The Template has a data sheet where we have
multiple
ranges and the Report sheet which shows the final report based on the data
from the DataSheet. The ReportSheet contains formulas to show the final
output. It could contain Charts/Graphs also. The data is fetched from SQL
server and is pasted on the Ranges. And finally when all the data is
pasted
and Macro is run the Template is saved as a Final Report somewhere on the
Report Server.

The Excel runs in the background in all this process. There could be
multiple instances of Excel running on the same box. The instances run
under
separate WindowStation.

In this process depending on the options selected by the end user, the
Report
options are applied on the final report workbook. The some of the Report
options are Protect Workbook, Protect Worksheets, Protect Macro Code,
Delete Macro code, etc.

I am facing a problem when it comes to Protecting Macro Code.
I believe that there is no API/Excel Object Mode available to accomplish
this task.

Given below is the code I am using for protecting the macro code.

Sub protectMacroCode()

With Application
.CutCopyMode = False
.DisplayAlerts = False

.SendKeys "%{F11}", True
.SendKeys "%T", True
.SendKeys "E", True
.SendKeys "^{TAB}", True
.SendKeys "%V", True
.SendKeys "{TAB}" & Trim("XXXXXX"), True
.SendKeys "{TAB}" & Trim("XXXXXX"), True
.SendKeys "~", True
.SendKeys "%{F11}", True
End With
End Sub

The XLAM writes the above code to the Report Workbook and Excutes this
macro code. The macro is successfully written in the workbook but does
not work as expected when call from XLAM.

Whereas this particular piece of code works fine when the workbook has
this macro already present and the XLAM just executes it.

If anybody have any idea please let me know as it is a blocking issue in
our
process.

Any help appreciated.

Thanks in advance.

Thx for the reply.

If the workbook is Protected in the design mode then I won't be able to open
it in XLAM and work upon it i.e Paste Data and apply Report options, etc.

The flow is that the user uploads the Template ( a simple workbook empty
formulas and empty ranges where data would be pasted and formulas would
calculate on the data pasted) which is opened in XLAM and worked upon and
saved as the final report. So if the Template itself is protected then XLAM
wont be able to open it and work upon.

Please let me know if I am missing something.



"Bob Phillips" wrote:

Why do you need to protect the workbook dynamically, why not just set the
password in the design mode?

--
__________________________________
HTH

Bob

"Anand Nichkaode" wrote in
message ...
Hi all,

We are using Excel for reporting in our organization.
Basically there is a Java web application which instantiates Excel 2007
with
an XLAM. This XLAM opens a Report Template(an .xls, .xlsm file, name
supplied as
command line parameter). The Template has a data sheet where we have
multiple
ranges and the Report sheet which shows the final report based on the data
from the DataSheet. The ReportSheet contains formulas to show the final
output. It could contain Charts/Graphs also. The data is fetched from SQL
server and is pasted on the Ranges. And finally when all the data is
pasted
and Macro is run the Template is saved as a Final Report somewhere on the
Report Server.

The Excel runs in the background in all this process. There could be
multiple instances of Excel running on the same box. The instances run
under
separate WindowStation.

In this process depending on the options selected by the end user, the
Report
options are applied on the final report workbook. The some of the Report
options are Protect Workbook, Protect Worksheets, Protect Macro Code,
Delete Macro code, etc.

I am facing a problem when it comes to Protecting Macro Code.
I believe that there is no API/Excel Object Mode available to accomplish
this task.

Given below is the code I am using for protecting the macro code.

Sub protectMacroCode()

With Application
.CutCopyMode = False
.DisplayAlerts = False

.SendKeys "%{F11}", True
.SendKeys "%T", True
.SendKeys "E", True
.SendKeys "^{TAB}", True
.SendKeys "%V", True
.SendKeys "{TAB}" & Trim("XXXXXX"), True
.SendKeys "{TAB}" & Trim("XXXXXX"), True
.SendKeys "~", True
.SendKeys "%{F11}", True
End With
End Sub

The XLAM writes the above code to the Report Workbook and Excutes this
macro code. The macro is successfully written in the workbook but does
not work as expected when call from XLAM.

Whereas this particular piece of code works fine when the workbook has
this macro already present and the XLAM just executes it.

If anybody have any idea please let me know as it is a blocking issue in
our
process.

Any help appreciated.

Thanks in advance.

I think that you can, I certainly do that all of the time.

I have never had to unprotect my code to run it, worksheets yes, but not
the code.


--
__________________________________
HTH

Bob

"Anand Nichkaode" wrote in
message ...
Thx for the reply.

If the workbook is Protected in the design mode then I won't be able to
open
it in XLAM and work upon it i.e Paste Data and apply Report options, etc.

The flow is that the user uploads the Template ( a simple workbook empty
formulas and empty ranges where data would be pasted and formulas would
calculate on the data pasted) which is opened in XLAM and worked upon and
saved as the final report. So if the Template itself is protected then
XLAM
wont be able to open it and work upon.

Please let me know if I am missing something.



"Bob Phillips" wrote:

Why do you need to protect the workbook dynamically, why not just set the
password in the design mode?

--
__________________________________
HTH

Bob

"Anand Nichkaode" wrote in
message ...
Hi all,

We are using Excel for reporting in our organization.
Basically there is a Java web application which instantiates Excel 2007
with
an XLAM. This XLAM opens a Report Template(an .xls, .xlsm file, name
supplied as
command line parameter). The Template has a data sheet where we have
multiple
ranges and the Report sheet which shows the final report based on the
data
from the DataSheet. The ReportSheet contains formulas to show the final
output. It could contain Charts/Graphs also. The data is fetched from
SQL
server and is pasted on the Ranges. And finally when all the data is
pasted
and Macro is run the Template is saved as a Final Report somewhere on
the
Report Server.

The Excel runs in the background in all this process. There could be
multiple instances of Excel running on the same box. The instances run
under
separate WindowStation.

In this process depending on the options selected by the end user, the
Report
options are applied on the final report workbook. The some of the
Report
options are Protect Workbook, Protect Worksheets, Protect Macro Code,
Delete Macro code, etc.

I am facing a problem when it comes to Protecting Macro Code.
I believe that there is no API/Excel Object Mode available to
accomplish
this task.

Given below is the code I am using for protecting the macro code.

Sub protectMacroCode()

With Application
.CutCopyMode = False
.DisplayAlerts = False

.SendKeys "%{F11}", True
.SendKeys "%T", True
.SendKeys "E", True
.SendKeys "^{TAB}", True
.SendKeys "%V", True
.SendKeys "{TAB}" & Trim("XXXXXX"), True
.SendKeys "{TAB}" & Trim("XXXXXX"), True
.SendKeys "~", True
.SendKeys "%{F11}", True
End With
End Sub

The XLAM writes the above code to the Report Workbook and Excutes this
macro code. The macro is successfully written in the workbook but does
not work as expected when call from XLAM.

Whereas this particular piece of code works fine when the workbook has
this macro already present and the XLAM just executes it.

If anybody have any idea please let me know as it is a blocking issue
in
our
process.

Any help appreciated.

Thanks in advance.

Agreed. I don't have to unprotect the code to run it.

But then I have a "Delete macro" option, which user can select and I have to
delete all the macro code from the XLAM and then save the Final Report
workbook. I guess for that purpose I will have to unprotect the code too.

Thx for the reply.


"Bob Phillips" wrote:

I think that you can, I certainly do that all of the time.

I have never had to unprotect my code to run it, worksheets yes, but not
the code.


--
__________________________________
HTH

Bob

"Anand Nichkaode" wrote in
message ...
Thx for the reply.

If the workbook is Protected in the design mode then I won't be able to
open
it in XLAM and work upon it i.e Paste Data and apply Report options, etc.

The flow is that the user uploads the Template ( a simple workbook empty
formulas and empty ranges where data would be pasted and formulas would
calculate on the data pasted) which is opened in XLAM and worked upon and
saved as the final report. So if the Template itself is protected then
XLAM
wont be able to open it and work upon.

Please let me know if I am missing something.



"Bob Phillips" wrote:

Why do you need to protect the workbook dynamically, why not just set the
password in the design mode?

--
__________________________________
HTH

Bob

"Anand Nichkaode" wrote in
message ...
Hi all,

We are using Excel for reporting in our organization.
Basically there is a Java web application which instantiates Excel 2007
with
an XLAM. This XLAM opens a Report Template(an .xls, .xlsm file, name
supplied as
command line parameter). The Template has a data sheet where we have
multiple
ranges and the Report sheet which shows the final report based on the
data
from the DataSheet. The ReportSheet contains formulas to show the final
output. It could contain Charts/Graphs also. The data is fetched from
SQL
server and is pasted on the Ranges. And finally when all the data is
pasted
and Macro is run the Template is saved as a Final Report somewhere on
the
Report Server.

The Excel runs in the background in all this process. There could be
multiple instances of Excel running on the same box. The instances run
under
separate WindowStation.

In this process depending on the options selected by the end user, the
Report
options are applied on the final report workbook. The some of the
Report
options are Protect Workbook, Protect Worksheets, Protect Macro Code,
Delete Macro code, etc.

I am facing a problem when it comes to Protecting Macro Code.
I believe that there is no API/Excel Object Mode available to
accomplish
this task.

Given below is the code I am using for protecting the macro code.

Sub protectMacroCode()

With Application
.CutCopyMode = False
.DisplayAlerts = False

.SendKeys "%{F11}", True
.SendKeys "%T", True
.SendKeys "E", True
.SendKeys "^{TAB}", True
.SendKeys "%V", True
.SendKeys "{TAB}" & Trim("XXXXXX"), True
.SendKeys "{TAB}" & Trim("XXXXXX"), True
.SendKeys "~", True
.SendKeys "%{F11}", True
End With
End Sub

The XLAM writes the above code to the Report Workbook and Excutes this
macro code. The macro is successfully written in the workbook but does
not work as expected when call from XLAM.

Whereas this particular piece of code works fine when the workbook has
this macro already present and the XLAM just executes it.

If anybody have any idea please let me know as it is a blocking issue
in
our
process.

Any help appreciated.

Thanks in advance.

Then you are stuck, there is nothing exposed in the VBE object model to
allow you to do what you want.

Personally, I think that adding/deleting code/controls dynamically is not a
good idea, it is better to design around it IMO.

--
__________________________________
HTH

Bob

"Anand Nichkaode" wrote in
message ...
Agreed. I don't have to unprotect the code to run it.

But then I have a "Delete macro" option, which user can select and I have
to
delete all the macro code from the XLAM and then save the Final Report
workbook. I guess for that purpose I will have to unprotect the code too.

Thx for the reply.


"Bob Phillips" wrote:

I think that you can, I certainly do that all of the time.

I have never had to unprotect my code to run it, worksheets yes, but not
the code.


--
__________________________________
HTH

Bob

"Anand Nichkaode" wrote in
message ...
Thx for the reply.

If the workbook is Protected in the design mode then I won't be able to
open
it in XLAM and work upon it i.e Paste Data and apply Report options,
etc.

The flow is that the user uploads the Template ( a simple workbook
empty
formulas and empty ranges where data would be pasted and formulas would
calculate on the data pasted) which is opened in XLAM and worked upon
and
saved as the final report. So if the Template itself is protected then
XLAM
wont be able to open it and work upon.

Please let me know if I am missing something.



"Bob Phillips" wrote:

Why do you need to protect the workbook dynamically, why not just set
the
password in the design mode?

--
__________________________________
HTH

Bob

"Anand Nichkaode" wrote in
message ...
Hi all,

We are using Excel for reporting in our organization.
Basically there is a Java web application which instantiates Excel
2007
with
an XLAM. This XLAM opens a Report Template(an .xls, .xlsm file, name
supplied as
command line parameter). The Template has a data sheet where we have
multiple
ranges and the Report sheet which shows the final report based on
the
data
from the DataSheet. The ReportSheet contains formulas to show the
final
output. It could contain Charts/Graphs also. The data is fetched
from
SQL
server and is pasted on the Ranges. And finally when all the data is
pasted
and Macro is run the Template is saved as a Final Report somewhere
on
the
Report Server.

The Excel runs in the background in all this process. There could be
multiple instances of Excel running on the same box. The instances
run
under
separate WindowStation.

In this process depending on the options selected by the end user,
the
Report
options are applied on the final report workbook. The some of the
Report
options are Protect Workbook, Protect Worksheets, Protect Macro
Code,
Delete Macro code, etc.

I am facing a problem when it comes to Protecting Macro Code.
I believe that there is no API/Excel Object Mode available to
accomplish
this task.

Given below is the code I am using for protecting the macro code.

Sub protectMacroCode()

With Application
.CutCopyMode = False
.DisplayAlerts = False

.SendKeys "%{F11}", True
.SendKeys "%T", True
.SendKeys "E", True
.SendKeys "^{TAB}", True
.SendKeys "%V", True
.SendKeys "{TAB}" & Trim("XXXXXX"), True
.SendKeys "{TAB}" & Trim("XXXXXX"), True
.SendKeys "~", True
.SendKeys "%{F11}", True
End With
End Sub

The XLAM writes the above code to the Report Workbook and Excutes
this
macro code. The macro is successfully written in the workbook but
does
not work as expected when call from XLAM.

Whereas this particular piece of code works fine when the workbook
has
this macro already present and the XLAM just executes it.

If anybody have any idea please let me know as it is a blocking
issue
in
our
process.

Any help appreciated.

Thanks in advance.