Linking Worksheets in a Workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good morning,

I have an Excel workbook which contains multiple worksheets. These
worksheets are linked to an Access database. The users will input the data
in the worksheets, and my database is pulling the data from those worksheets.


What I like to do is I like to lock the workbook so that the users can not
rename the sheets names and can not move or insert the sheets. I tried the
feature "Protect Workbook", and when I did that, I was not able to get the
data in
the database. It's saying that the workbook is protected.

Please help. I understand this message relates more for the Excel forum. I
also posted in the Excel forum, and I have not yet received any response. I
thought some of you here might have the same experience that would like to
share with me.

Thanks. Happy Holidays!
 
Protecting the workbook won't prevent users from renaming, moving, or
inserting new sheets. But just so you'll know, when you do protect a
workbook, before you do you can select the cells you want users to be able to
enter data in and change their locked property. Hold down the CTRL key and
select all the cells you want to unlock, then go into Format > Cells, select
the Protection tab, and clear the checkmark next to "Locked". Then when you
protect the workbook, be sure the option to allow users to select unlocked
cells has a checkmark beside it.

Hiding or unhiding tabs so that they can't be messed with can probably be
accomplished with Visual Basic, but unfortunately I can't help you figure
that one out. Perhaps there are some entries in the Excel forum under
Programming that will help.
 
Protecting the workbook won't prevent users from renaming, moving, or
inserting new sheets.
Click to expand...

Oh yes it will! According to Access 2003 Help, protecting a workbook's
"Structure" prevents, among other things:

-Viewing worksheets that you have hidden.
-Moving, deleting, hiding, or changing the names of worksheets.
-Inserting new worksheets or chart sheets.
-Moving or copying worksheets to another workbook.
But just so you'll know, when you do protect a
workbook, before you do you can select the cells you want users to be able to
enter data in and change their locked property. Hold down the CTRL key and
select all the cells you want to unlock, then go into Format > Cells, select
the Protection tab, and clear the checkmark next to "Locked". Then when you
protect the workbook, be sure the option to allow users to select unlocked
cells has a checkmark beside it.
Click to expand...

All this relates to protecting *worksheets*, not workbooks.
Hiding or unhiding tabs so that they can't be messed with can probably be
accomplished with Visual Basic, but unfortunately I can't help you figure
that one out.
Click to expand...

You may be thinking of the difference between (a) hiding a sheet in the
normal way by setting its Visible property to False, and (b) hiding it
completely from the user interface by setting its Visible property to
xlVeryHidden.
 
Hi,

I can link to sheets in a protected workbook without difficulty -
provided that no password was set on the protection. Using a password
encrypts the workbook file, and there is no mechanism for Access to
decrypt it so it can get at the data.

It's not mandatory to set a password when you protect the workbook, so
if you only need to protect against accidental changes by the users you
can omit the password and use your linked tables as normal.

If you do have to use a password on the workbook, the only way Access
can get at the data is if you open the workbook in Excel first and then
use the linked tables. This can be done manually or via VBA.

For more information, try searching http://groups.google.com for
"protected workbook" excel access
 
Hi guys,

Thanks for all of your helps. It works on the "Protect Workbook", without
using a password. When I tried to protect the workbook with a password, it
encrypted and Access database couldn't access the sheets.

Thanks again.
 
I hope that one of you can help me with a very similar problem.

I have an access database. I am analyzing a report using microsoft excel.
This creates a worksheet in excel. I have a template that "should" pull the
information from the worksheet. I then email the template format on to an
end user who enters information in fields that are not locked. When this
data is returned, I would like to be able to have my database pull the data
from the worksheets.

Here are my problems:
1. I am a novice at this and am probably over my head, but I am trying.
2. I do not change the information location; i.e. cells, type of info,
when I am analyzing with excel. I do, however, answer yes when asked "There
is already a "dealer worksheet file". Do you want to create a new one?
When opened, the template should pull the information from the cells.
a. The information is not pulling over to the template, and I have
to relink the worksheet cell by cell to the template, or
b. If the information pulls, it does not insert the info in the
correct location on the template.

Once I get the template to contain the correct information, I protect all
but a few cells and email to end-user.
1. How do I take info that is emailed back - the original template with
information inserted - and has access pull that info into the access
database without manually key punching info?

I have tried to go to Data/Export/Excel, but my query has 4 parameters that
the end-user must complete; therefore, all info is not exported to excel.
This is why I am creating a report via query and then analyzing it with
excel.

Many many thanks for your help. Any suggestions and assistance will be
greatly appreciated!
JR
 
Back
Top