How to use Excel VBA to cancel Excel readonly status

[Guest]

Dear All,

Recently, I have developed an Excel VBA for my internal users. In this
VBA, user can press a button to save the workbook immediatedly.

Now, I have placed this Excel file onto our SharePoint website for sharing
to the public. However, I have realized when the user open this Excel file,
it will be in Readonly status. User can open it but they can't save data in
this Excel (through marco).

I would like to ask that are there any coding can change the Excel
workbook from readonly to read-write status, so the user can save the data?
Otherwise, any other codes can help me to save the Excel without any notice
for the user in SharePoint website?

Thank you for all

 

[Jim Rech]

Changing the workbook file access is attempted through the workbook
ChangeFileAccess method. Of course, if there are restrictions on the
server, etc., it will fail.

--
Jim
| Dear All,
|
| Recently, I have developed an Excel VBA for my internal users. In this
| VBA, user can press a button to save the workbook immediatedly.
|
| Now, I have placed this Excel file onto our SharePoint website for
sharing
| to the public. However, I have realized when the user open this Excel
file,
| it will be in Readonly status. User can open it but they can't save data
in
| this Excel (through marco).
|
| I would like to ask that are there any coding can change the Excel
| workbook from readonly to read-write status, so the user can save the
data?
| Otherwise, any other codes can help me to save the Excel without any
notice
| for the user in SharePoint website?
|
| Thank you for all

 

[Guest]

If you the user has write access you can open the workbook, modify it, save
it back to sharepoint.

' Determine if workbook can be checked out.
On Error GoTo ErrorHandler
'Fails if User not Logged into Sharepoint
If Workbooks.CanCheckOut(Filename:=savename) = True Then
Workbooks.CheckOut savename
MsgBox "Checked out file from SharePoint: " & file
End If

'I actually don't need to open file
'Workbooks.Open (savename)

'Instead save over it.
ActiveWorkbook.SaveAs Filename:=savename,
FileFormat:=xlWorkbookNormal
'Check it in -- works if I checked it Out

If Workbooks(ActiveWorkbook.Name).CanCheckIn = True Then
Workbooks(ActiveWorkbook.Name).CheckIn
savechanges:=SaveDuringClose, Comments:="", MakePublic:=True
MsgBox file & " has been checked in."
Else
MsgBox "This file cannot be checked in " & _
"at this time. Please try again later, " & _
"someone else who has write access could have it Checked
Out."

End If

--If you come up with a way to check login access first and figure out how
to save a new file there. please let me know.

Thanks,
dalejrstwin