Access->Excel/Excel ProtectSharing

[Larry]

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]

The Excel.Workbook object has a ProtectSharing method
which can be used from Access.

Hope This Helps
Gerald Stanley MCSD

 

[Guest]

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.ProtectSharing
'objXLApp.ProtectSharing
'objXLBook.ProtectSharing
'objXLApp.Workbooks(1).ProtectSharing
'objXLBook.Workbooks(1).ProtectSharing
'objThisWorkbook.ProtectSharing
'####################################################

objXLApp.DisplayAlerts = True

objXLBook.Save
objXLApp.Quit

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

End Function

 

[Gerald Stanley]

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.Application")
Set objXLBook = objXLApp.Workbooks.Open(strFilePathName)

Kill strFilePathName

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

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

End Function

Hope This Helps
Gerald Stanley MCSD

 

[LarryP]

Did the job -- thanks. Only thing was I had to take out
the "Kill" line.