With the project protected, you'll have to use some SendKeys to unprotect the
project. And in newer versions of excel, each user can choose whether they want
to allow program access to their projects.

I think I'd use a template workbook that contains all the code that I need.
Then use that to put the data.

Option Explicit
Sub OpenTemplate()

Dim myTemplateName As String
Dim tempStr As String
Dim wkbk As Workbook

myTemplateName = "\\commonshare\foldername\templatename.xlt"

tempStr = ""
On Error Resume Next
tempStr = Dir(myTemplateName)
On Error GoTo 0
If tempStr = "" Then
MsgBox "design error--Template not available"
Exit Sub
End If

Set wkbk = Workbooks.Add(template:=myTemplateName)

'do what you need...
End Sub

May help get you started.

Anson wrote:

Is it possible to copy the entire VB Project (with protection) to a new workbook? I am trying to write a program to allow users to produce an extract of the workbook with all the programming functions in it (without changing the original workbook). I want to extract it witout saving it (sort of like the "sheets.copy" code) unless the user saves it manually.

--

Dave Peterson

Thanks again for your reply. I have thought about using an external template as you have described. Here is my situation: The file contains confidential information to be sent out to selected group of people, say executives, and are deposited in a restricted access drive. They in turn will do the "extraction" and send them to a level down, say directors. The directors will also extract them to the manager levels and they will be the ones to make the initial decision based on the information from the file they have given. The function of these workbooks contain data validation, electronic signature, a special budget calculator, and to consolidate the managers' input back to the original files. I want to retain these functionality each time then they do an extraction. Since there could be potentially hundreds of users and they could be working offsite or offline, so external template would not be feasible

Could you elaborate the sendkey method? I thought it doesn't work with project password protection.

If the excel gods are with you, you may be able to make sendkeys work. But it's
not something I'd rely on. And for sure, it's not something I would expect
others to rely on.

The first post I saw that used Sendkeys with protecting the project was by Bill
Manville.
http://groups.google.com/groups?thre...eb83%40msn.com

You might be able to use the same kind of thing to protect the project.

And if you stay away from the keyboard/mouse and let the macro run without
disturbance, it works ok for me.

Sendkeys will send stuff to whatever application has focus--so if you're in the
middle of another application, your project may not be protected and you may
have some unintentional changes to the current application.

======
And to make your life even more confusing. Excel's protection is very weak
(tools|protection type).

There's code/links posted every day that show how to unprotect a workbook or a
worksheet.

If that data is really sensitive, don't use excel.

Anson wrote:

Thanks again for your reply. I have thought about using an external template as you have described. Here is my situation: The file contains confidential information to be sent out to selected group of people, say executives, and are deposited in a restricted access drive. They in turn will do the "extraction" and send them to a level down, say directors. The directors will also extract them to the manager levels and they will be the ones to make the initial decision based on the information from the file they have given. The function of these workbooks contain data validation, electronic signature, a special budget calculator, and to consolidate the managers' input back to the original files. I want to retain these functionality each time then they do an extraction. Since there could be potentially hundreds of users and they could be working offsite or offline, so external template would not be feasible.

Could you elaborate the sendkey method? I thought it doesn't work with project password protection.

--

Dave Peterson

I meant that the sendkeys to protect the code works for me. I don't think I've
ever gotten the unprotect to work--but even if I did, I'm not sure if I'd use
it.

Protecting the project can also be broken pretty quickly--either a commericial
service or google searches would prove it.

Dave Peterson wrote:

If the excel gods are with you, you may be able to make sendkeys work. But it's
not something I'd rely on. And for sure, it's not something I would expect
others to rely on.

The first post I saw that used Sendkeys with protecting the project was by Bill
Manville.
http://groups.google.com/groups?thre...eb83%40msn.com

You might be able to use the same kind of thing to protect the project.

And if you stay away from the keyboard/mouse and let the macro run without
disturbance, it works ok for me.

Sendkeys will send stuff to whatever application has focus--so if you're in the
middle of another application, your project may not be protected and you may
have some unintentional changes to the current application.

======
And to make your life even more confusing. Excel's protection is very weak
(tools|protection type).

There's code/links posted every day that show how to unprotect a workbook or a
worksheet.

If that data is really sensitive, don't use excel.

Anson wrote:

Thanks again for your reply. I have thought about using an external template as you have described. Here is my situation: The file contains confidential information to be sent out to selected group of people, say executives, and are deposited in a restricted access drive. They in turn will do the "extraction" and send them to a level down, say directors. The directors will also extract them to the manager levels and they will be the ones to make the initial decision based on the information from the file they have given. The function of these workbooks contain data validation, electronic signature, a special budget calculator, and to consolidate the managers' input back to the original files. I want to retain these functionality each time then they do an extraction. Since there could be potentially hundreds of users and they could be working offsite or offline, so external template would not be feasible.

Could you elaborate the sendkey method? I thought it doesn't work with project password protection.

--

Dave Peterson


--

Dave Peterson

Thanks for you reply. I guess I am out of luck. Suddenly I have noticed that there are a lot of limitations on what VBA can do in Excel....

"Dave Peterson" wrote:

I meant that the sendkeys to protect the code works for me. I don't think I've
ever gotten the unprotect to work--but even if I did, I'm not sure if I'd use
it.

Protecting the project can also be broken pretty quickly--either a commericial
service or google searches would prove it.

Dave Peterson wrote:

If the excel gods are with you, you may be able to make sendkeys work. But it's
not something I'd rely on. And for sure, it's not something I would expect
others to rely on.

The first post I saw that used Sendkeys with protecting the project was by Bill
Manville.
http://groups.google.com/groups?thre...eb83%40msn.com

You might be able to use the same kind of thing to protect the project.

And if you stay away from the keyboard/mouse and let the macro run without
disturbance, it works ok for me.

Sendkeys will send stuff to whatever application has focus--so if you're in the
middle of another application, your project may not be protected and you may
have some unintentional changes to the current application.

======
And to make your life even more confusing. Excel's protection is very weak
(tools|protection type).

There's code/links posted every day that show how to unprotect a workbook or a
worksheet.

If that data is really sensitive, don't use excel.

Anson wrote:

Thanks again for your reply. I have thought about using an external template as you have described. Here is my situation: The file contains confidential information to be sent out to selected group of people, say executives, and are deposited in a restricted access drive. They in turn will do the "extraction" and send them to a level down, say directors. The directors will also extract them to the manager levels and they will be the ones to make the initial decision based on the information from the file they have given. The function of these workbooks contain data validation, electronic signature, a special budget calculator, and to consolidate the managers' input back to the original files. I want to retain these functionality each time then they do an extraction. Since there could be potentially hundreds of users and they could be working offsite or offline, so external template would not be feasible.

Could you elaborate the sendkey method? I thought it doesn't work with project password protection.

--

Dave Peterson


--

Dave Peterson