Access->Excel/Excel ProtectSharing

[Kristy]

Hi,
I could not reply to the topic below the line, I think because it's
old; so I copied it into this new post.

I tried the suggested code at the bottom (using ACC2002 and
Excel2002SP-2) as is but it set the WriteResPassword, not the
SharingPassword.
objXLBook.ProtectSharing strFilePathName, , "Password"

I added commas and got the Sharing pw set. objXLBook.ProtectSharing
strFilePathName, , , , , "Password"

My problem is I need to pw-protect the sheet, the pw-protect workbook
and the pw-protect sharing in code. I can do this manually in Excel
but not in Access code:
pw-protect the sheet then pw-protect the workbook then pw-protect
the sharing
Right now I can pw-protect the sheet and the pw-protect sharing, not
all three. Users can rename worksheets, etc, with the workbook
unprotected.

The following gives a run-time error 5 on objXLBook.ProtectSharing.
objXLApp.DisplayAlerts = False
objXLApp.ActiveSheet.Protect Password:="Password",
DrawingObjects:=True, Contents:=True, Scenarios:=True,
AllowFiltering:=True
objXLBook.Protect Password:="Password"
objXLBook.ProtectSharing strFilePathName, , , , , "Password"
objXLApp.DisplayAlerts = True

While code is in break on objXLBook.ProtectSharing, I can go into
Excel, click Tools, Protection, Protect and Share Workbook, check off
Sharing with track changes, enter a password twice AND IT WORKS! Is
this a bug that I cannot do this in code??
Any help would be greatly appreciated!
Thanks,
Kristy
============================================================

Access->Excel/Excel ProtectSharing
All 5 messages in topic
Larry May 25 2004, 10:28 am
Trying to write code that will let me open an Excel file
from Access and set/save it as a Shared file so I don't
get conflict messages when multiple users of my Access
tool are hitting the same Excel source file at the same
time. I'm okay with the general "package": create an
instance of Excel, etc. However, I haven't been able to
find the right way of emulating the
Excel "ThisWorkbook.ProtectSharing" method from an Access
VBA procedure (note that Excel Help says ThisWorkbook only
works within Excel). Any tips most welcome.


Gerald Stanley May 25 2004, 10:43 am
The Excel.Workbook object has a ProtectSharing method
which can be used from Access.

(e-mail address removed) May 25 2004, 1:18 pm
I tried invoking that about half a dozen ways and couldn't
make it happen. Here's my code. Suggestions?

Public Function SetSharing()
Dim strFilePathName As String
Dim objXLApp As Object, objXLBook As Object,
objThisWorkbook As Object
strFilePathName = myDB!Filepath & myDB!Filename
strFilePathName = "c:\ShareTest.xls"

Set objXLBook = GetObject(strFilePathName)

Set objXLApp = objXLBook.Parent

Set objThisWorkbook = objXLApp.Workbooks(1)

objXLApp.DisplayAlerts = False

'##########NONE OF THE FOLLOWING WORKED!!!###########
'objXLApp.ThisWorkbook.Protect­Sharing
'objXLApp.ProtectSharing
'objXLBook.ProtectSharing
'objXLApp.Workbooks(1).Protect­Sharing
'objXLBook.Workbooks(1).Protec­tSharing
'objThisWorkbook.ProtectSharin­g
'#############################­#######################

objXLApp.DisplayAlerts = True

objXLBook.Save
objXLApp.Quit

Set objThisWorkbook = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing

End Function


Gerald Stanley May 25 2004, 2:12 pm
The following is put together from snippets of my own code
that do work.

Public Function SetSharing()
Dim strFilePathName As String
Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook

strFilePathName = "c:\ShareTest.xls"

Set objXLApp = CreateObject("Excel.Applicatio­n")
Set objXLBook = objXLApp.Workbooks.Open(strFil­ePathName)

Kill strFilePathName

objXLApp.DisplayAlerts = False
objXLBook.ProtectSharing strFilePathName, , "Password"
objXLApp.DisplayAlerts = True

objXLBook.Save
Set objXLBook = Nothing
objXLApp.Quit
Set objXLApp = Nothing

End Function

LarryP May 27 2004, 9:58 am
Did the job -- thanks. Only thing was I had to take out
the "Kill" line.

 

[John Nurick]

Hi Kristy,

This question is about how to use Excel rather than Access, so you'll
probably get a better answer if you post it in an Excel newsgroup.